[POLL] Escaping behaviour in SQL/LDAP/regexes

10 Mar 2025

      For FreeRADIUS v4 we have a new method of automatically escaping values used in various DSLs (Domain Specific Languages) like SQL, LDAP and regular expressions.

We'd appreciate it if FreeRADIUS users could look over the policy snippets below and indicate what you expect the SQL statement to be _as executed_ (not the result of evaluating the statement).

Assume the value of User-Name is: Bob'; DROP TABLE users;--

Possible answers:

a) SELECT * FROM my-table WHERE Service-Type = 'Framed-User' AND User-Name = 'Bob'; DROP TABLE users;--'
b) SELECT * FROM my-table WHERE Service-Type = \'Framed-User\' AND User-Name = 'Bob\'; DROP TABLE users;--'
c) SELECT * FROM my-table WHERE Service-Type = 'Framed-User' AND User-Name = 'Bob\'; DROP TABLE users;--'
d) Other.  Please write expected, expanded, SQL statement.

Do not return to previous statements and alter answers.  The purpose of this poll is to determine what user's expect behaviour to be, prior to seeing example code or reading documentation.

1) 

recv authorize {
	string sql_condition

	sql_condition = "Service-Type = 'Framed-User'"

	%sql.query("SELECT * FROM my-table WHERE %{sql_condition} AND User-Name = '%{User-Name}'")
}

2)

recv authorize {
	string sql_condition

	sql_condition = %sql.safe("Service-Type = 'Framed-User'")

	%sql.query("SELECT * FROM my-table WHERE %{sql_condition} AND User-Name = '%{User-Name}'")
}

3)

recv authorize {
	string sql_condition

	sql_condition = "Service-Type = 'Framed-User'"

	%sql.query("SELECT * FROM my-table WHERE %sql.safe(%{sql_condition}) AND User-Name = '%{User-Name}'")
}

4)

recv authorize {
	string sql_condition

	sql_condition = "Service-Type = 'Framed-User'"

	%sql.query("SELECT * FROM my-table WHERE %sql.safe(%{sql_condition}) AND User-Name = '%sql.safe(%{User-Name})'")
}

Other FreeRADIUS developers please refrain from responding until the 14th of March (when we'll tally results).

Many Thanks,
-Arran

Arran Cudbard-Bell <a.cudbardb@freeradius.org>
FreeRADIUS Development Team

FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2