SQL Counter Escape String !
Suman Dash
suman at clydontech.com
Wed Mar 16 11:22:18 CET 2011
Hi Alan,
Did you managed to look into the issue ?
or maybe any hints on how to use DATETIME in Expiration instead of String ?
Regads
Suman
On 3/15/2011 4:04 PM, Suman Dash wrote:
> Dear Alan,
>
> I have not removed any debug messages. I will try to put everything
> once again . I was not aware that i sent you a mail. I am having a
> nightmare and accidently i clicked Send All instead of selecting the
> mailing list.
>
> 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 21658,
> id=10, 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'), '%0%0d %0%0M %0%0Y
> %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT
> value FROM tbl_check WHERE username = '%{User-Name}'
> AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%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] 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'), '%0%0d %0%0M %0%0Y
> %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT
> value FROM tbl_check WHERE username = '%{User-Name}'
> AND attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%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'), '%0%0d %0%0M %0%0Y
> %0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECT
> value FROM tbl_check WHERE username = 'suman' AND
> attribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%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'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s'))
> AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username =
> 'suman' AND attribute = 'Expiration'), '%0%0d %0%0M
> %0%0Y %0%0H:%0%0i:%0%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] 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'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s'))
> AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username =
> 'suman' AND attribute = 'Expiration'), '%0%0d %0%0M
> %0%0Y %0%0H:%0%0i:%0%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'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s'))
> AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username =
> 'suman' AND attribute = 'Expiration'), '%0%0d %0%0M
> %0%0Y %0%0H:%0%0i:%0%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'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s'))
> AND (SELECT STR_TO_DATE((SELECT value FROM tbl_check WHERE username =
> 'suman' AND attribute = 'Expiration'), '%0%0d %0%0M
> %0%0Y %0%0H:%0%0i:%0%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
> 15:57:53')
> rlm_sql (sql) in sql_postauth: query is INSERT INTO
> tbl_postauth (username, pass, reply,
> authdate) VALUES (
> 'suman',
> 'duman12', 'Access-Accept', '2011-03-15
> 15:57:53')
> 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 10 to 122.175.85.117 port 21658
> Session-Timeout = 31469970
> Finished request 0.
> Going to the next request
> Waking up in 4.9 seconds.
> Cleaning up request 0 ID 10 with timestamp +3
> Ready to process requests.
>
> ************************************************************************************
>
> The above is the complete log , nothing removed . As you can see %0%0d
> %0%0M %0%0Y %0%0H:%0%0i:%0%0s which is nothing but %%%%d %%%M %%%%Y
> %%%%H ............
> I am using freeradius 2.1.8 and now i am in a process of checking the
> same in the latest release.
>
> As for the read receipt is concerned then i am sorry in case i have
> annoyed you in any way.
>
> Best Regards
> Suman Dash
>
>
>
>
> 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
>>
>>
>>
>>
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 5924 (20110303) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
> -
> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20110316/e001b889/attachment.html>
More information about the Freeradius-Users
mailing list