Search code examples
mysqlfreeradius

FreeRADIUS 3 stores # in groupname as =23 for %{SQL-Group}

i have a user bipin linked to a usergroup called #dl#-daily-plan which FR is able to read from mysql DB but it stores it as =23dl=23-daily-plan in %{SQL-Group}. Is there any specific reasoning for this as im on the vanilla config of FR and it seems to happen in this

rlm_sql (sql): Reserved connection (7)
(1) sql: EXPAND SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id
(1) sql:    --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'Bipin' ORDER BY id
(1) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'Bipin' ORDER BY id
(1) sql: User found in radcheck table
(1) sql: Conditional check items matched, merging assignment check items
(1) sql:   Cleartext-Password := "bipin"
(1) sql:   Expiration := "Feb 10 2020 00:00:00 +04"
(1) sql: EXPAND SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id
(1) sql:    --> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'Bipin' ORDER BY id
(1) sql: Executing select query: SELECT id, username, attribute, value, op FROM radreply WHERE username = 'Bipin' ORDER BY id
(1) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority
(1) sql:    --> SELECT groupname FROM radusergroup WHERE username = 'Bipin' ORDER BY priority
(1) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'Bipin' ORDER BY priority
(1) sql: User found in the group table
(1) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{SQL-Group}' ORDER BY id
(1) sql:    --> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '=23dl=23-daily-plan' ORDER BY id
(1) sql: Executing select query: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '=23dl=23-daily-plan' ORDER BY id
(1) sql: Group "#dl#-daily-plan": Conditional check items matched
(1) sql: Group "#dl#-daily-plan": Merging assignment check items
(1) sql: EXPAND SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{SQL-Group}' ORDER BY id
(1) sql:    --> SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '=23dl=23-daily-plan' ORDER BY id
(1) sql: Executing select query: SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '=23dl=23-daily-plan' ORDER BY id
(1) sql: Group "#dl#-daily-plan": Merging reply items
rlm_sql (sql): Released connection (7)
Solution

  • This is not a bug, but the intended behavior. To protect from SQL injection, there is a list of safe characters (safe_characters) that are passed as is, all other characters are converted into this hex-escape-notation before used in a query.

    To resolve this, there are a couple options:

    1. Rename the group in the database tables to use this escaped form as well.
    2. Add # to the list of safe characters (at your own risk)
    3. Insert a call to a stored procedure in the queries to convert =HH back to the character.
    4. Wait until 3.0.18 is released and use the option auto_escape