Run time variable in sql
Hey list, after problems in an older version I changed to the newest one (3.0.16). Now the attributes are processed but not delivered correctly. I want to process %{Actual-Data-Rate-Downstream} and set it as attribute Huawei-Output-Average-Rate. +-----------+----------------------------+----+------------------------------------------+ | groupname | attribute | op | value | +-----------+----------------------------+----+------------------------------------------+ | 50m | Huawei-Input-Average-Rate | = | `%{request:Actual-Data-Rate-Upstream}00` | | 50m | Huawei-Output-Average-Rate | = | `%{request:Actual-Data-Rate-Downstream}00` | +-----------+----------------------------+----+------------------------------------------+ Debug looks OK: Expand of attribute: (96) sql: EXPAND `%{request:Actual-Data-Rate-Upstream}00` (96) sql: --> `4199900` But it is allready 0: Result to the client/bras: (96) Huawei-Input-Average-Rate = 0 it sure is just a simpler one, but I can not find it, like a quote error or anything Best wishes Rob Sample Debug: (96) Received Access-Request Id 22 from <brasip>:1812 to radiusip:1812 length 362 (96) User-Name = "user123" (96) User-Password = "userpasschap" (96) NAS-Port = 3166215 (96) NAS-IP-Address = <brasip> (96) Service-Type = Framed-User (96) Framed-Protocol = PPP (96) Calling-Station-Id = "<cpemac>" (96) NAS-Identifier = "bras3" (96) NAS-Port-Type = Ethernet (96) NAS-Port-Id = "10.126.41.171/0.0.0.0 eth 2/7" (96) Acct-Session-Id = "bras30030342352307b37b9d003a40" (96) Connect-Info = "1000000000" (96) Huawei-Startup-Stamp = 1516351868 (96) Huawei-IPHost-Addr = "255.255.255.255 <cpemac>" (96) Huawei-Connect-ID = 14912 (96) Huawei-Version = "Huawei NE20E" (96) Huawei-Product-ID = "NE20E" (96) Huawei-Domain-Name = "example.com" (96) Huawei-User-Mac = "<cpemac>" (96) ADSL-Agent-Circuit-Id = 0x31302e3132362e34safasghasdasfasd2e302e302065746820322f37 (96) ADSL-Agent-Remote-Id = 0x4445552asdasfasdasd441472e414d525436 (96) Actual-Data-Rate-Upstream = 41999 (96) Actual-Data-Rate-Downstream = 109066 (96) Access-Loop-Encapsulation = 0x010000 (96) # Executing section authorize from file /usr/local/freeradius3/etc/raddb/sites-enabled/default (96) authorize { (96) policy filter_username { (96) if (&User-Name) { (96) if (&User-Name) -> TRUE (96) if (&User-Name) { (96) if (&User-Name =~ / /) { (96) if (&User-Name =~ / /) -> FALSE (96) if (&User-Name =~ /@[^@]*@/ ) { (96) if (&User-Name =~ /@[^@]*@/ ) -> FALSE (96) if (&User-Name =~ /\.\./ ) { (96) if (&User-Name =~ /\.\./ ) -> FALSE (96) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) { (96) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) -> FALSE (96) if (&User-Name =~ /\.$/) { (96) if (&User-Name =~ /\.$/) -> FALSE (96) if (&User-Name =~ /@\./) { (96) if (&User-Name =~ /@\./) -> FALSE (96) } # if (&User-Name) = notfound (96) } # policy filter_username = notfound (96) [preprocess] = ok (96) [chap] = noop (96) suffix: Checking for suffix after "@" (96) suffix: Looking up realm "example.com" for User-Name = "user123" (96) suffix: No such realm "example.com" (96) [suffix] = noop (96) sql: EXPAND %{User-Name} (96) sql: --> user123 (96) sql: SQL-User-Name set to 'user123' rlm_sql (sql): Reserved connection (113) (96) sql: EXPAND SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id (96) sql: --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'user123' ORDER BY id (96) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'user123' ORDER BY id (96) sql: User found in radcheck table (96) sql: Conditional check items matched, merging assignment check items (96) sql: Cleartext-Password := "userpasschap" (96) sql: EXPAND SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id (96) sql: --> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'user123' ORDER BY id (96) sql: Executing select query: SELECT id, username, attribute, value, op FROM radreply WHERE username = 'user123' ORDER BY id (96) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority (96) sql: --> SELECT groupname FROM radusergroup WHERE username = 'user123' ORDER BY priority (96) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'user123' ORDER BY priority (96) sql: User found in the group table (96) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{SQL-Group}' ORDER BY id (96) sql: --> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'dyn' ORDER BY id (96) sql: Executing select query: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'dyn' ORDER BY id (96) sql: Group "dyn": Conditional check items matched (96) sql: Group "dyn": Merging assignment check items (96) sql: Pool-Name := "DIALPOOL-DYN-1" (96) sql: EXPAND SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{SQL-Group}' ORDER BY id (96) sql: --> SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = 'dyn' ORDER BY id (96) sql: Executing select query: SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = 'dyn' ORDER BY id (96) sql: Group "dyn": Merging reply items (96) sql: MS-Primary-DNS-Server = <primarydns> (96) sql: MS-Secondary-DNS-Server = <secondarydns> (96) sql: Session-Timeout := 86400 (96) sql: Fall-Through = Yes (96) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{SQL-Group}' ORDER BY id (96) sql: --> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '50m' ORDER BY id (96) sql: Executing select query: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '50m' ORDER BY id (96) sql: Group "50m": Conditional check items matched (96) sql: Group "50m": Merging assignment check items (96) sql: EXPAND SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{SQL-Group}' ORDER BY id (96) sql: --> SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '50m' ORDER BY id (96) sql: Executing select query: SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '50m' ORDER BY id (96) sql: Group "50m": Merging reply items (96) sql: Huawei-Input-Average-Rate = "`%{request:Actual-Data-Rate-Upstream}00`" (96) sql: EXPAND `%{request:Actual-Data-Rate-Upstream}00` (96) sql: --> `4199900` rlm_sql (sql): Released connection (113) Need 1 more connections to reach min connections (3) rlm_sql (sql): Opening additional connection (114), 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 10.1.26-MariaDB-0+deb9u1, protocol version 10 (96) [sql] = ok (96) [expiration] = noop (96) [logintime] = noop (96) [pap] = updated (96) } # authorize = updated (96) Found Auth-Type = PAP (96) # Executing group from file /usr/local/freeradius3/etc/raddb/sites-enabled/default (96) Auth-Type PAP { (96) pap: Login attempt with password (96) pap: Comparing with "known good" Cleartext-Password (96) pap: User authenticated successfully (96) [pap] = ok (96) } # Auth-Type PAP = ok (96) # Executing section post-auth from file /usr/local/freeradius3/etc/raddb/sites-enabled/default (96) post-auth { (96) update { (96) No attributes updated (96) } # update = noop rlm_sql (sql): Reserved connection (112) (96) sql: EXPAND .query (96) sql: --> .query (96) sql: Using query template 'query' rlm_sql (sql): Reserved connection (113) (96) sql: EXPAND %{User-Name} (96) sql: --> user123 (96) sql: SQL-User-Name set to 'user123' (96) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') (96) sql: --> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'user123', 'userpass', 'Access-Accept', '2018-03-23 15:25:35') (96) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'user123', 'userpass', 'Access-Accept', '2018-03-23 15:25:35') (96) sql: SQL query returned: success (96) sql: 1 record(s) updated rlm_sql (sql): Released connection (113) (96) [sql] = ok (96) [exec] = noop (96) policy remove_reply_message_if_eap { (96) if (&reply:EAP-Message && &reply:Reply-Message) { (96) if (&reply:EAP-Message && &reply:Reply-Message) -> FALSE (96) else { (96) [noop] = noop (96) } # else = noop (96) } # policy remove_reply_message_if_eap = noop (96) } # post-auth = ok (96) Sent Access-Accept Id 22 from <radiusip>:1812 to <brasip>:1812 length 0 (96) MS-Primary-DNS-Server = <primarydns> (96) MS-Secondary-DNS-Server = <secondarydns> (96) Session-Timeout = 86400 (96) Huawei-Input-Average-Rate = 0 (96) Framed-IP-Address = <usercpeip> (96) Finished request Waking up in 4.9 seconds.
On Mar 26, 2018, at 4:32 AM, rob-ml@mailbox.org wrote:
after problems in an older version I changed to the newest one (3.0.16). Now the attributes are processed but not delivered correctly.
I want to process %{Actual-Data-Rate-Downstream} and set it as attribute Huawei-Output-Average-Rate.
OK...
+-----------+----------------------------+----+------------------------------------------+ | groupname | attribute | op | value | +-----------+----------------------------+----+------------------------------------------+ | 50m | Huawei-Input-Average-Rate | = | `%{request:Actual-Data-Rate-Upstream}00` | | 50m | Huawei-Output-Average-Rate | = | `%{request:Actual-Data-Rate-Downstream}00` | +-----------+----------------------------+----+------------------------------------------+
Which sets Huawei-Input-Average-Rate, but only if it isn't already set. If you want to force it to that value, use ":=". See the rlm_sql documentation for more details on the difference between "=" and ":=". Alan DeKok.
Hi, now tested, no matter which operator, always with the same result. Receive: (3) Actual-Data-Rate-Upstream = 41999 (3) Actual-Data-Rate-Downstream = 109066 Processing in radiusd -X debug: (3) sql: EXPAND `%{request:Actual-Data-Rate-Upstream}00` (3) sql: --> `4199900` (3) sql: EXPAND `%{request:Actual-Data-Rate-Downstream}00` (3) sql: --> `10906600` Result/response: (3) Huawei-Input-Average-Rate = 0 (3) Huawei-Output-Average-Rate = 0 Best wishes Rob Alan DeKok <aland@deployingradius.com> hat am 26. März 2018 um 13:30 geschrieben: On Mar 26, 2018, at 4:32 AM, rob-ml@mailbox.org wrote: after problems in an older version I changed to the newest one (3.0.16). Now the attributes are processed but not delivered correctly. I want to process %{Actual…
Alan DeKok <aland@deployingradius.com> hat am 26. März 2018 um 13:30 geschrieben:
On Mar 26, 2018, at 4:32 AM, rob-ml@mailbox.org wrote:
after problems in an older version I changed to the newest one (3.0.16). Now the attributes are processed but not delivered correctly.
I want to process %{Actual-Data-Rate-Downstream} and set it as attribute Huawei-Output-Average-Rate.
OK...
+-----------+----------------------------+----+------------------------------------------+ | groupname | attribute | op | value | +-----------+----------------------------+----+------------------------------------------+ | 50m | Huawei-Input-Average-Rate | = | `%{request:Actual-Data-Rate-Upstream}00` | | 50m | Huawei-Output-Average-Rate | = | `%{request:Actual-Data-Rate-Downstream}00` | +-----------+----------------------------+----+------------------------------------------+
Which sets Huawei-Input-Average-Rate, but only if it isn't already set. If you want to force it to that value, use ":=".
See the rlm_sql documentation for more details on the difference between "=" and ":=".
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Mar 26, 2018, at 8:05 AM, rob-ml@mailbox.org wrote:
now tested, no matter which operator, always with the same result.
Receive: (3) Actual-Data-Rate-Upstream = 41999 (3) Actual-Data-Rate-Downstream = 109066
Processing in radiusd -X debug: (3) sql: EXPAND `%{request:Actual-Data-Rate-Upstream}00` (3) sql: --> `4199900` (3) sql: EXPAND `%{request:Actual-Data-Rate-Downstream}00` (3) sql: --> `10906600`
Ah... a careful reading of the debug output helps here. A back-tick quoted string calls a *program*. e.g. `/bin/echo foo` You probably want to use normal double quotes. Alan DeKok.
Hey, hmm... the one quotations-info i found in manual/wiki (run_time_variables site) with: .. you must put the string into an back-quoted string .. If i put the radgroupreply table value in a double quoted string like: "%{request:Actual-Data-Rate-Upstream}00" i receive only a red error line: (2) sql: ERROR: Error parsing value: Unknown or invalid value "%{request:Actual-Data-Rate-Upstream}00" for attribute Huawei-Input-Average-Rate (2) sql: ERROR: Error parsing user data from database result (2) sql: ERROR: Error retrieving reply pairs for group 50m A back-tick quoted value looks right for me, because it is processed. But the result string is back-tick quoted too, could it be the problem? Best wishes, Rob
Alan DeKok <aland@deployingradius.com> hat am 26. März 2018 um 14:58 geschrieben:
On Mar 26, 2018, at 8:05 AM, rob-ml@mailbox.org wrote:
now tested, no matter which operator, always with the same result.
Receive: (3) Actual-Data-Rate-Upstream = 41999 (3) Actual-Data-Rate-Downstream = 109066
Processing in radiusd -X debug: (3) sql: EXPAND `%{request:Actual-Data-Rate-Upstream}00` (3) sql: --> `4199900` (3) sql: EXPAND `%{request:Actual-Data-Rate-Downstream}00` (3) sql: --> `10906600`
Ah... a careful reading of the debug output helps here.
A back-tick quoted string calls a *program*. e.g. `/bin/echo foo`
You probably want to use normal double quotes.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Mar 26, 2018, at 9:22 AM, rob-ml@mailbox.org wrote:
Hey, hmm... the one quotations-info i found in manual/wiki (run_time_variables site) with: .. you must put the string into an back-quoted string ..
If i put the radgroupreply table value in a double quoted string like: "%{request:Actual-Data-Rate-Upstream}00"
i receive only a red error line:
(2) sql: ERROR: Error parsing value: Unknown or invalid value "%{request:Actual-Data-Rate-Upstream}00" for attribute Huawei-Input-Average-Rate (2) sql: ERROR: Error parsing user data from database result (2) sql: ERROR: Error retrieving reply pairs for group 50m
A back-tick quoted value looks right for me, because it is processed.
I guess.
But the result string is back-tick quoted too, could it be the problem?
No, that's just an artifact of the debugging. Alan DeKok.
On 26.03.2018 17:27, Alan DeKok wrote:
But the result string is back-tick quoted too, could it be the problem?
No, that's just an artifact of the debugging.
That's artifact of source code (rlm_sql module - sql.c, string 131 in 3.0.16 version): ... case T_BACK_QUOTED_STRING: do_xlat = 1; /* FALL-THROUGH */ /* * Keep the original string. */ default: value = row[3]; break; } ... There really "Keep the original" - back-quoted string. Best regards, Alex Dotsenko.
hey, i don't think so, will turn out during testing. best wishes rob
Alan Buxey <alan.buxey@gmail.com> hat am 26. März 2018 um 17:21 geschrieben:
do you need quotes in the SQL row at all?
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Hi, i am still looking for a solution, would that be an approach?
That's artifact of source code (rlm_sql module - sql.c, string 131 in 3.0.16 version):
...
case T_BACK_QUOTED_STRING: do_xlat = 1;
/* FALL-THROUGH */
/* * Keep the original string. */ default: value = row[3]; break; } ...
There really "Keep the original" - back-quoted string.
Best regards Rob
rob-ml@mailbox.org hat am 26. März 2018 um 17:50 geschrieben:
hey, i don't think so, will turn out during testing.
best wishes rob
Alan Buxey <alan.buxey@gmail.com> hat am 26. März 2018 um 17:21 geschrieben:
do you need quotes in the SQL row at all?
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Hi, Rob As a solution, you can patch the module rlm_sql: diff -ru freeradius-server-3.0.12.orig/src/modules/rlm_sql/sql.c freeradius-server-3.0.12/src/modules/rlm_sql/sql.c --- freeradius-server-3.0.12.orig/src/modules/rlm_sql/sql.c 2016-09-29 18:19:48.000000000 +0300 +++ freeradius-server-3.0.12/src/modules/rlm_sql/sql.c 2016-12-20 11:44:37.001299860 +0300 @@ -168,16 +168,13 @@ break; /* - * Mark the pair to be allocated later. + * Take the unquoted string and mark the pair to be allocated later. */ case T_BACK_QUOTED_STRING: do_xlat = 1; - /* FALL-THROUGH */ - - /* - * Keep the original string. - */ + value = buf; + break; default: value = row[3]; break; Best regards, Alex On 27.03.2018 12:13, rob-ml@mailbox.org wrote:
Hi, i am still looking for a solution, would that be an approach?
That's artifact of source code (rlm_sql module - sql.c, string 131 in 3.0.16 version):
...
case T_BACK_QUOTED_STRING: do_xlat = 1;
/* FALL-THROUGH */
/* * Keep the original string. */ default: value = row[3]; break; } ...
There really "Keep the original" - back-quoted string.
Best regards Rob
rob-ml@mailbox.org hat am 26. März 2018 um 17:50 geschrieben:
hey, i don't think so, will turn out during testing.
best wishes rob
Alan Buxey <alan.buxey@gmail.com> hat am 26. März 2018 um 17:21 geschrieben:
do you need quotes in the SQL row at all?
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Hi Alexey, great, the workaround works! Thank you so much! Like a bug, like a fix :) (6) Sent Access-Accept Id 67 from radius to bras length 0 .. (6) Huawei-Input-Average-Rate = 41999000 (6) Huawei-Output-Average-Rate = 109066000 .. Best regards, Rob
Alexey Dotsenko <lex@rwx.su> hat am 27. März 2018 um 13:27 geschrieben:
Hi, Rob
As a solution, you can patch the module rlm_sql:
diff -ru freeradius-server-3.0.12.orig/src/modules/rlm_sql/sql.c freeradius-server-3.0.12/src/modules/rlm_sql/sql.c --- freeradius-server-3.0.12.orig/src/modules/rlm_sql/sql.c 2016-09-29 18:19:48.000000000 +0300 +++ freeradius-server-3.0.12/src/modules/rlm_sql/sql.c 2016-12-20 11:44:37.001299860 +0300 @@ -168,16 +168,13 @@ break;
/* - * Mark the pair to be allocated later. + * Take the unquoted string and mark the pair to be allocated later. */ case T_BACK_QUOTED_STRING: do_xlat = 1;
- /* FALL-THROUGH */ - - /* - * Keep the original string. - */ + value = buf; + break; default: value = row[3]; break;
Best regards, Alex
Hi, i am still looking for a solution, would that be an approach?
That's artifact of source code (rlm_sql module - sql.c, string 131 in 3.0.16 version):
...
case T_BACK_QUOTED_STRING: do_xlat = 1;
/* FALL-THROUGH */
/* * Keep the original string. */ default: value = row[3]; break; } ...
There really "Keep the original" - back-quoted string.
Best regards Rob
rob-ml@mailbox.org hat am 26. März 2018 um 17:50 geschrieben:
hey, i don't think so, will turn out during testing.
best wishes rob
Alan Buxey <alan.buxey@gmail.com> hat am 26. März 2018 um 17:21 geschrieben:
do you need quotes in the SQL row at all?
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On 27.03.2018 12:13, rob-ml@mailbox.org wrote: - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Mar 27, 2018, at 8:34 AM, rob-ml@mailbox.org wrote:
great, the workaround works! Thank you so much! Like a bug, like a fix :)
(6) Sent Access-Accept Id 67 from radius to bras length 0 .. (6) Huawei-Input-Average-Rate = 41999000 (6) Huawei-Output-Average-Rate = 109066000
I'll push a fix. Alan DeKok.
participants (4)
-
Alan Buxey -
Alan DeKok -
Alexey Dotsenko -
rob-ml＠mailbox.org