(RADIATOR) AuthBy SQL and Passwords ..

[Zebaulon Kansal]

Hi,

	Well, some of you may remember me posting here about prepaid
VoIP woes a few weeks back.  Kudos to Simon Hackett for sending his
rather detailed reply, because now I have gotten that issue all ironed
out. :)

	Here's where the next interesting problem comes in...  a problem
which isn't really VoIP/prepaid related.

	We are using 14-digit card numbers.  The idea is that, at some
point, we can set the first 10 digits to their home telephone number.
(We're in the US, which uses 3 digit area codes, 3 digit exchanges and 4
digit subscriber numbers.)  The last four digits could be random.  The
idea here being that once we get all of the backend security setup, we
can sell prepaid calls and say: "Use this from your home phone, and you
won't have to enter the card number.  Use it anywhere else, and you can
still use it, but you'll need to put the card number in."  (In other
words, if we can authenticate them off ANI, they won't have to enter the
card number..  otherwise, they will.)

	Here's where the problem is:  Right now we are assigning people
card numbers for beta-testing.  They all happen to have the same first
ten digits (which we use for card type and advertising/date tracking
currently.)  The last four digits are random.  So in the database (a
scaled down version) you may have:

+------------+------+----------+
|  USERNAME  | PASS | TIMELEFT |
+------------+------+----------+
| 1234567890 | 9999 | 20       |
| 1234567890 | 2222 | 3600     |
+------------+------+----------+

	Now, whenever someone dials in and enters their card number,
RADIUS uses a statement like this:  (using AuthSelect)

SELECT USERNAME, PASS, TIMELEFT FROM SUBSCRIBERS WHERE USERNAME='%n'

	All of the appropriate AuthColumnDef lines are there, and there
are a few more columns that we use too, but that's an example.

	Problem comes here:  If the first user calls in, most likely
they will be able to login fine.  However, if the second user calls in,
and enters their card number (12345678902222) RADIUS will execute a SQL
query like:

SELECT USERNAME, PASS, TIMELEFT FROM SUBSCRIBERS WHERE \
  USERNAME='1234567890'

	The query is obviously going to return two results there, one
for each.  RADIUS looks at the first one, checks the PASS field for the
Password, which is going to be 9999 -- whereas the Cisco asked for 2222.
The request is rejected.  The caller ends up with an error and the call
is disconnected.

	So I got the bright idea to add a "AND PASS='%{Password}'" to
the AuthSelect line.  But the query ends up AND PASS='' (nothing is put
in there.)  So, obviously RADIUS either 1) can't pass it like that or 2)
can but I'm doing it wrong.

	I'd like to be able to do one of two things.  First, have RADIUS
look at *all* the matching results from the query.  (In other words, do
a check for all rows with that username.)  Second, and more preferred
way of doing it, is to have RADIUS somehow put the password into the
query.  That way, it doesn't have to sift through a lot of records.
Sure, it can do that now no problem, but as we add more records, that
could be a problem.  (Do you honestly think Radiator would be happy if
it had to process 200,000 MySQL rows on every query?  I doubt any
program would be. <grin>)  I doubt we'll have THAT many, but you get the
idea..  I'd prefer to prevent problems, rather than work around them
when they do arise.. :)

	I did try using the %{User-Password} in the AuthSelect (after I
saw that in the trace 4 debug log.)  Ended up with some encrypted stuff
there. :)  (I noticed it that way in the log, but thought possibly it
would be unencrypted by then..?)

	Probably a simple problem with a simple solution, just one that
I haven't figured out yet.  Anyone else have some ideas?  If you need
all the configs, etc. let me know..  I try not to pollute the list with
all that unless needed. :)  TIA.




===
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.