rlm_sql: Bug in stripping output of dynamic strings {sql:...}

[Milan Holub]

Hi all,

- latest CVS head
- mysql  Ver 14.7 Distrib 4.1.8, for pc-linux-gnu (i386)
- FreeRADIUS Version 2.0.0-pre0, for host i386-pc-linux-gnu, built on
  Apr 13 2007 at 10:11:51

I'm using dynamic variables like {sql:<sql statement>} throughout my
configuration to fetch data from the DB.
For example acct_users:
DEFAULT Acct-Status-Type == Start
	session_count := "%{sql:select count(*) from radacct where username='%{SQL-User-Name}'"

or radgroupcheck table:
+----+-----------+---------------+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | GroupName | Attribute     | op | Value
|
+----+-----------+---------------+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 44 | config    | session_count | := | `%{sql:select count(*) from radacct where username='%{SQL-User-Name}'}`
|
| 47 | config    | product_code  | := | `%{sql:<retun_some_string>}` |
+----+-----------+---------------+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

or even in sqlcounter:

        sqlcounter noresetcounterflat {
                counter-name = Max-All-Session-Time-Flat
                check-name = Max-All-Session-Flat
                sqlmod-inst = sql
                key = User-Name
                reset = never
                query = "SELECT UNIX_TIMESTAMP()-UNIX_TIMESTAMP(AcctStartTime) FROM radacct WHERE username='%{%k}' ORDER BY radacctid limit 1"
        }


Unfortunately I'm getting the output stripped by last character(byte):
instead of getting 37 for session_count I get 3, instead of getting 1563
for noresetcounterflat I get 156, instead of getting "S3H" for product_code 
I get "S3". When the query returns 1 character I get empty output.

>>> BEGIN DEBUG
radius_xlat: Running registered xlat function of module sql for string
'select count(*) from radacct where username='%{SQL-User-Name}''
rlm_sql (sql): - sql_xlat
radius_xlat:  'fkafvt'
rlm_sql (sql): sql_set_user escaped user --> 'fkafvt'
radius_xlat:  'select count(*) from radacct where username='fkafvt''
rlm_sql (sql): Reserving sql socket id: 2
rlm_sql (sql): - sql_xlat finished
rlm_sql (sql): Released sql socket id: 2
radius_xlat:  '3'
>>>END DEBUG

DB query clearly shows:
mysql> select count(*) from radacct where username = 'fkafvt';
+----------+
| count(*) |
+----------+
|       37 |
+----------+
1 row in set (0.00 sec)

This behaviour is the same no matter in which part of config the dynamic
string appears(radgroupcheck, acct_users, sqlcounter configuration).

My workaround for this was to output some additional dummy character
from within mysql by surrounding the queries by mysql concat function:
%{sql:select concat((select count(*) from radacct where %username='%{SQL-User-Name}'),'\n')}
ie:
%{sql: select concat((<your query>), '\n')}


I hope this could help someone until the code is fixed...


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