mysql> select * from tbl_check;
+----+----------+---------------------+----+----------------------+
| id | username | attribute           | op | value                |
+----+----------+---------------------+----+----------------------+
| 17 | suman    | Crypt-Password      | := | TeatgXaDQelbQ        |
| 18 | suman    | Calling-Station-Id  | := | 001122334455         |
| 25 | suman    | Expiration          | := | 13 Mar 2012 21:37:23 |
| 22 | suman    | Activation          | := | 13 Mar 2011 21:37:23 |
| 24 | suman    | Max-Monthly-Session | := | 30                   |
+----+----------+---------------------+----+----------------------+
5 rows in set (0.00 sec)

mysql> SELECT SUM(acctsessiontime) FROM tbl_acct where username = 'suman' AND acctstarttime BETWEEN \
    -> (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Activation'), '%d %M %Y %H:%i:%s')) \
    -> AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%d %M %Y %H:%i:%s'));
+----------------------+
| SUM(acctsessiontime) |
+----------------------+
|                  200 |
+----------------------+
1 row in set (0.00 sec)

mysql>

Counter.SQL

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 tbl_acct where \
                username = '%{%k}' AND acctstarttime BETWEEN \
                (SELECT STR_TO_DATE((SELECT value FROM tbl_check \
                WHERE username = '%{%k}' AND attribute = 'Activation'), '%%d %%M %%Y %%H:%%i:%%s')) \
                AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{%k}' \
                AND attribute = 'Expiration'), '%%d %%M %%Y %%H:%%i:%%s'))"


DEBUG

Listening on authentication address * port 1812
Listening on accounting address * port 1813
Listening on proxy address * port 1814
Ready to process requests.
rad_recv: Access-Request packet from host 122.175.85.117 port 19169, id=6, length=59
        User-Name = "suman"
        User-Password = "duman12"
        Calling-Station-Id = "001122334455"
+- entering group authorize {...}
++[preprocess] returns ok
++[chap] returns noop
++[mschap] 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
++[unix] returns notfound
++[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 tbl_check           WHERE username = '%{SQL-User-Name}'
           ORDER BY id -> SELECT id, username, attribute, value, op           FROM tbl_check           WHERE username = 'suman'
           ORDER BY id
[sql] User found in radcheck table
[sql]   expand: SELECT id, username, attribute, value, op           FROM tbl_reply           WHERE username = '%{SQL-User-Name}'
           ORDER BY id -> SELECT id, username, attribute, value, op           FROM tbl_reply           WHERE username = 'suman'
           ORDER BY id
[sql]   expand: SELECT groupname           FROM tbl_usergroup           WHERE username = '%{SQL-User-Name}'
           ORDER BY priority -> SELECT groupname           FROM tbl_usergroup           WHERE username = 'suman'
           ORDER BY priority
[sql]   expand: SELECT id, groupname, attribute,           Value, op           FROM tbl_groupcheck
           WHERE groupname = '%{Sql-Group}'           ORDER BY id -> SELECT id, groupname, attribute,
           Value, op           FROM tbl_groupcheck           WHERE groupname = 'Biz1Mbps-UL'           ORDER BY id
[sql] User found in group Biz1Mbps-UL
[sql]   expand: SELECT id, groupname, attribute,           value, op           FROM tbl_groupreply
           WHERE groupname = '%{Sql-Group}'           ORDER BY id -> SELECT id, groupname, attribute,
           value, op           FROM tbl_groupreply           WHERE groupname = 'Biz1Mbps-UL'           ORDER BY id
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
++[dailycounter] returns noop
rlm_sqlcounter: Entering module authorize code
sqlcounter_expand:  'SELECT SUM(acctsessiontime) FROM tbl_acct where                 username = '%{User-Name}' AND acctstarttime BETWEEN
                 (SELECT STR_TO_DATE((SELECT value FROM tbl_check
                 WHERE username = '%{User-Name}' AND attribute = 'Activation'), '%0d %0M %0Y %0H:%0i:%0s'))
                 AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{User-Name}'
                 AND attribute = 'Expiration'), '%0d %0M %0Y %0H:%0i:%0s'))'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter]        expand: SELECT SUM(acctsessiontime) FROM tbl_acct where                 username = '%{User-Name}' AND acctstarttime BETWEEN
                 (SELECT STR_TO_DATE((SELECT value FROM tbl_check
                 WHERE username = '%{User-Name}' AND attribute = 'Activation'), '%0d %0M %0Y %0H:%0i:%0s'))
                 AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{User-Name}'
                 AND attribute = 'Expiration'), '%0d %0M %0Y %0H:%0i:%0s')) -> SELECT SUM(acctsessiontime) FROM tbl_acct where
                 username = 'suman' AND acctstarttime BETWEEN                 (SELECT STR_TO_DATE((SELECT value FROM tbl_check
                 WHERE username = 'suman' AND attribute = 'Activation'), '%0d %0M %0Y %0H:%0i:%0s'))
                 AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman'
                 AND attribute = 'Expiration'), '%0d %0M %0Y %0H:%0i:%0s'))
