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