Using encode/decode with SASL and Mysql

From: Gary Mills (mills at cc dot UManitoba dot CA)
Date: Wed Mar 17 2004 - 14:11:39 EST

  • Next message: Kenny Olano: "Compile sasl2 with mysql support"

    We've decided to keep our SASL clear-text passwords in a Mysql
    database, but to use the Mysql encode() and decode() functions to
    store and retrieve them. The main benefit of this decision is that
    clear-text passwords are not visible to anyone accidentially viewing
    the table. They also do not appear in the dumps and on the backup

    This is still not very secure, but it's adequate, given the benefits
    of maintaining clear-text passwords. The encryption key must be coded
    in the SQL statements that reside in imapd.conf. It's also sent over
    the network along with each SQL query.

    My first attempt was with these statements in imapd.conf:

     sasl_sql_select: select decode(%p,'secret') from sasl where username = '%u' and realm = '%r'
     sasl_sql_insert: insert into sasl (username, realm, %p) values ('%u', '%r', encode('%v','secret'))
     sasl_sql_update: update sasl set %p = encode('%v','secret') where username = '%u' and realm = '%r'

    This mostly worked, except that `sasl_auto_transition' caused this error:

     Mar 17 10:44:30 setup01 imapd[2086]: [ID 702911 auth.error] sql query failed: You have an error in your SQL syntax near '*,'secret') from sasl where username = 'mills' and realm = 'testing.umanitoba.' at line 1

    This happened because the SQL auxprop sets the property (%p) to "*",
    which is not valid in the decode() function. It's attempting to
    determine if the user exists in the table. The only solution I could
    find was to avoid %p this way:

     sasl_sql_select: select decode(userPassword,'secret') from sasl where username = '%u' and realm = '%r'

    Is there a better way?

    -Gary Mills-    -Unix Support-    -U of M Academic Computing and Networking-

  • Next message: Kenny Olano: "Compile sasl2 with mysql support"

    Hosted Email Solutions

    Invaluement Anti-Spam DNSBLs

    Powered By FreeBSD   Powered By FreeBSD