Blank spaces after username - problem with accounting - MySql database.
Hi there... It is me again... Few days ago, I wrote about my problem with accounting. Problem is that accounting doesn't work for some users. In this case it is "test.user"... After some investigation with log files (holly radiusd -X), I can see that problem is in username. As you can see (line marked with *-*-*-*-*-*-*), some blank spaces were added to username. I'm not sure how is this possible and who adds this blank spaces (Mikrotik as nas or radius server?). Log file is truncated but I believe you can see where is the problem. ------------------------------------------------------------------------------------------------- LOG STARTS Finished request 13 Going to the next request Waking up in 5 seconds... rad_recv: Access-Request packet from host 192.168.15.2:48263, id=86, length=145 Service-Type = Framed-User Framed-Protocol = PPP NAS-Port = 23427 NAS-Port-Type = Ethernet User-Name = "test.user " *-*-*-*-*-*-* (notice blank spaces before closing ") .... rlm_realm: No '@' in User-Name = "test.user ", looking up realm NULL *-*-*-*-*-*-* (NOTICE BLANK SPACE BEFORE CLOSING ") .... ... radius_xlat: 'test.user ' *-*-*-*-*-*-* (NOTICE BLANK SPACE BEFORE CLOSING ") rlm_sql (sql): sql_set_user escaped user --> 'test.user ' *-*-*-*-*-*-* (NOTICE BLANK SPACE BEFORE CLOSING ") radius_xlat: 'SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = 'test.user ' ORDER BY id' *-*-*-*-*-*-* (NOTICE BLANK SPACE BEFORE CLOSING ") ..... rad_check_password: Found Auth-Type CHAP auth: type "CHAP" Processing the authenticate section of radiusd.conf modcall: entering group CHAP for request 14 rlm_chap: login attempt by "test.user " with CHAP password *-*-*-*-*-*-* (NOTICE BLANK SPACE BEFORE CLOSING ") rlm_chap: Using clear text password "testpassword" for user test.user authentication. rlm_chap: chap user test.user authenticated succesfully *-*-*-*-*-*-* (user with blank spaces doesn't exist anywhere and especially not in radcheck table and authentication was successful ????) modcall[authenticate]: module "chap" returns ok for request 14 modcall: leaving group CHAP (returns ok) for request 14 .............. radius_xlat: 'INSERT into radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('814054d0', '8ff2e3a7022d4a99', 'test.user ', '', '192.168.15.2', '23427', 'Ethernet', '2008-01-21 23:41:06', '0', '0', 'RADIUS', '', '', '0', '0', 'ht1', '00:19:66:11:59:F7', '', 'Framed-User', 'PPP', '192.168.15.212', '0', '0')' ------------------------------------------------------------------------------------------------- LOG ENDS p.s. As I can see in debug lines, raddacct table has been filled with wrong username so I searched for username "test.user " and I can see his traffic. I can solve this problem with small scheduler script who will replace "test.user " with "test.user" but who knows... Maybe tomorrow I will have more spaces and other signs in username ??? One is sure. MySql seems fine and only solution I can do now is to make querry "UPDATE radacct SET UserName='test.user' WHERE UserName='test.user '"; FR is 1.1.4 and I will update it and see what's going on...
Marinko Tarlac wrote:
Hi there... It is me again... Few days ago, I wrote about my problem with accounting. Problem is that accounting doesn't work for some users. In this case it is "test.user"... After some investigation with log files (holly radiusd -X), I can see that problem is in username. As you can see (line marked with *-*-*-*-*-*-*), some blank spaces were added to username. I'm not sure how is this possible and who adds this blank spaces (Mikrotik as nas or radius server?).
Neither. The user is adding the spaces. It looks to me like someone figured out that you have test accounts. They are using the test accounts to log in without paying.
p.s. As I can see in debug lines, raddacct table has been filled with wrong username so I searched for username "test.user " and I can see his traffic. I can solve this problem with small scheduler script who will replace "test.user " with "test.user" but who knows... Maybe tomorrow I will have more spaces and other signs in username ???
You need to to audit your configuration to ensure that you are using the user name *correctly*. e.g. this is wrong: SELECT ... %{User-Name} ... this is correct: SELECT ... '%{User-Name}' ...
One is sure. MySql seems fine and only solution I can do now is to make querry "UPDATE radacct SET UserName='test.user' WHERE UserName='test.user '";
Do not do that.
FR is 1.1.4 and I will update it and see what's going on...
The default queries and configuration should not have this problem. Alan DeKok.
Neither. The user is adding the spaces. It looks to me like someone figured out that you have test accounts. They are using the test accounts to log in without paying.
Well it is more than one account and they are random usernames (example, mirije, drogbba, etc. )
You need to to audit your configuration to ensure that you are using the user name *correctly*.
e.g. this is wrong: SELECT ... %{User-Name} ...
this is correct: SELECT ... '%{User-Name}' ...
One is sure. MySql seems fine and only solution I can do now is to make querry "UPDATE radacct SET UserName='test.user' WHERE UserName='test.user '";
-
Inside sql.conf everything seems fine. .... sql_user_name = "%{User-Name}" ..... authorize_check_query = "SELECT id, UserName, Attribute, Value, op \ FROM ${authcheck_table} \ WHERE Username = '%{SQL-User-Name}' \ ORDER BY id" authorize_reply_query = "SELECT id, UserName, Attribute, Value, op \ FROM ${authreply_table} \ WHERE Username = '%{SQL-User-Name}' \ ORDER BY id" Also, accounting queries are also the same. ???
Marinko Tarlac wrote:
Well it is more than one account and they are random usernames (example, mirije, drogbba, etc. )
FreeRADIUS does not add spaces to user names. Again, run it in debugging mode to see *exactly* what it is seeing.
Inside sql.conf everything seems fine. .... sql_user_name = "%{User-Name}" .....
Then run the server in debugging mode to see WHY "test-user " is being treated the same as "test-user". The user names are NOT the same, and should NOT be treated identically.
Also, accounting queries are also the same. ???
Once you fix the authentication so that users with spaces are not authenticated, the accounting should fix itself. In 2.0.0, you can simply put this at the *start* of the "authorize" section: if ("%{User-Name}" =~ / /) { reject } It won't fix the problem, but it will ensure that the users get rejected. Alan DeKok.
There is a configuration line in radiusd.conf: nospace_user = yes (default is no) that will remove trailing space even when entered. By the user. It doesn't help if the trailing space is in the database. Ivan Kalik Kalik Informatika ISP Dana 22/1/2008, "Marinko Tarlac" <mangia81@gmail.com> piše:
Neither. The user is adding the spaces. It looks to me like someone figured out that you have test accounts. They are using the test accounts to log in without paying.
Well it is more than one account and they are random usernames (example, mirije, drogbba, etc. )
You need to to audit your configuration to ensure that you are using the user name *correctly*.
e.g. this is wrong: SELECT ... %{User-Name} ...
this is correct: SELECT ... '%{User-Name}' ...
One is sure. MySql seems fine and only solution I can do now is to make querry "UPDATE radacct SET UserName='test.user' WHERE UserName='test.user '";
-
Inside sql.conf everything seems fine. ..... sql_user_name = "%{User-Name}" ...... authorize_check_query = "SELECT id, UserName, Attribute, Value, op \ FROM ${authcheck_table} \ WHERE Username = '%{SQL-User-Name}' \ ORDER BY id" authorize_reply_query = "SELECT id, UserName, Attribute, Value, op \ FROM ${authreply_table} \ WHERE Username = '%{SQL-User-Name}' \ ORDER BY id"
Also, accounting queries are also the same. ???
Thanks but this option didn't help. I tried with random username and when I add blank space after username user still can connect... Seems like a radius bug so I will try to install newer version. In any case I will inform you about this... Until I fix this issue I will update radacct with my own script who will remove blank spaces in usernames. Best regards 2008/1/22 <tnt@kalik.co.yu>:
There is a configuration line in radiusd.conf:
nospace_user = yes (default is no)
that will remove trailing space even when entered. By the user. It doesn't help if the trailing space is in the database.
Ivan Kalik Kalik Informatika ISP
Dana 22/1/2008, "Marinko Tarlac" <mangia81@gmail.com> piše:
Neither. The user is adding the spaces. It looks to me like someone figured out that you have test accounts. They are using the test accounts to log in without paying.
Well it is more than one account and they are random usernames (example, mirije, drogbba, etc. )
You need to to audit your configuration to ensure that you are using the user name *correctly*.
e.g. this is wrong: SELECT ... %{User-Name} ...
this is correct: SELECT ... '%{User-Name}' ...
One is sure. MySql seems fine and only solution I can do now is to make querry "UPDATE radacct SET UserName=' test.user' WHERE UserName='test.user '";
-
Inside sql.conf everything seems fine. ..... sql_user_name = "%{User-Name}" ...... authorize_check_query = "SELECT id, UserName, Attribute, Value, op \ FROM ${authcheck_table} \ WHERE Username = '%{SQL-User-Name}' \ ORDER BY id" authorize_reply_query = "SELECT id, UserName, Attribute, Value, op \ FROM ${authreply_table} \ WHERE Username = '%{SQL-User-Name}' \ ORDER BY id"
Also, accounting queries are also the same. ???
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
It's more likely to be a MySQL bug. Try the same with a user entry in users file - if user can authenticate with and without trailing space then it is freeradius bug. If SELECT ..... 'test' and SELECT ..... 'test ' produce the same output, then the problem is with MySQL. Ivan Kalik Kalik Informatika ISP Dana 22/1/2008, "Marinko Tarlac" <mangia81@gmail.com> piše:
Thanks but this option didn't help.
I tried with random username and when I add blank space after username user still can connect... Seems like a radius bug so I will try to install newer version.
In any case I will inform you about this... Until I fix this issue I will update radacct with my own script who will remove blank spaces in usernames.
Best regards
2008/1/22 <tnt@kalik.co.yu>:
There is a configuration line in radiusd.conf:
nospace_user = yes (default is no)
that will remove trailing space even when entered. By the user. It doesn't help if the trailing space is in the database.
Ivan Kalik Kalik Informatika ISP
Dana 22/1/2008, "Marinko Tarlac" <mangia81@gmail.com> piše:
Neither. The user is adding the spaces. It looks to me like someone figured out that you have test accounts. They are using the test accounts to log in without paying.
Well it is more than one account and they are random usernames (example, mirije, drogbba, etc. )
You need to to audit your configuration to ensure that you are using the user name *correctly*.
e.g. this is wrong: SELECT ... %{User-Name} ...
this is correct: SELECT ... '%{User-Name}' ...
One is sure. MySql seems fine and only solution I can do now is to make querry "UPDATE radacct SET UserName=' test.user' WHERE UserName='test.user '";
-
Inside sql.conf everything seems fine. ..... sql_user_name = "%{User-Name}" ...... authorize_check_query = "SELECT id, UserName, Attribute, Value, op \ FROM ${authcheck_table} \ WHERE Username = '%{SQL-User-Name}' \ ORDER BY id" authorize_reply_query = "SELECT id, UserName, Attribute, Value, op \ FROM ${authreply_table} \ WHERE Username = '%{SQL-User-Name}' \ ORDER BY id"
Also, accounting queries are also the same. ???
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
MySQL is 5.0.x Query SELECT * FROM `radcheck` WHERE `UserName` LIKE 'test.user '; (added space) returns 0 records (not found) Query SELECT * FROM `radcheck` WHERE `UserName` LIKE 'test.user'; (without blank space) returns valid records. (password, simultaneus-use and other check entries. So this can be called as FreeRadius bug ? I saw binary option so I will test it later. Best regards and thanks for your time and ideas you gave to me. Marinko 2008/1/22 <tnt@kalik.co.yu>:
It's more likely to be a MySQL bug. Try the same with a user entry in users file - if user can authenticate with and without trailing space then it is freeradius bug. If SELECT ..... 'test' and SELECT ..... 'test ' produce the same output, then the problem is with MySQL.
Ivan Kalik Kalik Informatika ISP
Dana 22/1/2008, "Marinko Tarlac" <mangia81@gmail.com> piše:
Thanks but this option didn't help.
I tried with random username and when I add blank space after username user still can connect... Seems like a radius bug so I will try to install newer version.
In any case I will inform you about this... Until I fix this issue I will update radacct with my own script who will remove blank spaces in usernames.
Best regards
2008/1/22 <tnt@kalik.co.yu>:
There is a configuration line in radiusd.conf:
nospace_user = yes (default is no)
that will remove trailing space even when entered. By the user. It doesn't help if the trailing space is in the database.
Ivan Kalik Kalik Informatika ISP
Dana 22/1/2008, "Marinko Tarlac" <mangia81@gmail.com> piše:
Neither. The user is adding the spaces. It looks to me like someone figured out that you have test accounts. They are using the test accounts to log in without paying.
Well it is more than one account and they are random usernames (example, mirije, drogbba, etc. )
You need to to audit your configuration to ensure that you are using the user name *correctly*.
e.g. this is wrong: SELECT ... %{User-Name} ...
this is correct: SELECT ... '%{User-Name}' ...
One is sure. MySql seems fine and only solution I can do now is to make querry "UPDATE radacct SET UserName=' test.user' WHERE UserName='test.user '";
-
Inside sql.conf everything seems fine. ..... sql_user_name = "%{User-Name}" ...... authorize_check_query = "SELECT id, UserName, Attribute, Value, op \ FROM ${authcheck_table} \ WHERE Username = '%{SQL-User-Name}' \ ORDER BY id" authorize_reply_query = "SELECT id, UserName, Attribute, Value, op \ FROM ${authreply_table} \ WHERE Username = '%{SQL-User-Name}' \ ORDER BY id"
Also, accounting queries are also the same. ???
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Marinko Tarlac wrote:
So this can be called as FreeRadius bug ?
No. As I have said, FreeRADIUS does not add spaces to user names. This is the *first* time I can recall seeing this problem in almost 9 years of working with FreeRADIUS. It is *not* a FreeRADIUS issue. If you see spaces, then: a) the user has typed the user name with spaces b) local edits to your configuration files are adding those spaces Alan DeKok.
Alan DeKok wrote:
a) the user has typed the user name with spaces
Yes. User has typed user name with space but why radius didn't ignore them? I repeat, user names and all other records in database are without space. User has entered space and he can connect but he can't see his accounting informations because they are connected with the same user but with space at the end. This problems has been noticed on our old server. I made myself a completely new php script for database manipulation and we use it on our new server. It works perfectly and without any problems. (FR1.1.7) Old server will be replaced with the new one but you must confess that "space problem" is a very interesting .. :) Seems like I'm subscribed for a strange problems ... :)
On Tuesday 22 January 2008 13:20:27 Marinko Tarlac wrote:
Alan DeKok wrote:
a) the user has typed the user name with spaces
Yes. User has typed user name with space but why radius didn't ignore them?
FreeRADIUS doesn't mangle usernames by default.
I repeat, user names and all other records in database are without space. User has entered space and he can connect but he can't see his accounting informations because they are connected with the same user but with space at the end.
If the user can connect with an invalid username, the problem is a configuration issue. Reject usernames with a space (see email from Alan) in the username or strip the spaces from the request. -Kevin
Marinko Tarlac wrote:
a) the user has typed the user name with spaces
Yes. User has typed user name with space but why radius didn't ignore them?
Why would it? Spaces are perfectly valid in a user name.
I repeat, user names and all other records in database are without space. User has entered space and he can connect but he can't see his accounting informations because they are connected with the same user but with space at the end.
You keep repeating that. Yes, I understand. Yes, I have read your messages. I think it's clear you either haven't read my responses, or that you haven't understood them. If "user with spaces" is given access, then it is very likely YOU that configured the server to do that. The default configuration does not have this issue. YOU BROKE THE SERVER. Now go fix it. Stop complaining about the problem. Stop repeated that "the users in the database don't have spaces". Stop trying to fix the accounting records. Stop blaming the server.
Seems like I'm subscribed for a strange problems ... :)
A large part of that is you're so stuck on talking about the problem that you don't want to understand the cause, and you don't want to implement the solution I posted in another message. Alan DeKok.
Please don't be angry. I'm trying to fix this issue because it works perfectly on FR1.1.7 Now I'm completely sure that problem is on my side but radius is on default setup. Only thing we changed is uncommenting sql statements (for simul check, author, and account.) in radiusd.conf. Case closed Have a nice day. On Jan 23, 2008 7:25 AM, Alan DeKok <aland@deployingradius.com> wrote:
Marinko Tarlac wrote:
a) the user has typed the user name with spaces
Yes. User has typed user name with space but why radius didn't ignore them?
Why would it? Spaces are perfectly valid in a user name.
I repeat, user names and all other records in database are without space. User has entered space and he can connect but he can't see his accounting informations because they are connected with the same user but with space at the end.
You keep repeating that. Yes, I understand. Yes, I have read your messages. I think it's clear you either haven't read my responses, or that you haven't understood them.
If "user with spaces" is given access, then it is very likely YOU that configured the server to do that. The default configuration does not have this issue. YOU BROKE THE SERVER. Now go fix it.
Stop complaining about the problem. Stop repeated that "the users in the database don't have spaces". Stop trying to fix the accounting records. Stop blaming the server.
Seems like I'm subscribed for a strange problems ... :)
A large part of that is you're so stuck on talking about the problem that you don't want to understand the cause, and you don't want to implement the solution I posted in another message.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Hi,
Please don't be angry. I'm trying to fix this issue because it works perfectly on FR1.1.7
if you've copied the config files direct from 1.1.7 to a 2.0.0 system then there will be quirks. wheres the full debug log? alan
It is solved now. I deleted FR .4 and migrated to .7 with fresh clean install. I didn't use the old files. Thanks A.L.M.Buxey@lboro.ac.uk wrote:
Hi,
Please don't be angry. I'm trying to fix this issue because it works perfectly on FR1.1.7
if you've copied the config files direct from 1.1.7 to a 2.0.0 system then there will be quirks.
wheres the full debug log?
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (5)
-
A.L.M.Buxey＠lboro.ac.uk -
Alan DeKok -
Kevin Bonner -
Marinko Tarlac -
tnt＠kalik.co.yu