OK i found something, it looks if i set the safe_characters in the main sql definition it works. ``` sql { # The sub-module to use to execute queries. This should match # the database you're attempting to connect to. # # * rlm_sql_mysql # * rlm_sql_mssql # * rlm_sql_oracle # * rlm_sql_postgresql # * rlm_sql_sqlite # * rlm_sql_null (log queries to disk) # driver = "rlm_sql_mysql" safe_characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /(),'" ........ ``` Le 20-06-17 à 09 h 45, Fabrice Durand a écrit :
Hello Alan,
thanks for the reply, so i tested what you suggested and i am not still able to make it work.
Le 20-06-17 à 09 h 20, Alan DeKok a écrit :
On Jun 17, 2020, at 8:55 AM, Fabrice Durand <fdurand@inverse.ca> wrote:
i am trying to set the safe_characters in a sql configuration and it looks that the safe characters are not working anymore (at least the extra i add). I'd suggest avoiding "safe_characters".
From 3.0.18:
* Some SQL modules can now use "auto_escape" to escape unsafe strings. See mods-config/sql/main/mysql/queries.conf
Just tried with auto_escape:
```
(1) Wed Jun 17 09:34:39 2020: Debug: SQL-User-Name set to '64-76-ba-89-71-4c' (1) Wed Jun 17 09:34:39 2020: Debug: Executing query: INSERT into radreply (username, attribute, value) values (\'64:76:ba:89:71:4c\',\'Tunnel-Medium-Type:0\',\'IEEE-802\'), (\'64:76:ba:89:71:4c\',\'Tunnel-Private-Group-Id:0\',\'135\'), (\'64:76:ba:89:71:4c\',\'Tunnel-Type:0\',\'VLAN\') (1) Wed Jun 17 09:34:39 2020: ERROR: rlm_sql_mysql: ERROR 1064 (You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '\'64:76:ba:89:71:4c\',\'Tunnel-Medium-Type:0\',\'IEEE-802\'), (\'64:76:ba:89:71:' at line 1): 42000 (1) Wed Jun 17 09:34:39 2020: ERROR: SQL query failed: server error (1) Wed Jun 17 09:34:39 2020: Debug: EXPAND %{sql_degraded:%{control:PacketFence-reply-insert}}
```
I have the following configuration:
```
sql sql_degraded { ... safe_characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /(),'" }
```
I added (),' as extra char. That should still work.
Followinf the trace from freeradius 3.0.21 (doesn't work) and from freeradius-3.0.13 (works) for exactly the same radius request and exactly the same configuration: The key point for the sql_degraded module is:
authorize_reply_query = "SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{Calling-Station-Id}' ORDER BY id" group_membership_query = "" safe_characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /" auto_escape = no
In the debug the sql_degraded contain that:
```
sql sql_degraded { driver = "rlm_sql_mysql" server = "127.0.0.1" port = 3306 login = "pf" password = <<< secret >>> radius_db = "pf" read_groups = yes read_profiles = yes read_clients = no delete_stale_sessions = yes sql_user_name = "%{User-Name}" default_user_profile = "" client_query = "SELECT id,nasname,shortname,type,secret FROM nas" authorize_reply_query = "SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{Calling-Station-Id}' ORDER BY id" group_membership_query = "" safe_characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /(),'"
```
You probably took the one from "sql_reject".
So it's not picking up the "safe_characters" string you set.
Probably because of this:
... $INCLUDE ${modconfdir}/${.:name}/main/mysql/reject.conf safe_characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /(),'" ...
If the "reject.conf" file *also* has "safe_characters" set, that one will be used instead of the extra one you added.
You can't "over-ride" configuration items by adding a second one.
I removed $INCLUDE ${modconfdir}/${.:name}/main/mysql/reject.conf and the issue is still here. (attached the new trace)
Regards
Fabrice
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-- Fabrice Durand fdurand@inverse.ca :: +1.514.447.4918 (x135) :: www.inverse.ca Inverse inc. :: Leaders behind SOGo (http://www.sogo.nu) and PacketFence (http://packetfence.org)