SQL Counter Escape String !

[Suman Dash]

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 at 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-attribute-tt2775323.html#a2775328
>>   <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
>
>
>
>