SQL Counter Escape String !

Suman Dash suman at clydontech.com
Tue Mar 15 14:36:19 CET 2011


Hi Alan,

Looks like i am getting some meaningful debugs in fr-2.1.10. 
Undermentioned are the Counter and Debug Messages.

I used %% escape but in the query it's '%d %M %Y %H:%i:%s' . Seems like 
that's how escaping works.

I assumed that i needed to add more % but adding more doesn't help . It 
seems that more than 3% and it starts truncating . If i give %%%%% , It's
stripped to %%d %%M %%Y %%H:%%i:%%s

Case 1:


sqlcounter monthlycounter {
         counter-name = Monthly-Session-Time
                 check-name = Max-Monthly-Session
                 reply-name = Session-Timeout
                 sqlmod-inst = sql
                 key = User-Name
                 reset = never

         query = "SELECT SUM(acctsessiontime) FROM radacct where \
                 username = '%{%k}' AND acctstarttime BETWEEN \
                 (SELECT STR_TO_DATE((SELECT value FROM radcheck \
                 WHERE username = '%{%k}' AND attribute = 'Activation'), 
'%%d %%M %%Y %%H:%%i:%%s')) \
                 AND (SELECT STR_TO_DATE((SELECT value FROM radcheck 
WHERE username = '%{%k}' \
                 AND attribute = 'Expiration'), '%%d %%M %%Y %%H:%%i:%%s'))"
}


DEBUG LOGS

rlm_sqlcounter: Entering module authorize code
rlm_sqlcounter: Could not find Check item value pair
++[noresetcounter] returns noop
rlm_sqlcounter: Entering module authorize code
rlm_sqlcounter: Could not find Check item value pair
++[dailycounter] returns noop
rlm_sqlcounter: Entering module authorize code
sqlcounter_expand:  'SELECT SUM(acctsessiontime) FROM radacct where
                  username = '%{User-Name}' AND acctstarttime BETWEEN
                  (SELECT STR_TO_DATE((SELECT value FROM radcheck
                  WHERE username = '%{User-Name}' AND attribute = 
'Activation'), '%d %M %Y %H:%i:%s'))
                  AND (SELECT STR_TO_DATE((SELECT value FROM radcheck 
WHERE username = '%{User-Name}'
                  AND attribute = 'Expiration'), '%d %M %Y %H:%i:%s'))'
[monthlycounter]        expand: SELECT SUM(acctsessiontime) FROM radacct 
where
                  username = '%{User-Name}' AND acctstarttime BETWEEN
                  (SELECT STR_TO_DATE((SELECT value FROM radcheck
                  WHERE username = '%{User-Name}' AND attribute = 
'Activation'), '%d %M %Y %H:%i:%s'))
                  AND (SELECT STR_TO_DATE((SELECT value FROM radcheck 
WHERE username = '%{User-Name}'
                  AND attribute = 'Expiration'), '%d %M %Y %H:%i:%s')) 
-> SELECT SUM(acctsessiontime) FROM radacct where
                  username = 'suman' AND acctstarttime 