sqlcounter_expand:  '%{sql:SELECT SUM(acctsessiontime) FROM tbl_acct where                 username = 'suman' AND acctstarttime BETWEEN
                (SELECT STR_TO_DATE((SELECT value FROM tbl_check
                WHERE username = 'suman' AND attribute = 'Activation'), '%0d %0M %0Y %0H:%0i:%0s'))
                AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman'
                AND attribute = 'Expiration'), '%0d %0M %0Y %0H:%0i:%0s'))}'
[monthlycounter] sql_xlat
[monthlycounter]        expand: %{User-Name} -> suman
[monthlycounter] sql_set_user escaped user --> 'suman'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter] WARNING: Unknown variable '%0': See 'doc/variables.txt'
[monthlycounter]        expand: SELECT SUM(acctsessiontime) FROM tbl_acct where                 username = 'suman' AND acctstarttime BETWEEN
                (SELECT STR_TO_DATE((SELECT value FROM tbl_check
                WHERE username = 'suman' AND attribute = 'Activation'), '%0d %0M %0Y %0H:%0i:%0s'))
                AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman'
                AND attribute = 'Expiration'), '%0d %0M %0Y %0H:%0i:%0s')) -> SELECT SUM(acctsessiontime) FROM tbl_acct where
                username = 'suman' AND acctstarttime BETWEEN                 (SELECT STR_TO_DATE((SELECT value FROM tbl_check
                WHERE username = 'suman' AND attribute = 'Activation'), '%0d %0M %0Y %0H:%0i:%0s'))
                AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman'
                AND attribute = 'Expiration'), '%0d %0M %0Y %0H:%0i:%0s'))
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 tbl_acct where                 username = 'suman' AND acctstarttime BETWEEN
                (SELECT STR_TO_DATE((SELECT value FROM tbl_check
                WHERE username = 'suman' AND attribute = 'Activation'), '%0d %0M %0Y %0H:%0i:%0s'))
                AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman'
                AND attribute = 'Expiration'), '%0d %0M %0Y %0H:%0i:%0s'))} ->
rlm_sqlcounter: No integer found in string ""
++[monthlycounter] returns noop
rlm_sqlcounter: Entering module authorize code
rlm_sqlcounter: Could not find Check item value pair
++[noresetcounter] returns noop
rlm_checkval: Item Name: Calling-Station-Id, Value: 001122334455
rlm_checkval: Value Name: Calling-Station-Id, Value: 001122334455
++[checkval] returns ok
[expiration] Checking Expiration time: '13 Mar 2012 21:37:23'
++[expiration] returns ok
++[logintime] returns noop
++[pap] returns updated
Found Auth-Type = PAP
+- entering group PAP {...}
[pap] login attempt with password "duman12"
[pap] Using CRYPT encryption.
[pap] User authenticated successfully
++[pap] returns ok
+- 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 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')
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')
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 6 to 122.175.85.117 port 19169
        Session-Timeout = 31474849
Finished request 0.
Going to the next request
Waking up in 4.9 seconds.
Cleaning up request 0 ID 6 with timestamp +3
Ready to process requests.



**********************************************************

When the Counter Reset Period is monthly , %%' becomes '%1298917800

When the Counter Reset Period is Never , New Problem Arises i.e %0d %0M %0Y %0H:%0i:%0s


Best Regards
Suman 
Suman Dash wrote:

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.

  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.

http://www.eset.com