* Thanks for your fix. Now Xlat working but server didn't give correct *>* response. I was used following attribute values and operators in my *>* database; *> >* 15GB_Quota_2048KBurst Mikrotik-Xmit-Limit := `%{sql:SELECT 100000}` *>* 15GB_Quota_2048KBurst Mikrotik-Recv-Limit := `%{sql:SELECT *>* trafsumdw('%{User-Name}',15360)}`
Hello Alan, Thanks for reply. But as you can see prior debug xlat in sql work with back quotes (` `) While i was tried to add xlat between "" in this case freeradius gives following error; (0) sql - Executing select query: SELECT id, groupname, attribute, value, o p FROM radgroupreply WHERE groupname = '15GB_Quota_2048KBurst' ORDER BY id (0) sql - ERROR: Error parsing value: Unknown or invalid value "%{sql:SELEC T 100000}" for attribute Mikrotik-Xmit-Limit (0) sql - ERROR: Error parsing user data from database result (0) sql - ERROR: Error retrieving reply pairs for group 15GB_Quota_2048KBur st But while i change back to back quote its seem execute query but not replied as value. I think we miss some part.. Also i download github version 3.1.x and this behavior is same. Could you check please. Debug output for Radius 3.1.x; (1) sql - EXPAND SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = ' %{SQL-Group}' ORDER BY id (1) sql - --> SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '15G B_Quota_2048KBurst' ORDER BY id (1) sql - Executing select query: SELECT id, groupname, attribute, value, op FROM radgroupreply WH ERE groupname = '15GB_Quota_2048KBurst' ORDER BY id (1) sql - Group "15GB_Quota_2048KBurst": Merging reply items (1) sql - &Mikrotik-Recv-Limit := "`%{sql:SELECT trafsumdw('%{User-Name}',15360)}`" (1) sql - &Mikrotik-Xmit-Limit := "`%{sql:SELECT 100000}`" (1) sql - &Mikrotik-Rate-Limit := "192k/768k 512k/2048k 160k/1024k 30/30" (1) sql - &Acct-Interim-Interval := 100 (1) sql - EXPAND `%{sql:SELECT trafsumdw('%{User-Name}',15360)}` (1) sql - 1 of 1 connections in use. You may need to increase "spare" (1) sql - Opening additional connection (8), 1 of 31 pending slots used rlm_sql_mysql - Starting connect to MySQL server rlm_sql_mysql - Connected to database 'radius' on Localhost via UNIX socket, server version 5.5.47-0+de b8u1, protocol version 10 (1) sql - Reserved connection (8) (1) sql - Released connection (8) (1) sql - Need 1 more connections to reach 10 spares (1) sql - Opening additional connection (9), 1 of 30 pending slots used rlm_sql_mysql - Starting connect to MySQL server rlm_sql_mysql - Connected to database 'radius' on Localhost via UNIX socket, server version 5.5.47-0+de b8u1, protocol version 10 (1) sql - EXPAND %{User-Name} (1) sql - --> dseral (1) sql - SQL-User-Name set to 'dseral' (1) sql - Reserved connection (8) (1) sql - Executing select query: SELECT trafsumdw('dseral',15360) (1) sql - Released connection (8) (1) sql - --> `4294967290` (1) sql - EXPAND `%{sql:SELECT 100000}` (1) sql - EXPAND %{User-Name} (1) sql - --> dseral (1) sql - SQL-User-Name set to 'dseral' (1) sql - Reserved connection (9) (1) sql - Executing select query: SELECT 100000 (1) sql - Released connection (9) (1) sql - --> `100000` (1) sql - Released connection (7) (1) sql (ok) (1) Sent Access-Accept Id 192 from 127.0.0.1:1812 to 127.0.0.1:45590 via lo length 0 (1) Mikrotik-Recv-Limit = 0 (1) Mikrotik-Xmit-Limit = 0 (1) Mikrotik-Rate-Limit = "192k/768k 512k/2048k 160k/1024k 30/30" (1) Acct-Interim-Interval = 100 (1) Session-Timeout = 30695059 (1) Finished request Thanks. devrim On Mar 5, 2016, at 4:54 AM, Devrim Seral <dseral at gmail.com <http://lists.freeradius.org/mailman/listinfo/freeradius-users>> wrote: * Use double quotes "". Not back quotes ``. See "man unlang" for why. Alan DeKok. On Thu, Mar 3, 2016 at 9:10 PM, Devrim Seral <dseral@gmail.com> wrote:
Hello freeradius Users, I have some strange problem with SQL Xlat that i haven't find out the way to fix it.
I was used freeradius 2.x before and my SQL Xlat working. But when i was switched to 3.0.11 my SQL Xlat not working anymore.
As you can see following debug log has; sql: ERROR: Error marking pair for xlat sql: ERROR: Error parsing user data from database result sql: ERROR: Error retrieving reply pairs for group 15GB_Quota_2048KBurst
My database contains following lines in radgroupreply table;
15GB_Quota_2048KBurst Mikrotik-Recv-Limit := `%{sql:SELECT trafsumdw('%{User-Name}',15360)}` 15GB_Quota_2048KBurst Mikrotik-Xmit-Limit := `%{sql:SELECT trafsumup('%{User-Name}',3840)}`
As i understand %{sql: SQL } routines has a problem. How can i fix it.. Thanks devrim
Following part have my debug output;
radiusd: FreeRADIUS Version 3.0.11, for host x86_64-pc-linux-gnu, built on Feb 13 2016 at 16:58:54
(4) Received Access-Request Id 161 from 127.0.0.1:38043 to 127.0.0.1:1812 length 76 (4) User-Name = "dseral" (4) User-Password = "123" (4) NAS-IP-Address = 127.0.1.1 (4) NAS-Port = 111 (4) Message-Authenticator = 0x01a430a9fdf69589934e577266f130f2 (4) # Executing section authorize from file /etc/freeradius/sites-enabled/default (4) authorize { (4) policy filter_username { (4) if (&User-Name) { (4) if (&User-Name) -> TRUE (4) if (&User-Name) { (4) if (&User-Name =~ / /) { (4) if (&User-Name =~ / /) -> FALSE (4) if (&User-Name =~ /@[^@]*@/ ) { (4) if (&User-Name =~ /@[^@]*@/ ) -> FALSE (4) if (&User-Name =~ /\.\./ ) { (4) if (&User-Name =~ /\.\./ ) -> FALSE (4) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) { (4) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) -> FALSE
(4) if (&User-Name =~ /\.$/) { (4) if (&User-Name =~ /\.$/) -> FALSE (4) if (&User-Name =~ /@\./) { (4) if (&User-Name =~ /@\./) -> FALSE (4) } # if (&User-Name) = notfound (4) } # policy filter_username = notfound (4) [preprocess] = ok (4) [chap] = noop (4) [mschap] = noop (4) [digest] = noop (4) suffix: Checking for suffix after "@" (4) suffix: No '@' in User-Name = "dseral", looking up realm NULL (4) suffix: No such realm "NULL" (4) [suffix] = noop (4) eap: No EAP-Message, not doing EAP (4) [eap] = noop (4) [files] = noop (4) sql: EXPAND %{User-Name} (4) sql: --> dseral (4) sql: SQL-User-Name set to 'dseral'
rlm_sql (sql): You probably need to lower "min" rlm_sql_mysql: Socket destructor called, closing socket rlm_sql (sql): Closing connection (10): Hit idle_timeout, was idle for 4561 seconds rlm_sql (sql): You probably need to lower "min" rlm_sql_mysql: Socket destructor called, closing socket rlm_sql (sql): Closing connection (8): Hit idle_timeout, was idle for 4561 seconds rlm_sql (sql): You probably need to lower "min" rlm_sql_mysql: Socket destructor called, closing socket rlm_sql (sql): 0 of 0 connections in use. You may need to increase "spare" rlm_sql (sql): Opening additional connection (11), 1 of 32 pending slots used rlm_sql_mysql: Starting connect to MySQL server rlm_sql_mysql: Connected to database 'radius' on Localhost via UNIX socket, server version 5.5.47-0+deb8u1, protocol version 10 rlm_sql (sql): Reserved connection (11) (4) sql: EXPAND SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id (4) sql: --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'dseral' ORDER BY id (4) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'dseral' ORDER BY id (4) sql: User found in radcheck table (4) sql: Conditional check items matched, merging assignment check items (4) sql: Cleartext-Password := "123" (4) sql: Expiration := "Feb 24 2017 00:00:00 EET" (4) sql: EXPAND SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id (4) sql: --> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'dseral' ORDER BY id (4) sql: Executing select query: SELECT id, username, attribute, value, op FROM radreply WHERE username = 'dseral' ORDER BY id (4) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority (4) sql: --> SELECT groupname FROM radusergroup WHERE username = 'dseral' ORDER BY priority (4) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'dseral' ORDER BY priority (4) sql: User found in the group table (4) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{SQL-Group}' ORDER BY id (4) sql: --> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '15GB_Quota_2048KBurst' ORDER BY id (4) sql: Executing select query: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '15GB_Quota_2048KBurst' ORDER BY id (4) sql: Group "15GB_Quota_2048KBurst": Conditional check items matched (4) sql: Group "15GB_Quota_2048KBurst": Merging assignment check items (4) sql: Simultaneous-Use := 1 (4) sql: EXPAND SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{SQL-Group}' ORDER BY id (4) sql: --> SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '15GB_Quota_2048KBurst' ORDER BY id (4) sql: Executing select query: SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '15GB_Quota_2048KBurst' ORDER BY id (4) sql: ERROR: Error marking pair for xlat (4) sql: ERROR: Error parsing user data from database result (4) sql: ERROR: Error retrieving reply pairs for group 15GB_Quota_2048KBurst rlm_sql (sql): Released connection (11) rlm_sql (sql): Need 2 more connections to reach 10 spares rlm_sql (sql): Opening additional connection (12), 1 of 31 pending slots used rlm_sql_mysql: Starting connect to MySQL server rlm_sql_mysql: Connected to database 'radius' on Localhost via UNIX socket, server version 5.5.47-0+deb8u1, protocol version 10 (4) [sql] = fail (4) } # authorize = fail (4) Using Post-Auth-Type Reject (4) # Executing group from file /etc/freeradius/sites-enabled/default (4) Post-Auth-Type REJECT { (4) sql: EXPAND .query (4) sql: --> .query (4) sql: Using query template 'query' rlm_sql (sql): Reserved connection (11) (4) sql: EXPAND %{User-Name} (4) sql: --> dseral (4) sql: SQL-User-Name set to 'dseral' (4) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, date) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') (4) sql: --> INSERT INTO radpostauth (username, pass, reply, date) VALUES ( 'dseral', '123', 'Access-Reject', '2016-03-03 20:48:36') (4) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, date) VALUES ( 'dseral', '123', 'Access-Reject', '2016-03-03 20:48:36') ...