|
|
|
|
The <AuthQuery> element is the SQL query issued for login authentication. You will need the placeholders %?U, %?D, and/or %?E, as well as either %?C or %?M. The query returns one or zero rows. When zero rows are returned, the authentication fails.
When one row is returned there are two potential columns. The first holds the UNIX-style encrypted password. The second is a Boolean that is true if the authentication has succeeded. If the Boolean is false, and there is a non-NULL value in the first column, the application compares the password from the authentication command to the encrypted one that came from the query. If they match, then the authentication succeeds.
It is important to construct your <AuthQuery> so that plaintext passwords are not passed over the wire to the database server. Most database connections are not encrypted and can easily expose credentials if you are careless with your SQL. For this reason, there is no placeholder for the plaintext password received in the authentication command.
The proper way to check a database of encrypted passwords is to simply return only the first column. The proper way to check a database of plaintext passwords is to send the encrypted password in your SQL and compare it to an SQL function call that encrypts the plaintext column on the database server. This method is slow, but secure. The correct way to query a database with both kinds of data is to do both and return both columns.
The following snippet shows a very simple <AuthQuery> element that returns the encrypted user password from a password table:
<AuthQuery>
SELECT crypted
FROM crypted_passwords
WHERE mail_domain=%?D AND username=%?E
</AuthQuery>
The next snippet shows a method to write your <AuthQuery> element to check plaintext passwords on a MySQL server (other servers have different SQL functions available). Note that the first column (encrypted password) is always NULL and that the second column is the Boolean result of a comparison between the encrypted password (which is sent over the wire) and the encrypted version of the plaintext password (which is calculated on the server side). The calculation of the encrypted password on the SQL server uses the supplied encrypted password as a salt, which is standard practice (the prefix of the encrypted form holds the salt bits).
<AuthQuery>
SELECT NULL, encrypt(cleartext_password,%?C)=%?C
FROM plain_passwords
WHERE user_email=%?E
</AuthQuery>