BETWEEN                 (SELECT STR_TO_DATE((SELECT value FROM radcheck
                  WHERE username = 'suman' AND attribute = 
'Activation'), '15 0 2011 18:001122334455:_'))
                  AND (SELECT STR_TO_DATE((SELECT value FROM radcheck 
WHERE username = 'suman'
                  AND attribute = 'Expiration'), '15 0 2011 
18:001122334455:_'))
sqlcounter_expand:  '%{sql:SELECT SUM(acctsessiontime) FROM radacct where
                  username = 'suman' AND acctstarttime BETWEEN
                  (SELECT STR_TO_DATE((SELECT value FROM radcheck
                  WHERE username = 'suman' AND attribute = 
'Activation'), '15 0 2011 18:001122334455:_'))
                  AND (SELECT STR_TO_DATE((SELECT value FROM radcheck 
WHERE username = 'suman'
                  AND attribute = 'Expiration'), '15 0 2011 
18:001122334455:_'))}'
[monthlycounter] sql_xlat
[monthlycounter]        expand: %{User-Name} -> suman
[monthlycounter] sql_set_user escaped user --> 'suman'
[monthlycounter]        expand: SELECT SUM(acctsessiontime) FROM radacct 
where
                  username = 'suman' AND acctstarttime BETWEEN
                  (SELECT STR_TO_DATE((SELECT value FROM radcheck
                  WHERE username = 'suman' AND attribute = 
'Activation'), '15 0 2011 18:001122334455:_'))
                  AND (SELECT STR_TO_DATE((SELECT value FROM radcheck 
WHERE username = 'suman'
                  AND attribute = 'Expiration'), '15 0 2011 
18:001122334455:_')) -> SELECT SUM(acctsessiontime) FROM radacct where
                  username = 'suman' AND acctstarttime BETWEEN
                  (SELECT STR_TO_DATE((SELECT value FROM radcheck
                  WHERE username = 'suman' AND attribute = 
'Activation'), '15 0 2011 18:001122334455:_'))
                  AND (SELECT STR_TO_DATE((SELECT value FROM radcheck 
WHERE username = 'suman'
                  AND attribute = 'Expiration'), '15 0 2011 
18:001122334455:_'))
rlm_sql (sql): Reserving sql socket id: 2
[monthlycounter] row[0] returned NULL
rlm_sql (sql): Released sql socket id: 2
[monthlycounter]        expand: %{sql:SELECT SUM(acctsessiontime) FROM 
radacct where
                  username = 'suman' AND acctstarttime BETWEEN
                  (SELECT STR_TO_DATE((SELECT value FROM radcheck
                  WHERE username = 'suman' AND attribute = 
'Activation'), '15 0 2011 18:001122334455:_'))
                  AND (SELECT STR_TO_DATE((SELECT value FROM radcheck 
WHERE username = 'suman'
                  AND attribute = 'Expiration'), '15 0 2011 
18:001122334455:_'))} ->
rlm_sqlcounter: No integer found in string ""
++[monthlycounter] returns noop
[expiration] Checking Expiration time: '5 Apr 2011 23:59:59'
++[expiration] returns ok
++[logintime] returns noop
++[pap] returns updated
Found Auth-Type = PAP
# Executing group from file /usr/local/etc/raddb/sites-enabled/default
+- entering group PAP {...}
[pap] login attempt with password "duman12"
[pap] Using CRYPT password "AEgNJ3NmPjAKs"
[pap] User authenticated successfully
++[pap] returns ok
# Executing section post-auth from file 
/usr/local/etc/raddb/sites-enabled/default
+- entering group post-auth {...}
[sqlippool] No Pool-Name defined.
[sqlippool]     expand: No Pool-Name defined   (did %{Called-Station-Id} 
cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) -> No 
Pool-Name defined   (did  cli 001122334455 port  user suman)
No Pool-Name defined   (did  cli 001122334455 port  user suman)
++[sqlippool] returns noop
[sql]   expand: %{User-Name} -> suman
[sql] sql_set_user escaped user --> 'suman'
[sql]   expand: %{User-Password} -> duman12
[sql]   expand: INSERT INTO radpostauth                           
(username, pass, reply, authdate)                           VALUES 
(                           '%{User-Name}',                           
'%{%{User-Password}:-%{Chap-Password}}',                           
'%{reply:Packet-Type}', '%S') -> INSERT INTO 
radpostauth                           (username, pass, reply, 
authdate)                           VALUES (                           
'suman',                           'duman12',                           
'Access-Accept', '2011-03-15 18:27:05')
rlm_sql (sql) in sql_postauth: query is INSERT INTO 
radpostauth                           (username, pass, reply, 
authdate)                           VALUES (                           
'suman',                           'duman12',                           
'Access-Accept', '2011-03-15 18:27:05')
rlm_sql (sql): Reserving sql socket id: 1
rlm_sql (sql): Released sql socket id: 1
++[sql] returns ok
++[exec] returns noop
Sending Access-Accept of id 31 to 122.175.85.117 port 12279
         Session-Timeout = 1834374
Finished request 0.
Going to the next request
Waking up in 4.9 seconds.






Case 2


sqlcounter monthlycounter {
         counter-name = Monthly-Session-Time
                 check-name = Max-Monthly-Session
                 reply-name = Session-Timeout
                 sqlmod-inst = sql
                 key = User-Name
                 reset = never

         query = "SELECT SUM(acctsessiontime) FROM radacct where \
                 username = '%{%k}' AND acctstarttime BETWEEN \
                 (SELECT STR_TO_DATE((SELECT value FROM radcheck \
                 WHERE username = '%{%k}' AND attribute = 'Activation'), 
'%%%%d %%%%M %%%%Y %%%%H:%%%%i:%%%%s')) \
                 AND (SELECT STR_TO_DATE((SELECT value FROM radcheck 
WHERE username = '%{%k}' \
                 AND attribute = 'Expiration'), '%%%%d %%%%M %%%%Y 
%%%%H:%%%%i:%%%%s'))"

}


DEBUG Logs


Listening on authentication address * port 1812
Listening on accounting address * port 1813
Listening on command file /usr/local/var/run/radiusd/radiusd.sock
Listening on authentication address 127.0.0.1 port 18120 as server 
inner-tunnel
Listening on proxy address * port 1814
Ready to process requests.
rad_recv: Access-Request packet from host 122.175.85.117 port 12893, 
id=35, length=59
         User-Name = "suman"
         User-Password = "duman12"
         Calling-Station-Id = "001122334455"
# Executing section authorize from file 
/usr/local/etc/raddb/sites-enabled/default
+- entering group authorize {...}
++[preprocess] returns ok
++[chap] returns noop
++[mschap] returns noop
++[digest] returns noop
[suffix] No '@' in User-Name = "suman", looking up realm NULL
[suffix] No such realm "NULL"
++[suffix] returns noop
[eap] No EAP-Message, not doing EAP
++[eap] returns noop
++[files] returns noop
[sql]   expand: %{User-Name} -> suman
[sql] sql_set_user escaped user --> 'suman'
rlm_sql (sql): Reserving sql socket id: 3
[sql]   expand: SELECT id, username, attribute, value, op
            FROM radcheck           WHERE username = '%{SQL-User-Name}'
            ORDER BY id -> SELECT id, username, attribute, value, op
            FROM radcheck           WHERE username = 'suman'
            ORDER BY id
[sql] User found in radcheck table
[sql]   expand: SELECT id, username, attribute, value, op
            FROM radreply           WHERE username = '%{SQL-User-Name}'
            ORDER BY id -> SELECT id, username, attribute, value, op
            FROM radreply           WHERE username = 'suman'
            ORDER BY id
[sql]   expand: SELECT groupname           FROM radusergroup
            WHERE username = '%{SQL-User-Name}'
            ORDER BY priority -> SELECT groupname
            FROM radusergroup           WHERE username = 'suman'
            ORDER BY priority
rlm_sql (sql): Released sql socket id: 3
++[sql] returns ok
rlm_sqlcounter: Entering module authorize code
rlm_sqlcounter: Could not find Check item value pair
++[noresetcounter] returns noop
rlm_sqlcounter: Entering module authorize code
rlm_sqlcounter: Could not find Check item value pair
++[dailycounter] returns noop
rlm_sqlcounter: Entering module authorize code
sqlcounter_expand:  'SELECT SUM(acctsessiontime) FROM radacct where
                  username = '%{User-Name}' AND acctstarttime BETWEEN
                  (SELECT STR_TO_DATE((SELECT value FROM radcheck
                  WHERE username = '%{User-Name}' AND attribute = 
'Activation'), '%%d %%M %%Y %%H:%%i:%%s'))
                  AND (SELECT STR_TO_DATE((SELECT value FROM radcheck 
WHERE username = '%{User-Name}'
                  AND attribute = 'Expiration'), '%%d %%M %%Y 
%%H:%%i:%%s'))'
[monthlycounter]        expand: SELECT SUM(acctsessiontime) FROM radacct 
where
                  username = '%{User-Name}' AND acctstarttime BETWEEN
                  (SELECT STR_TO_DATE((SELECT value FROM radcheck
                  WHERE username = '%{User-Name}' AND attribute = 
'Activation'), '%%d %%M %%Y %%H:%%i:%%s'))
                  AND (SELECT STR_TO_DATE((SELECT value FROM radcheck 
WHERE username = '%{User-Name}'
                  AND attribute = 'Expiration'), '%%d %%M %%Y 
%%H:%%i:%%s')) -> SELECT SUM(acctsessiontime) FROM radacct where
                  username = 'suman' AND acctstarttime BETWEEN
                  (SELECT STR_TO_DATE((SELECT value FROM radcheck
                  WHERE username = 'suman' AND attribute = 
'Activation'), '%d %M %Y %H:%i:%s'))
                  AND (SELECT STR_TO_DATE((SELECT value FROM radcheck 
WHERE username = 'suman'
                  AND attribute = 'Expiration'), '%d %M %Y %H:%i:%s'))
sqlcounter_expand:  '%{sql:SELECT SUM(acctsessiontime) FROM radacct where
                  username = 'suman' AND acctstarttime BETWEEN
                  (SELECT STR_TO_DATE((SELECT value FROM radcheck
                  WHERE username = 'suman' AND attribute = 
'Activation'), '%d %M %Y %H:%i:%s'))
                  AND (SELECT STR_TO_DATE((SELECT value FROM radcheck 
WHERE username = 'suman'
                  AND attribute = 'Expiration'), '%d %M %Y %H:%i:%s'))}'
[monthlycounter] sql_xlat
[monthlycounter]        expand: %{User-Name} -> suman
[monthlycounter] sql_set_user escaped user --> 'suman'
[monthlycounter]        expand: SELECT SUM(acctsessiontime) FROM radacct 
where
                  username = 'suman' AND acctstarttime BETWEEN
                  (SELECT STR_TO_DATE((SELECT value FROM radcheck
                  WHERE username = 'suman' AND attribute = 
'Activation'), '%d %M %Y %H:%i:%s'))
                  AND (SELECT STR_TO_DATE((SELECT value FROM radcheck 
WHERE username = 'suman'
                  AND attribute = 'Expiration'), '%d %M %Y %H:%i:%s')) 
-> SELECT SUM(acctsessiontime) FROM radacct where
                  username = 'suman' AND acctstarttime BETWEEN
                  (SELECT STR_TO_DATE((SELECT value FROM radcheck
                  WHERE username = 'suman' AND attribute = 
'Activation'), '15 0 2011 18:001122334455:_'))
                  AND (SELECT STR_TO_DATE((SELECT value FROM radcheck 
WHERE username = 'suman'
                  AND attribute = 'Expiration'), '15 0 2011 
18:001122334455:_'))
rlm_sql (sql): Reserving sql socket id: 2
[monthlycounter] row[0] returned NULL
rlm_sql (sql): Released sql socket id: 2
[monthlycounter]        expand: %{sql:SELECT SUM(acctsessiontime) FROM 
radacct where
                  username = 'suman' AND acctstarttime BETWEEN
                  (SELECT STR_TO_DATE((SELECT value FROM radcheck
                  WHERE username = 'suman' AND attribute = 
'Activation'), '%d %M %Y %H:%i:%s'))
                  AND (SELECT STR_TO_DATE((SELECT value FROM radcheck 
WHERE username = 'suman'
                  AND attribute = 'Expiration'), '%d %M %Y %H:%i:%s'))} ->
rlm_sqlcounter: No integer found in string ""
++[monthlycounter] returns noop
[expiration] Checking Expiration time: '5 Apr 2011 23:59:59'
++[expiration] returns ok
++[logintime] returns noop
++[pap] returns updated
Found Auth-Type = PAP
# Executing group from file /usr/local/etc/raddb/sites-enabled/default
+- entering group PAP {...}
[pap] login attempt with password "duman12"
[pap] Using CRYPT password "AEgNJ3NmPjAKs"
[pap] User authenticated successfully
++[pap] returns ok
# Executing section post-auth from file 
/usr/local/etc/raddb/sites-enabled/default
+- entering group post-auth {...}
[sqlippool] No Pool-Name defined.
[sqlippool]     expand: No Pool-Name defined   (did %{Called-Station-Id} 
cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) -> No 
Pool-Name defined   (did  cli 001122334455 port  user suman)
No Pool-Name defined   (did  cli 001122334455 port  user suman)
++[sqlippool] returns noop
[sql]   expand: %{User-Name} -> suman
[sql] sql_set_user escaped user --> 'suman'
[sql]   expand: %{User-Password} -> duman12
[sql]   expand: INSERT INTO radpostauth                           
(username, pass, reply, authdate)                           VALUES 
(                           '%{User-Name}',                           
'%{%{User-Password}:-%{Chap-Password}}',                           
'%{reply:Packet-Type}', '%S') -> INSERT INTO 
radpostauth                           (username, pass, reply, 
authdate)                           VALUES (                           
'suman',                           'duman12',                           
'Access-Accept', '2011-03-15 18:53:17')
rlm_sql (sql) in sql_postauth: query is INSERT INTO 
radpostauth                           (username, pass, reply, 
authdate)                           VALUES (                           
'suman',                           'duman12',                           
'Access-Accept', '2011-03-15 18:53:17')
rlm_sql (sql): Reserving sql socket id: 1
rlm_sql (sql): Released sql socket id: 1
++[sql] returns ok
++[exec] returns noop
Sending Access-Accept of id 35 to 122.175.85.117 port 12893
         Session-Timeout = 1832802
Finished request 0.
Going to the next request
Waking up in 4.9 seconds.



On 3/15/2011 3:29 PM, Alan DeKok wrote:
> Suman Dash wrote:
>>   sqlcounter monthlycounter {
> ...
>>                  WHERE username = '%{%k}' AND attribute = 'Activation'),
>> '%%%%d %%%%M %%%%Y %%%%H:%%%%i:%%%%s')) \
>    The debug log doesn't show that this string is being used.
>
>    And *again* you delete large amounts of the debug log.  Why?  It just
> makes it harder to help you.
>
>    In short: you are editing a configuration file, BUT the server isn't
> using the configuration file you're editing.  That is likely the *major*
> source of the problems you're seeing.
>
>    And don't CC me on messages to the list.  I *do* read the list.  And
> especially do NOT set "return receipt requested".  It's rude and
> annoying.  If it keeps up, I'll just delete the messages unread.
>
>    Alan DeKok.
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus signature database 5924 (20110303) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>
>



More information about the Freeradius-Users mailing list