sql expansion with query string stored in attribute?

[Fajar A. Nugraha]

On Wed, Oct 19, 2011 at 4:01 PM, Phil Mayers <p.mayers at 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