SQL results going ... wrong
Hi, I'm just implementing a new virtual server with a slightly complex query and sizable result set coming back in radreply. The query goes out as expected, and the MySQL reply is well-formed and looks as expected in wireshark when it comes back. But the debug output is ... interesting: Thu Apr 14 15:43:07 2011 : Info: [sql-aai] User found in radcheck table Thu Apr 14 15:43:07 2011 : Info: [sql-aai] expand: SELECT * FROM reply_aai_firstname WHERE username='%{SQL-User-Name}' UNION ALL SELECT * FROM reply_aai_lastname WHERE username='%{SQL-User-Name}' UNION ALL SELECT * FROM reply_aai_mail WHERE username='%{SQL-User-Name}' UNION ALL SELECT * FROM reply_aai_eduPersonAffiliation WHERE username='%{SQL-User-Name}' -> SELECT * FROM reply_aai_firstname WHERE username='swinter' UNION ALL SELECT * FROM reply_aai_lastname WHERE username='swinter' UNION ALL SELECT * FROM reply_aai_mail WHERE username='swinter' UNION ALL SELECT * FROM reply_aai_eduPersonAffiliation WHERE username='swinter' Thu Apr 14 15:43:07 2011 : Error: rlm_sql: Invalid operator "?x�{?(�{?@�{?D�{?<�{?D�{?Z�{?]�{?v�{?swinter" for attribute += Thu Apr 14 15:43:07 2011 : Error: rlm_sql (sql-aai): Error getting data from database Thu Apr 14 15:43:07 2011 : Error: [sql-aai] SQL query error; rejecting user Something looks like accessing memory where it better shouldn't. If I execute the xlated query on the MySQL server directly, the result looks beautiful: +----------+-----------------------+----+----------------------------------------------------------------+ | username | attribute | op | value | +----------+-----------------------+----+----------------------------------------------------------------+ | swinter | RESTENA-AAI-Attribute | += | urn:oid:2.5.4.42='Stefan' | | swinter | RESTENA-AAI-Attribute | += | urn:oid:2.5.4.4='Winter' | | swinter | RESTENA-AAI-Attribute | += | urn:oid:0.9.2342.19200300.100.1.3='stefan.winter@education.lu' | | swinter | RESTENA-AAI-Attribute | += | urn:oid:1.3.6.1.4.1.5923.1.1.1.1='member' | +----------+-----------------------+----+----------------------------------------------------------------+ So it must go wrong somewhere in the server. That same server executes many many other SQL queries of the radcheck style without issues. This is the first time I'm using a radreply query though. Version is 2.1.10. mysql client lib is so old I'm too ashamed to tell here. So... any known badnesses in MySQL/radreply? Anything I should do (besides updating mysql client libs, which has right now popped near the top of my TODO list)? Greetings, Stefan Winter -- Stefan WINTER Ingenieur de Recherche Fondation RESTENA - Réseau Téléinformatique de l'Education Nationale et de la Recherche 6, rue Richard Coudenhove-Kalergi L-1359 Luxembourg Tel: +352 424409 1 Fax: +352 422473
What character set encodings are you using for the database? I suspect the database is set UTF8 and your default character encoding on the system you are developing FreeRadius is different. You can check the MySQL like this : mysql> show variables like 'character_%' ; +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/share/mysql/charsets/ | +--------------------------+----------------------------------+ 8 rows in set (0.00 sec) On 2011-Apr-14, at 08:06, Stefan Winter wrote:
Hi,
I'm just implementing a new virtual server with a slightly complex query and sizable result set coming back in radreply. The query goes out as expected, and the MySQL reply is well-formed and looks as expected in wireshark when it comes back. But the debug output is ... interesting:
Thu Apr 14 15:43:07 2011 : Info: [sql-aai] User found in radcheck table Thu Apr 14 15:43:07 2011 : Info: [sql-aai] expand: SELECT * FROM reply_aai_firstname WHERE username='%{SQL-User-Name}' UNION ALL SELECT * FROM reply_aai_lastname WHERE username='%{SQL-User-Name}' UNION ALL SELECT * FROM reply_aai_mail WHERE username='%{SQL-User-Name}' UNION ALL SELECT * FROM reply_aai_eduPersonAffiliation WHERE username='%{SQL-User-Name}' -> SELECT * FROM reply_aai_firstname WHERE username='swinter' UNION ALL SELECT * FROM reply_aai_lastname WHERE username='swinter' UNION ALL SELECT * FROM reply_aai_mail WHERE username='swinter' UNION ALL SELECT * FROM reply_aai_eduPersonAffiliation WHERE username='swinter' Thu Apr 14 15:43:07 2011 : Error: rlm_sql: Invalid operator "?x�{?(�{?@�{?D�{?<�{?D�{?Z�{?]�{?v�{?swinter" for attribute += Thu Apr 14 15:43:07 2011 : Error: rlm_sql (sql-aai): Error getting data from database Thu Apr 14 15:43:07 2011 : Error: [sql-aai] SQL query error; rejecting user
Something looks like accessing memory where it better shouldn't.
If I execute the xlated query on the MySQL server directly, the result looks beautiful:
+----------+-----------------------+---- +----------------------------------------------------------------+ | username | attribute | op | value | +----------+-----------------------+---- +----------------------------------------------------------------+ | swinter | RESTENA-AAI-Attribute | += | urn:oid:2.5.4.42='Stefan' | | swinter | RESTENA-AAI-Attribute | += | urn:oid:2.5.4.4='Winter' | | swinter | RESTENA-AAI-Attribute | += | urn:oid:0.9.2342.19200300.100.1.3='stefan.winter@education.lu' | | swinter | RESTENA-AAI-Attribute | += | urn:oid:1.3.6.1.4.1.5923.1.1.1.1='member' | +----------+-----------------------+---- +----------------------------------------------------------------+
So it must go wrong somewhere in the server.
That same server executes many many other SQL queries of the radcheck style without issues. This is the first time I'm using a radreply query though. Version is 2.1.10. mysql client lib is so old I'm too ashamed to tell here.
So... any known badnesses in MySQL/radreply? Anything I should do (besides updating mysql client libs, which has right now popped near the top of my TODO list)?
Greetings,
Stefan Winter
-- Stefan WINTER Ingenieur de Recherche Fondation RESTENA - Réseau Téléinformatique de l'Education Nationale et de la Recherche 6, rue Richard Coudenhove-Kalergi L-1359 Luxembourg
Tel: +352 424409 1 Fax: +352 422473
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-- Guy Fraser Network Administrator The Internet Centre 1-888-450-6787 (780)450-6787
Hi,
Thu Apr 14 15:43:07 2011 : Error: rlm_sql: Invalid operator "?x�{?(�{?@�{?D�{?<�{?D�{?Z�{?]�{?v�{?swinter" for attribute += Thu Apr 14 15:43:07 2011 : Error: rlm_sql (sql-aai): Error getting data from database Thu Apr 14 15:43:07 2011 : Error: [sql-aai] SQL query error; rejecting user
Something looks like accessing memory where it better shouldn't.
What character set encodings are you using for the database?
I suspect the database is set UTF8 and your default character encoding on the system you are developing FreeRadius is different.
This does definitely not look like a character encoding issue to me. I've seen lots of these, and I'm using the same database structure all around in our production setup. And the characters being transmitted are all good old plain ASCII characters. If you check the debug output against what's being sent, you'll see striking mismatches; ' Invalid operator "..." for attribute +=' There is no attribute += - attributes are all "RESTENA-AAI-Attribute" - which is defined in my dictionaries. The quoted strange-string content contains my username swinter, but the debug output says it considers this to be part of the "operator" column. Sorry, but this is beyond character set badnesses. I'll run the same test case with sql module debug on - maybe that sheds more light into what's going wrong. Stefan -- Stefan WINTER Ingenieur de Recherche Fondation RESTENA - Réseau Téléinformatique de l'Education Nationale et de la Recherche 6, rue Richard Coudenhove-Kalergi L-1359 Luxembourg Tel: +352 424409 1 Fax: +352 422473
On 04/15/2011 06:57 AM, Stefan Winter wrote:
Hi,
Thu Apr 14 15:43:07 2011 : Error: rlm_sql: Invalid operator "?x�{?(�{?@�{?D�{?<�{?D�{?Z�{?]�{?v�{?swinter" for attribute += Thu Apr 14 15:43:07 2011 : Error: rlm_sql (sql-aai): Error getting data from database Thu Apr 14 15:43:07 2011 : Error: [sql-aai] SQL query error; rejecting user
Something looks like accessing memory where it better shouldn't.
What character set encodings are you using for the database?
I suspect the database is set UTF8 and your default character encoding on the system you are developing FreeRadius is different.
This does definitely not look like a character encoding issue to me. I've seen lots of these, and I'm using the same database structure all around in our production setup. And the characters being transmitted are all good old plain ASCII characters.
If you check the debug output against what's being sent, you'll see striking mismatches; ' Invalid operator "..." for attribute +='
There is no attribute += - attributes are all "RESTENA-AAI-Attribute" - which is defined in my dictionaries.
The quoted strange-string content contains my username swinter, but the debug output says it considers this to be part of the "operator" column. Sorry, but this is beyond character set badnesses. I'll run the same test case with sql module debug on - maybe that sheds more light into what's going wrong.
Maybe try an strace or gdb w/ breakpoint. Is there any possibility you're pulling an attribute of >253 bytes from the database, which might be stomping the stack? IIRC rlm_sql should prevent that itself, but maybe there are holes in the code.
Hi,
Maybe try an strace or gdb w/ breakpoint.
Is there any possibility you're pulling an attribute of >253 bytes from the database, which might be stomping the stack? IIRC rlm_sql should prevent that itself, but maybe there are holes in the code.
Good idea, but that wasn't it... A mix of "D'oh" and insufficient input checks by FR. My mistake was that my table had 4 columns - which contained all the value I cared for, but FreeRADIUS expects 5 - an "id" column as first. It also expects this first column to be the row denomination integer, but it got a string from me. I fixed my schema/view and things work just fine now. But: how about a sanity check for SQL along with a more adequate error message? Greetings, Stefan Winter -- Stefan WINTER Ingenieur de Recherche Fondation RESTENA - Réseau Téléinformatique de l'Education Nationale et de la Recherche 6, rue Richard Coudenhove-Kalergi L-1359 Luxembourg Tel: +352 424409 1 Fax: +352 422473
participants (3)
-
Guy Fraser -
Phil Mayers -
Stefan Winter