sql expansion with query string stored in attribute?

Fajar A. Nugraha work at fajar.net
Wed Oct 19 08:39:57 CEST 2011


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



More information about the Freeradius-Users mailing list