sql expansion with query string stored in attribute?
Hi, Is it possible to use sql expansion with query stored in a radius attribute (e.g. Tmp-String-0)? Currently it mime-encodes the characters equal (=) and single quote (') to "=3D" and "=27", so it's not usable. For example, I have something like this to store my local configuration variables: local-config { sql-now="select concat('Current time = ',now())" } ... and modify safe-characters on an sql/mysql/dialup.conf to include some extra characters (including equal sign and single quote) safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /'=" ... and put some test sql expansion (on sites-available/default, authorize section) update control { Tmp-String-0 := "${local-config.sql-now}" } update reply { Reply-Message := "Test1: ${local-config.sql-now}" Reply-Message += "Test2: %{sql:${local-config.sql-now}}" Reply-Message += "Test3: %{control:Tmp-String-0}" Reply-Message += "Test4: %{sql:%{control:Tmp-String-0}}" } ... and the resulting radtest output: $ radtest testuser testpass 127.0.0.1 0 testing123 Sending Access-Request of id 228 to 127.0.0.1 port 1812 User-Name = "testuser" User-Password = "testpass" NAS-IP-Address = 127.0.0.1 NAS-Port = 0 Message-Authenticator = 0x00000000000000000000000000000000 rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=228, length=166 Reply-Message = "Test1: select concat('Current time = ',now())" Reply-Message = "Test2: Current time = 2011-10-19 13:35:21" Reply-Message = "Test3: select concat('Current time = ',now())" Reply-Message = "Test4: " So the fourth test (sql expansion with query in an attribute) failed. Radius debug output shows this ++[control] returns updated sql_xlat expand: %{User-Name} -> testuser sql_set_user escaped user --> 'testuser' expand: select concat('Current time = ',now()) -> select concat('Current time = ',now()) rlm_sql (sql): Reserving sql socket id: 4 sql_xlat finished rlm_sql (sql): Released sql socket id: 4 expand: Test2: %{sql:select concat('Current time = ',now())} -> Test2: Current time = 2011-10-19 13:35:21 expand: Test3: %{control:Tmp-String-0} -> Test3: select concat('Current time = ',now()) sql_xlat expand: %{User-Name} -> testuser sql_set_user escaped user --> 'testuser' expand: %{control:Tmp-String-0} -> select concat=28'Current time = '=2Cnow=28=29=29 rlm_sql (sql): Reserving sql socket id: 3 rlm_sql_mysql: MYSQL check_error: 1064 received rlm_sql (sql): database query error, select concat=28'Current time = '=2Cnow=28=29=29: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=2Cnow=28=29=29' at line 1 rlm_sql (sql): Released sql socket id: 3 expand: Test4: %{sql:%{control:Tmp-String-0}} -> Test4: ++[reply] returns updated Is there a way to change which characters remain unencoded? Thanks. -- Fajar
Fajar A. Nugraha wrote:
Is it possible to use sql expansion with query stored in a radius attribute (e.g. Tmp-String-0)?
Not currently.
For example, I have something like this to store my local configuration variables:
local-config { sql-now="select concat('Current time = ',now())" }
You can use that as a *load-time* expansion, via ${...}. But it means you can't change it at run-time. That's mainly used to abstract configuration on different machines.
Is there a way to change which characters remain unencoded? Thanks.
That's what "safe_characters" is for. Alan DeKok.
On Wed, Oct 19, 2011 at 1:45 PM, Alan DeKok <aland@deployingradius.com> wrote:
Fajar A. Nugraha wrote:
Is it possible to use sql expansion with query stored in a radius attribute (e.g. Tmp-String-0)?
Not currently.
I was afraid of that :)
For example, I have something like this to store my local configuration variables:
local-config { sql-now="select concat('Current time = ',now())" }
You can use that as a *load-time* expansion, via ${...}. But it means you can't change it at run-time.
Yes, which is what Test2 does, and it works correctly.
That's mainly used to abstract configuration on different machines.
Is there a way to change which characters remain unencoded? Thanks.
That's what "safe_characters" is for.
In dialup.conf? Yes, but it doesn't seem to work when the query is stored in an attribute. Looks like sql_xlat is responsible for this? Thanks -- Fajar
On Wed, Oct 19, 2011 at 1:50 PM, Fajar A. Nugraha <list@fajar.net> wrote:
On Wed, Oct 19, 2011 at 1:45 PM, Alan DeKok <aland@deployingradius.com> wrote:
Is there a way to change which characters remain unencoded? Thanks.
That's what "safe_characters" is for.
In dialup.conf? Yes, but it doesn't seem to work when the query is stored in an attribute. Looks like sql_xlat is responsible for this?
I think I found the source of my problem. Two, actually. First, my safe_character list in incomplete. I needed to add "()," Second, I have multiple sql instances (not shown in the debug posted earlier) including different dialup.conf, and only one of them (sql) has the complete safe_characters needed. Turns out FR uses the safe_characters from the LAST instantiated sql instance. Manually putting the sql instances in instantiate section of radiusd.conf, with the one containing safe_characters I want last, works as expected. Thanks, Fajar
Dear all, Please tell me how to configure my freeradius server with wifi accesspoint. its very urgent. I am getting many problem. Thanks Warm Regards Harish Mandowara -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
On Wed, Oct 19, 2011 at 3:16 PM, Harish Mandowara <harishm@cdac.in> wrote:
Dear all,
Please tell me how to configure my freeradius server with wifi accesspoint.
There is no single source of information that I know of that put all that you need together in a complete document. Feel free to contribute your own documentation if you get this working. In the mean time, deployingradius.com has enough information to get started with simple setup, but only for radius part. You still have to look another place (e.g. documentation from vendor) to configure your wireless AP.
its very urgent. I am getting many problem.
I'd recommend you get an expert. What you're saying is something like "Help, I'm sick and I don't know why. Please tell me how to be a doctor in one day so I can cure myself!". -- Fajar
Dear all, Please tell me how to configure my freeradius server with wifi accesspoint. its very urgent. I am getting many problem. Thanks Warm Regards Harish Mandowara -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
Please tell me how to configure my freeradius server with wifi accesspoint.
its very urgent. I am getting many problem.
No Arran Cudbard-Bell a.cudbardb@freeradius.org Betelwiki, Betelwiki, Betelwiki.... http://wiki.freeradius.org/ !
On 10/19/2011 08:41 AM, Fajar A. Nugraha wrote:
has the complete safe_characters needed. Turns out FR uses the safe_characters from the LAST instantiated sql instance. Manually putting the sql instances in instantiate section of radiusd.conf, with the one containing safe_characters I want last, works as expected.
Yeah, that's a tedious bug that's hard to solve. The "sql_xlat" function calls "radius_xlat" and supplies "sql_escape_func" as the final argument; unfortunately the "escape functions" you can pass to "radius_xlat" just get the input string and output buffer - they don't get any per-module config, so making the safe_characters per-module is tricky. I did have some patches making the escape function (but not the safe_characters) selectable and offering "\xAA" as well as "raw" i.e. no escaping. The latter was intended for use with Postgres' dollar-quoted strings e.g. select * from foo where bar=$tag$this is the string$tag$ Are you using postgres? If so, you could try to abuse this feature by making EVERY character safe, then perform the escaping yourself by doing this: update request { SQL-Query := "select * from foo where bar=$tag$%{User-Name}$tag$" SQL-Result := "%{sql:%{SQL-Query}}" } It's not the most secure option; someone could contrive to get the string "$tag$; drop table foo" into a radius field, but if you can be sure this won't happen (e.g. sanitise it) it might work.
On Wed, Oct 19, 2011 at 4:01 PM, Phil Mayers <p.mayers@imperial.ac.uk> wrote:
Are you using postgres?
Nope. MySQL.
If so, you could try to abuse this feature by making EVERY character safe, then perform the escaping yourself by doing this:
update request { SQL-Query := "select * from foo where bar=$tag$%{User-Name}$tag$" SQL-Result := "%{sql:%{SQL-Query}}" }
"SQL-Query" and "SQL-Result" is just an example, right? Unless it's specifically added to a dictionary.
It's not the most secure option; someone could contrive to get the string "$tag$; drop table foo" into a radius field, but if you can be sure this won't happen (e.g. sanitise it) it might work.
The most dangerous character would probably be ";". Right now I'm adding "'=(),|". The first five because it's often used in queries. The last one ("|") is because I need a "marker" character, so that I can abuse mysql's CONCAT() and split the result later using unlang's regex. The "put queries in attribute" part is necessary to be able to create a generic pseudo-redundant sql expansion. I'm currently testing it for dynamic-clients. The modification uses less sql query (one, as opposed to five), and can use another sql server if the first one is dead or returns no result (which is why I said pseudo-redundant). If anyone's interested, the modification is something like this: local-config.conf: ================================== local-config { ... dynamic-clients { sql-nas="SELECT CONCAT('|', shortname, '|', secret , '|', type , '|', IF(ISNULL(server),'',server), '|') FROM nas WHERE nasname = '%{Packet-Src-IP-Address}'" } ... } policy.conf: ================================== policy { ... # SQL expansion: query from Tmp-String-0, result stored on Tmp-String-1 expand_sql1 { if (control:Tmp-String-0) { update control { Tmp-String-1 := "%{sql-expansion-1: %{control:Tmp-String-0}}" } } } expand_sql2 { if (control:Tmp-String-0) { update control { Tmp-String-1 := "%{sql-expansion-2: %{control:Tmp-String-0}}" } } } expand_sql_redundant { expand_sql1 if (! "%{control:Tmp-String-1}") { expand_sql2 } } ... } sites-available/dynamic-clients: ================================== server dynamic_client_server { ... authorize { update control { Tmp-String-0 := "${local-config.dynamic-clients.sql-nas}" } expand_sql_redundant if (control:Tmp-String-1 =~ /\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|/) { update control { FreeRADIUS-Client-IP-Address = "%{Packet-Src-IP-Address}" FreeRADIUS-Client-Shortname = "%{1}" FreeRADIUS-Client-Secret = "%{2}" FreeRADIUS-Client-NAS-Type = "%{3}" FreeRADIUS-Client-Virtual-Server = "%{4}" } } ok } ... } -- Fajar
participants (6)
-
Alan DeKok -
Arran Cudbard-Bell -
Fajar A. Nugraha -
Fajar A. Nugraha -
Harish Mandowara -
Phil Mayers