Hi Guys Well, my lovely users have managed to find a bug. I am trying to figure out at present if I am doing something wrong in sqlippool or if there is a general problem with rlm_sql or more specifically rlm_sql_postgresql. I have 2 users sending me weird usernames. One is "?AZq?/?" and the other is "*?Z?�?-�" Now, I authorise my users on this system based on other attributes, and do no actual authentication so the username is ignored. I could just delete the username part from the queries and it would solve the problem for me, but thats beside the point. The main sql auth and acct s working fine, however sqlippool is throwing the following error: Error: sqlippool_command: database query error in: 'UPDATE radippool SET expiry_time = 'now'::timestamp(0) + '3600 seconds'::interval WHERE nasipaddress = '217.31.232.36' AND pool_key = '038153' AND username = '?AZq?/?'' AND callingstationid = 'username' AND framedipaddress = '10.11.10.28'' The errors Postgresql shows in the log are: 2007-02-05 14:22:59 EET saaas raduser ERROR: invalid byte sequence for encoding "UTF8": 0x85 2007-02-05 14:23:26 EET saaas raduser ERROR: invalid byte sequence for encoding "UTF8": 0x87 The actual query in question is: alive-update = "UPDATE radippool \ SET expiry_time = 'now'::timestamp(0) + '${lease-duration} seconds'::interval \ WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' AND username = '%{SQL-User-Name}' \ AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'" Does anyone know what is going wrong? -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
Peter Nixon wrote:
The errors Postgresql shows in the log are: 2007-02-05 14:22:59 EET saaas raduser ERROR: invalid byte sequence for encoding "UTF8": 0x85 2007-02-05 14:23:26 EET saaas raduser ERROR: invalid byte sequence for encoding "UTF8": 0x87
Ah, yes. The standard encoding problems. RADIUS usernames are supposed to be UTF-8, but as you can see, the user isn't following that. The solution is probably to hack "sql_safe_chars" to escape characters above 0x80, or to have it validate UTF-8 strings... and then escape non-UTF-8 characers. Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog
On Mon 05 Feb 2007 14:50, Alan DeKok wrote:
Peter Nixon wrote:
The errors Postgresql shows in the log are: 2007-02-05 14:22:59 EET saaas raduser ERROR: invalid byte sequence for encoding "UTF8": 0x85 2007-02-05 14:23:26 EET saaas raduser ERROR: invalid byte sequence for encoding "UTF8": 0x87
Ah, yes. The standard encoding problems.
RADIUS usernames are supposed to be UTF-8, but as you can see, the user isn't following that.
The solution is probably to hack "sql_safe_chars" to escape characters above 0x80, or to have it validate UTF-8 strings... and then escape non-UTF-8 characers.
Eeek. The user? Shouldn't the NAS be checking this then? I am wondering why I am only getting the error from sqlippool and not from the sql accounting or auth queries. It makes me think that we are not doing something correctly in sqlippool. If you have a moment would you be able to look at the sqlippool code and tell me if we are doing something incredibly stupid? Regards -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
Peter Nixon wrote:
The solution is probably to hack "sql_safe_chars" to escape characters above 0x80, or to have it validate UTF-8 strings... and then escape non-UTF-8 characers.
Eeek. The user? Shouldn't the NAS be checking this then?
Nope. NASes are dumb.
I am wondering why I am only getting the error from sqlippool and not from the sql accounting or auth queries. It makes me think that we are not doing something correctly in sqlippool. If you have a moment would you be able to look at the sqlippool code and tell me if we are doing something incredibly stupid?
Last I looked at it, it seemed fine. I'll go check again. Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog
On Tue 06 Feb 2007 08:49, Alan DeKok wrote:
Peter Nixon wrote:
The solution is probably to hack "sql_safe_chars" to escape characters above 0x80, or to have it validate UTF-8 strings... and then escape non-UTF-8 characers.
Eeek. The user? Shouldn't the NAS be checking this then?
Nope. NASes are dumb.
Yep. And users?
I am wondering why I am only getting the error from sqlippool and not from the sql accounting or auth queries. It makes me think that we are not doing something correctly in sqlippool. If you have a moment would you be able to look at the sqlippool code and tell me if we are doing something incredibly stupid?
Last I looked at it, it seemed fine. I'll go check again.
Thanks. -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
participants (2)
-
Alan DeKok -
Peter Nixon