Navigation

If you happen to manage a hotspot that uses Freeradius with the sql module for authentication, you might want to pay attention. The default queries used by Freeradius sql module are case-insensitive. So if user “kwame” is successfully authenticated, another user “Kwame” can also successfully autheticate. And so can “KWAME”, “kwamE”, “KwaMe” and so on for that matter. I guess you can see where I’m going with this: if any of your users should catch on to this… And to think, this hadn’t crossed my mind till a friend who runs a wireless isp pointed out some strange activity he had noticed in his logs.

You shouldn’t forget to make a small change to the /etc/freeradius/sql/mysql/dialup.conf(or /etc/freeradius/sql.conf) file. Somewhere around line 82 lies the following:

#######################################################################
        # Use these for case sensitive usernames.
#        authorize_check_query = "SELECT id, username, attribute, value, op \
#         FROM ${authcheck_table} \
#         WHERE username = BINARY '%{SQL-User-Name}' \
#         ORDER BY id"
#        authorize_reply_query = "SELECT id, username, attribute, value, op \
#         FROM ${authreply_table} \
#         WHERE username = BINARY '%{SQL-User-Name}' \
#         ORDER BY id"

        # The default queries are case insensitive. (for compatibility with
        # older versions of FreeRADIUS)
       authorize_check_query = "SELECT id, username, attribute, value, op \
          FROM ${authcheck_table} \
          WHERE username = '%{SQL-User-Name}' \
          ORDER BY id"
       authorize_reply_query = "SELECT id, username, attribute, value, op \
          FROM ${authreply_table} \
          WHERE username = '%{SQL-User-Name}' \
          ORDER BY id"

This should be:

#######################################################################
        # Use these for case sensitive usernames.
        authorize_check_query = "SELECT id, username, attribute, value, op \
         FROM ${authcheck_table} \
         WHERE username = BINARY '%{SQL-User-Name}' \
         ORDER BY id"
        authorize_reply_query = "SELECT id, username, attribute, value, op \
         FROM ${authreply_table} \
         WHERE username = BINARY '%{SQL-User-Name}' \
         ORDER BY id"

        # The default queries are case insensitive. (for compatibility with
        # older versions of FreeRADIUS)
#       authorize_check_query = "SELECT id, username, attribute, value, op \
#          FROM ${authcheck_table} \
#          WHERE username = '%{SQL-User-Name}' \
#          ORDER BY id"
#       authorize_reply_query = "SELECT id, username, attribute, value, op \
#          FROM ${authreply_table} \
#          WHERE username = '%{SQL-User-Name}' \
#          ORDER BY id"

And if you apply your attributes per group instead of per user, like I do, then this:

        # Use these for case sensitive usernames.
#        group_membership_query = "SELECT groupname \
#        FROM ${usergroup_table} \
#         WHERE username = BINARY '%{SQL-User-Name}' \
#        ORDER BY priority"

       group_membership_query = "SELECT groupname \
          FROM ${usergroup_table} \
          WHERE username = '%{SQL-User-Name}' \
          ORDER BY priority"

should become:

        # Use these for case sensitive usernames.
        group_membership_query = "SELECT groupname \
        FROM ${usergroup_table} \
         WHERE username = BINARY '%{SQL-User-Name}' \
        ORDER BY priority"

#       group_membership_query = "SELECT groupname \
#          FROM ${usergroup_table} \
#          WHERE username = '%{SQL-User-Name}' \
#          ORDER BY priority"

Reload the freeradius server and your usernames should be case sensitive. Now, go and buy yourself a beer in celebration of your valiant victory against the dark forces of computer insecurity.