SQL results going ... wrong

[Guy Fraser]

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 at 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