rlm_sql: %{sql:<long query>} - length limit

[Milan Holub]

Hi Alan and others, 

using cvs head from yesterday...

I have some query in radgroupcheck for some custom attribute defined in
dictionary:

dictionary:
# test_query
ATTRIBUTE       test_query      3014    string

select * from radgroupcheck where id=67;
+----+-----------+------------+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | GroupName | Attribute  | op | Value                                                                                                                                                                                                                                                                                                                                                                                                    |
+----+-----------+------------+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 67 | hotzone   | test_query | := | `%{sql:select charge, charge_plan.name as 'charge_plan', zone.name as zone from charge_plan, zone, zone_definition where (nasid=(select nas.id from nas where nas.nasname='%{NAS-IP-Address}' and nas.ports='%{NAS-Port}') or nasid is null)  and batchid=(select batchid from card where username='%{SQL-User-Name}') and charge_planid=charge_plan.id and zoneid=zone.id order by nasid desc limit 1}` |
+----+-----------+------------+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

I wanted to use the value of test_query attribute later in rlm_perl module(as
I do it for my other attributes configured from DB). 
My approach is to fetch all neccesary data into "custom" attributes and
in rlm_perl I'm doing just simple "decissions" based on custom
attributes. I do not want to connect from within perl to database to
fetch those values(new DB connections, using other perl modules,
performance hit...)

My problem:
- it looks like Value field in radgroupcheck is limited by 253
  characters
==> I did:
mysql> alter table radgroupcheck modify column Value varchar(1024);
but still the possible length of string to be xlated is limited by
MAX_STRING_LEN rather than by MAX_QUERY_LEN...

rlm_sql (sql): User found in group hotzone
radius_xlat:  'SELECT id, GroupName, Attribute, Value, op           FROM radgroupreply           WHERE GroupName = 'hotzone'           ORDER BY id'
radius_xlat: Running registered xlat function of module sql for string 'select charge, charge_plan.name as 'charge_plan', zone.name as zone from charge_plan, zone, zone_definition where (nasid=(select nas.id from nas where nas.nasname='%{NAS-IP-Address}' and nas.ports='%{NAS-Port}') or nasid is null)  and batchid=(sel'
rlm_sql (sql): - sql_xlat
radius_xlat:  'pexcmp'
rlm_sql (sql): sql_set_user escaped user --> 'pexcmp'
radius_xlat:  'select charge, charge_plan.name as 'charge_plan', zone.name as zone from charge_plan, zone, zone_definition where (nasid=(select nas.id from nas where nas.nasname='193.247.122.178' and nas.ports='5280') or nasid is null)  and batchid=(sel'
rlm_sql (sql): Reserving sql socket id: 2
rlm_sql_mysql: MYSQL check_error: 1064 received
rlm_sql (sql): database query error, select charge, charge_plan.name as 'charge_plan', zone.name as zone from charge_plan, zone, zone_definition where (nasid=(select nas.id from nas where nas.nasname='193.247.122.178' and nas.ports='5280') or nasid is null)  and batchid=(sel: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
rlm_sql (sql): Released sql socket id: 2


I did not test yet but I think the string length limit is common for all
%{sql:} dynamic strings(not only for those in [rad|group][check|reply]
tables.).

Is there a way how to specify more complex(longer) sql queries in freeradius
configuration?

Please advise.


Milan Holub
holub (at) thenet (dot) ch

--------------------------------------
 TheNet-Internet Services AG,
 im Bernertechnopark, Morgenstr. 129
 CH-3018, Bern, Switzerland
 031 998 4333, Fax 031 998 4330
 http://www.thenet.ch
 http://wlan.thenet.ch
--------------------------------------