<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body text="#3366ff" bgcolor="#ffffff">
mysql> select * from tbl_check;
<br>
+----+----------+---------------------+----+----------------------+
<br>
| id | username | attribute | op | value |
<br>
+----+----------+---------------------+----+----------------------+
<br>
| 17 | suman | Crypt-Password | := | TeatgXaDQelbQ |
<br>
| 18 | suman | Calling-Station-Id | := | 001122334455 |
<br>
| 25 | suman | Expiration | := | 13 Mar 2012 21:37:23 |
<br>
| 22 | suman | Activation | := | 13 Mar 2011 21:37:23 |
<br>
| 24 | suman | Max-Monthly-Session | := | 30 |
<br>
+----+----------+---------------------+----+----------------------+
<br>
5 rows in set (0.00 sec)
<br>
<br>
mysql> SELECT SUM(acctsessiontime) FROM tbl_acct where username =
'suman' AND acctstarttime BETWEEN \
<br>
-> (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE
username = 'suman' AND attribute = 'Activation'), '%d %M %Y
%H:%i:%s')) \
<br>
-> AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE
username = 'suman' AND attribute = 'Expiration'), '%d %M %Y
%H:%i:%s'));
<br>
+----------------------+
<br>
| SUM(acctsessiontime) |
<br>
+----------------------+
<br>
| 200 |
<br>
+----------------------+
<br>
1 row in set (0.00 sec)
<br>
<br>
mysql>
<br>
<br>
Counter.SQL
<br>
<br>
sqlcounter monthlycounter {
<br>
counter-name = Monthly-Session-Time
<br>
check-name = Max-Monthly-Session
<br>
reply-name = Session-Timeout
<br>
sqlmod-inst = sql
<br>
key = User-Name
<br>
reset = never
<br>
<br>
query = "SELECT SUM(acctsessiontime) FROM tbl_acct where \
<br>
username = '%{%k}' AND acctstarttime BETWEEN \
<br>
(SELECT STR_TO_DATE((SELECT value FROM tbl_check \
<br>
WHERE username = '%{%k}' AND attribute =
'Activation'), '%%d %%M %%Y %%H:%%i:%%s')) \
<br>
AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check
WHERE username = '%{%k}' \
<br>
AND attribute = 'Expiration'), '%%d %%M %%Y
%%H:%%i:%%s'))"
<br>
<br>
<br>
DEBUG
<br>
<br>
Listening on authentication address * port 1812
<br>
Listening on accounting address * port 1813
<br>
Listening on proxy address * port 1814
<br>
Ready to process requests.
<br>
rad_recv: Access-Request packet from host 122.175.85.117 port 19169,
id=6, length=59
<br>
User-Name = "suman"
<br>
User-Password = "duman12"
<br>
Calling-Station-Id = "001122334455"
<br>
+- entering group authorize {...}
<br>
++[preprocess] returns ok
<br>
++[chap] returns noop
<br>
++[mschap] returns noop
<br>
[suffix] No '@' in User-Name = "suman", looking up realm NULL
<br>
[suffix] No such realm "NULL"
<br>
++[suffix] returns noop
<br>
[eap] No EAP-Message, not doing EAP
<br>
++[eap] returns noop
<br>
++[unix] returns notfound
<br>
++[files] returns noop
<br>
[sql] expand: %{User-Name} -> suman
<br>
[sql] sql_set_user escaped user --> 'suman'
<br>
rlm_sql (sql): Reserving sql socket id: 3
<br>
[sql] expand: SELECT id, username, attribute, value, op
FROM tbl_check WHERE username = '%{SQL-User-Name}'
<br>
ORDER BY id -> SELECT id, username, attribute, value,
op FROM tbl_check WHERE username = 'suman'
<br>
ORDER BY id
<br>
[sql] User found in radcheck table
<br>
[sql] expand: SELECT id, username, attribute, value, op
FROM tbl_reply WHERE username = '%{SQL-User-Name}'
<br>
ORDER BY id -> SELECT id, username, attribute, value,
op FROM tbl_reply WHERE username = 'suman'
<br>
ORDER BY id
<br>
[sql] expand: SELECT groupname FROM
tbl_usergroup WHERE username = '%{SQL-User-Name}'
<br>
ORDER BY priority -> SELECT groupname FROM
tbl_usergroup WHERE username = 'suman'
<br>
ORDER BY priority
<br>
[sql] expand: SELECT id, groupname, attribute, Value,
op FROM tbl_groupcheck
<br>
WHERE groupname = '%{Sql-Group}' ORDER BY id
-> SELECT id, groupname, attribute,
<br>
Value, op FROM tbl_groupcheck WHERE
groupname = 'Biz1Mbps-UL' ORDER BY id
<br>
[sql] User found in group Biz1Mbps-UL
<br>
[sql] expand: SELECT id, groupname, attribute, value,
op FROM tbl_groupreply
<br>
WHERE groupname = '%{Sql-Group}' ORDER BY id
-> SELECT id, groupname, attribute,
<br>
value, op FROM tbl_groupreply WHERE
groupname = 'Biz1Mbps-UL' ORDER BY id
<br>
rlm_sql (sql): Released sql socket id: 3
<br>
++[sql] returns ok
<br>
rlm_sqlcounter: Entering module authorize code
<br>
rlm_sqlcounter: Could not find Check item value pair
<br>
++[dailycounter] returns noop
<br>
rlm_sqlcounter: Entering module authorize code
<br>
sqlcounter_expand: 'SELECT SUM(acctsessiontime) FROM tbl_acct
where username = '%{User-Name}' AND acctstarttime
BETWEEN
<br>
(SELECT STR_TO_DATE((SELECT value FROM tbl_check
<br>
WHERE username = '%{User-Name}' AND attribute =
'Activation'), '%0d %0M %0Y %0H:%0i:%0s'))
<br>
AND (SELECT STR_TO_DATE((SELECT value FROM
tbl_check WHERE username = '%{User-Name}'
<br>
AND attribute = 'Expiration'), '%0d %0M %0Y
%0H:%0i:%0s'))'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] expand: SELECT SUM(acctsessiontime) FROM
tbl_acct where username = '%{User-Name}' AND
acctstarttime BETWEEN
<br>
(SELECT STR_TO_DATE((SELECT value FROM tbl_check
<br>
WHERE username = '%{User-Name}' AND attribute =
'Activation'), '%0d %0M %0Y %0H:%0i:%0s'))
<br>
AND (SELECT STR_TO_DATE((SELECT value FROM
tbl_check WHERE username = '%{User-Name}'
<br>
AND attribute = 'Expiration'), '%0d %0M %0Y
%0H:%0i:%0s')) -> SELECT SUM(acctsessiontime) FROM tbl_acct where
<br>
username = 'suman' AND acctstarttime
BETWEEN (SELECT STR_TO_DATE((SELECT value FROM
tbl_check
<br>
WHERE username = 'suman' AND attribute =
'Activation'), '%0d %0M %0Y %0H:%0i:%0s'))
<br>
AND (SELECT STR_TO_DATE((SELECT value FROM
tbl_check WHERE username = 'suman'
<br>
AND attribute = 'Expiration'), '%0d %0M %0Y
%0H:%0i:%0s'))
<br>
sqlcounter_expand: '%{sql:SELECT SUM(acctsessiontime) FROM tbl_acct
where username = 'suman' AND acctstarttime BETWEEN
<br>
(SELECT STR_TO_DATE((SELECT value FROM tbl_check
<br>
WHERE username = 'suman' AND attribute =
'Activation'), '%0d %0M %0Y %0H:%0i:%0s'))
<br>
AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check
WHERE username = 'suman'
<br>
AND attribute = 'Expiration'), '%0d %0M %0Y
%0H:%0i:%0s'))}'
<br>
[monthlycounter] sql_xlat
<br>
[monthlycounter] expand: %{User-Name} -> suman
<br>
[monthlycounter] sql_set_user escaped user --> 'suman'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] WARNING: Unknown variable '%0': See
'doc/variables.txt'
<br>
[monthlycounter] expand: SELECT SUM(acctsessiontime) FROM
tbl_acct where username = 'suman' AND acctstarttime
BETWEEN
<br>
(SELECT STR_TO_DATE((SELECT value FROM tbl_check
<br>
WHERE username = 'suman' AND attribute =
'Activation'), '%0d %0M %0Y %0H:%0i:%0s'))
<br>
AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check
WHERE username = 'suman'
<br>
AND attribute = 'Expiration'), '%0d %0M %0Y
%0H:%0i:%0s')) -> SELECT SUM(acctsessiontime) FROM tbl_acct where
<br>
username = 'suman' AND acctstarttime
BETWEEN (SELECT STR_TO_DATE((SELECT value FROM
tbl_check
<br>
WHERE username = 'suman' AND attribute =
'Activation'), '%0d %0M %0Y %0H:%0i:%0s'))
<br>
AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check
WHERE username = 'suman'
<br>
AND attribute = 'Expiration'), '%0d %0M %0Y
%0H:%0i:%0s'))
<br>
rlm_sql (sql): Reserving sql socket id: 2
<br>
[monthlycounter] row[0] returned NULL
<br>
rlm_sql (sql): Released sql socket id: 2
<br>
[monthlycounter] expand: %{sql:SELECT SUM(acctsessiontime)
FROM tbl_acct where username = 'suman' AND
acctstarttime BETWEEN
<br>
(SELECT STR_TO_DATE((SELECT value FROM tbl_check
<br>
WHERE username = 'suman' AND attribute =
'Activation'), '%0d %0M %0Y %0H:%0i:%0s'))
<br>
AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check
WHERE username = 'suman'
<br>
AND attribute = 'Expiration'), '%0d %0M %0Y
%0H:%0i:%0s'))} ->
<br>
rlm_sqlcounter: No integer found in string ""
<br>
++[monthlycounter] returns noop
<br>
rlm_sqlcounter: Entering module authorize code
<br>
rlm_sqlcounter: Could not find Check item value pair
<br>
++[noresetcounter] returns noop
<br>
rlm_checkval: Item Name: Calling-Station-Id, Value: 001122334455
<br>
rlm_checkval: Value Name: Calling-Station-Id, Value: 001122334455
<br>
++[checkval] returns ok
<br>
[expiration] Checking Expiration time: '13 Mar 2012 21:37:23'
<br>
++[expiration] returns ok
<br>
++[logintime] returns noop
<br>
++[pap] returns updated
<br>
Found Auth-Type = PAP
<br>
+- entering group PAP {...}
<br>
[pap] login attempt with password "duman12"
<br>
[pap] Using CRYPT encryption.
<br>
[pap] User authenticated successfully
<br>
++[pap] returns ok
<br>
+- entering group post-auth {...}
<br>
[sqlippool] No Pool-Name defined.
<br>
[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)
<br>
No Pool-Name defined (did cli 001122334455 port user suman)
<br>
++[sqlippool] returns noop
<br>
[sql] expand: %{User-Name} -> suman
<br>
[sql] sql_set_user escaped user --> 'suman'
<br>
[sql] expand: %{User-Password} -> duman12
<br>
[sql] expand: INSERT INTO tbl_postauth
(username, pass, reply, authdate) VALUES
(
'%{User-Name}',
'%{%{User-Password}:-%{Chap-Password}}',
'%{reply:Packet-Type}', '%S') -> INSERT INTO
tbl_postauth (username, pass, reply,
authdate) VALUES
( 'suman',
'duman12', 'Access-Accept', '2011-03-15
14:36:34')
<br>
rlm_sql (sql) in sql_postauth: query is INSERT INTO
tbl_postauth (username, pass, reply,
authdate) VALUES
( 'suman',
'duman12', 'Access-Accept', '2011-03-15
14:36:34')
<br>
rlm_sql (sql): Reserving sql socket id: 1
<br>
rlm_sql (sql): Released sql socket id: 1
<br>
++[sql] returns ok
<br>
++[exec] returns noop
<br>
Sending Access-Accept of id 6 to 122.175.85.117 port 19169
<br>
Session-Timeout = 31474849
<br>
Finished request 0.
<br>
Going to the next request
<br>
Waking up in 4.9 seconds.
<br>
Cleaning up request 0 ID 6 with timestamp +3
<br>
Ready to process requests.
<br>
<br>
<br>
<br>
**********************************************************
<br>
<br>
When the Counter Reset Period is monthly , %%' becomes '%1298917800
<br>
<br>
When the Counter Reset Period is Never , New Problem Arises i.e %0d
%0M %0Y %0H:%0i:%0s
<br>
<br>
<br>
Best Regards
<br>
Suman
<blockquote cite="mid:4D7F24F3.6040900@deployingradius.com"
type="cite">
<pre wrap="">Suman Dash wrote:
</pre>
<blockquote type="cite">
<pre wrap="">I have tried almost all sql escape but looks like none are working or
maybe i am missing something. I am stuck in this issue for more than 3
days and now i don't have any clue due to which i am trying to reach for
help on the mailing list.
</pre>
</blockquote>
<pre wrap="">
Hmm... the issue seems to be that the sqlcounter module does it's own
string expansion, and gets it *horribly* wrong.
As for why '%%' becomes '%1298917800', I have no idea. Posting *more*
debug output might help. What you did post was the final result of the
expansion, and didn't include *how* that expansion came about.
Alan DeKok.
__________ Information from ESET NOD32 Antivirus, version of virus signature database 5924 (20110303) __________
The message was checked by ESET NOD32 Antivirus.
<a class="moz-txt-link-freetext" href="http://www.eset.com">http://www.eset.com</a>
</pre>
</blockquote>
</body>
</html>