SQL results going ... wrong

[Stefan Winter]

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='***@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]

Hi,

Post by Stefan Winter
Thu Apr 14 15:43:07 2011 : Error: rlm_sql: Invalid operator
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]

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