Sorry, been busy + also this mail somehow got marked as read.
On Fri, 2010-03-05 at 23:44 +0000, Alain Williams wrote:
I tried to use MySQL stored procedures from dovecot:
password_query = CALL user_pass_check('%n', '%d', '%w')
user_query = CALL user_info('%n', '%d')
This failed with the message: User query failed: PROCEDURE imap.user_info can't return a result set in the given context
I thought it was possible to avoid that error by implementing the MySQL procedure in a specific way?..
The root of this problem is that mysql_real_connect() needs to be called with option CLIENT_MULTI_RESULTS
The problem with doing that is that 1) it's not normally necessary and more importantly 2) doing that makes any potential SQL injection security holes a lot easier to exploit. So I'm not all that eager in adding such code, especially if it can be worked around another way..
I am looking to use mysql procedures, there are some interesting things that can be done. Two suggestions that I have will help with this:
There be variable (say) %o - this be the obscured password, ie what password_query returns.
that dovecot look for either ''password_query'' as above, or ''password_check''. password_check would NOT return a password, but would be given %o and determine itself if the password is correct. It would return the other values (user, userdb_home, ...) and auth_result that would encode success/retry/fail (0, 1, 2 - or maybe more structured [**]) and auth_reason some human readable reason. The ''nologin'' value encodes some of this.
The motivation for this is that my stored procedure will record the number of successive login failures and lock the account after 3 of them. It would also be possible to do time based restrictions & the such.
Also: by passing %o the password is not sent in plain to the database server - which will increase security.
So what kind of obscuring where you thinking about? You could already use "%Mw", which gives you MD5 of the password.
And password_query can already do basically what you were thinking about with password_check:
password_query = select NULL as password, 'Y' as nopassword FROM users WHERE username = '%u' and password = '%w'
or something. The main problem with that is that it can't differentiate between "wrong password" and "user doesn't exist", so it logs all password failures as "user doesn't exist".