Re: SQL Counter Escape String !

Suman Dash Wed, 16 Mar 2011 03:31:20 -0700

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 everythingonce again . I was not aware that i sent you a mail. I am having anightmare and accidently i clicked Send All instead of selecting themailing 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_checkWHERE 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, opFROM tbl_check WHERE username = '%{SQL-User-Name}'ORDER BY id -> SELECT id, username, attribute, value, opFROM tbl_check WHERE username = 'suman' ORDER BY id
[sql] User found in radcheck table
[sql] expand: SELECT id, username, attribute, value, opFROM tbl_reply WHERE username = '%{SQL-User-Name}'ORDER BY id -> SELECT id, username, attribute, value, opFROM tbl_reply WHERE username = 'suman' ORDER BY id[sql] expand: SELECT groupname FROMtbl_usergroup WHERE username = '%{SQL-User-Name}'ORDER BY priority -> SELECT groupname FROMtbl_usergroup WHERE username = 'suman' ORDER BYpriority[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_groupcheckWHERE 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_groupreplyWHERE 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_acctwhere username = '%{User-Name}' AND acctstarttimeBETWEEN (SELECT STR_TO_DATE((SELECT value FROMtbl_check WHERE username = '%{User-Name}' ANDattribute = 'Activation'), '%0%0d %0%0M %0%0Y%0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECTvalue 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) FROMtbl_acct where username = '%{User-Name}' ANDacctstarttime BETWEEN (SELECT STR_TO_DATE((SELECTvalue 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((SELECTvalue 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 whereusername = 'suman' AND acctstarttime BETWEEN (SELECTSTR_TO_DATE((SELECT value FROM tbl_check WHEREusername = 'suman' AND attribute = 'Activation'), '%0%0d %0%0M %0%0Y%0%0H:%0%0i:%0%0s')) AND (SELECT STR_TO_DATE((SELECTvalue FROM tbl_check WHERE username = 'suman' ANDattribute = 'Expiration'), '%0%0d %0%0M %0%0Y %0%0H:%0%0i:%0%0s'))sqlcounter_expand: '%{sql:SELECT SUM(acctsessiontime) FROM tbl_acctwhere username = 'suman' AND acctstarttimeBETWEEN (SELECT STR_TO_DATE((SELECT value FROMtbl_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) FROMtbl_acct where username = 'suman' AND acctstarttimeBETWEEN (SELECT STR_TO_DATE((SELECT value FROMtbl_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) FROMtbl_acct where username = 'suman' AND acctstarttimeBETWEEN (SELECT STR_TO_DATE((SELECT value FROMtbl_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) FROMtbl_acct where username = 'suman' AND acctstarttimeBETWEEN (SELECT STR_TO_DATE((SELECT value FROMtbl_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 portuser 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 INTOtbl_postauth (username, pass, reply,authdate) VALUES ('suman','duman12', 'Access-Accept', '2011-03-1515:57:53')rlm_sql (sql) in sql_postauth: query is INSERT INTOtbl_postauth (username, pass, reply,authdate) VALUES ('suman','duman12', 'Access-Accept', '2011-03-1515: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 thesame in the latest release.
As for the read receipt is concerned then i am sorry in case i haveannoyed 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 virussignature 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

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

Re: SQL Counter Escape String !

Reply via email to