dovecot - Apr 2012 - help with AES_DECRYPT and password lookup - mysql password_query

Hello-

Sorry if this is a noob question, but I cant seem to get my password_query to
work with dovecot + mysql.  Im using 'Password verification by SQL
server' at:

http://wiki2.dovecot.org/AuthDatabase/SQL

trying to modify it to work with my encrypted passwords in the DB.

Im using the following which isnt working:

password_query = SELECT NULL AS password, \
    'Y' as nopassword, userid AS user \
    FROM users WHERE userid='%u' AND AES_DECRYPT(password,
'mykey')=password

1. Is it even possible to do this via 'password_query'?

2. If so, what am I doing wrong?

Thanks,
Jeff

/mf/home/jeep/shell/.signature

On 04/28/2012 04:54 PM, Jeff Lacki wrote:
> Hello-
>
> Sorry if this is a noob question, but I cant seem to get my password_query
to
> work with dovecot + mysql.  Im using 'Password verification by SQL
server' at:
>
> http://wiki2.dovecot.org/AuthDatabase/SQL
>
> trying to modify it to work with my encrypted passwords in the DB.
>
> Im using the following which isnt working:
>
> password_query = SELECT NULL AS password, \
>      'Y' as nopassword, userid AS user \
>      FROM users WHERE userid='%u' AND AES_DECRYPT(password,
'mykey')=password
>
> 1. Is it even possible to do this via 'password_query'?
>
> 2. If so, what am I doing wrong?
>
> Thanks,
> Jeff
>
> /mf/home/jeep/shell/.signature
The wiki says: "The password is in %w variable"
Maybe you meant

password_query = SELECT NULL AS password, \
     'Y' as nopassword, userid AS user \
     FROM users WHERE userid='%u' AND AES_DECRYPT(password,
'mykey')='%w'

Hi Jeff,

Jeff Lacki wrote:
> Sorry if this is a noob question, but I cant seem to get my password_query
to
> work with dovecot + mysql.  Im using 'Password verification by SQL
server' at:
> 
> http://wiki2.dovecot.org/AuthDatabase/SQL
> 
> trying to modify it to work with my encrypted passwords in the DB.
> 
> Im using the following which isn't working:
> 
> password_query = SELECT NULL AS password, \
>     'Y' as nopassword, userid AS user \
>     FROM users WHERE userid='%u' AND AES_DECRYPT(password,
'mykey')=password
> 
> 1. Is it even possible to do this via 'password_query'?
Please provide your dovecot version and output of the following command:
doveconf -n
and the complete external sql query files without passwords.

You might alsolet the SQL server compare the encrypted
password in the database with the encrypted string:

password_query = SELECT NULL AS password, \
  'Y' as nopassword, userid AS user \
  FROM users WHERE userid='%u' AND
AES_ENCRYPT('%w','mykey')=password

Regards,
Daniel