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. 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')); Doesn't Work 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')); Doesn't Work All i am looking forward is a link to the proper documentation or a small example on this issue. Any help in this regard will be much appreciated. Best Regards Suman Dash On 3/15/2011 12:02 PM, Suman Dash wrote:

New Modified 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 :

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'), '%1298917800d %1298917800M %1298917800Y %1298917800H:%1298917800i:%1298917800s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{User-Name}' AND attribute = 'Expiration'), '%1298917800d %1298917800M %1298917800Y %1298917800H:%1298917800i:%1298917800s'));' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': 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'), '%1298917800d %1298917800M %1298917800Y %1298917800H:%1298917800i:%1298917800s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = '%{User-Name}' AND attribute = 'Expiration'), '%1298917800d %1298917800M %1298917800Y %1298917800H:%1298917800i:%1298917800s')); -> 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'), '%1298917800d %1298917800M %1298917800Y %1298917800H:%1298917800i:%1298917800s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' 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'), '%1298917800d %1298917800M %1298917800Y %1298917800H:%1298917800i:%1298917800s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%1298917800d %1298917800M %1298917800Y %1298917800H:%1298917800i:%1298917800s'));}' [monthlycounter] sql_xlat [monthlycounter] expand: %{User-Name} -> suman [monthlycounter] sql_set_user escaped user --> 'suman' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': See 'doc/variables.txt' [monthlycounter] WARNING: Unknown variable '%1': 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'), '%1298917800d %1298917800M %1298917800Y %1298917800H:%1298917800i:%1298917800s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%1298917800d %1298917800M %1298917800Y %1298917800H:%1298917800i:%1298917800s')); -> 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'), '%1298917800d %1298917800M %1298917800Y %1298917800H:%1298917800i:%1298917800s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND 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'), '%1298917800d %1298917800M %1298917800Y %1298917800H:%1298917800i:%1298917800s')) AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username = 'suman' AND attribute = 'Expiration'), '%1298917800d %1298917800M %1298917800Y %1298917800H:%1298917800i:%1298917800s'));} -> rlm_sqlcounter: No integer found in string "" ++[monthlycounter] returns noop

On Tue, Mar 15, 2011 at 11:41 AM, Alan DeKok<aland@deployingradius.com> wrote:

...

Suman Dash wrote:

...

Please anyone advice me the way to escape run-time variables in freeradius. I am using STR_TO_DATE and freeradius run-time variable is over-riding the mysql time variables Yes... that's what it does.

...

I have tried escaping as per the thread http://freeradius.1045715.n5.nabble.com/Changing-the-format-of-a-date-attrib... <sigh> What's wrong with reading the documentation?

...

But no luck ..

My Entire Counter is : ... which doesn't follow the escaping rules of either the above message, or the documentation.

Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

__________ Information from ESET NOD32 Antivirus, version of virus signature database 5924 (20110303) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

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