safe_characters issue

[Fabrice Durand]

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 at 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 at inverse.ca ::  +1.514.447.4918 (x135) ::  www.inverse.ca
Inverse inc. :: Leaders behind SOGo (http://www.sogo.nu) and PacketFence (http://packetfence.org)