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: > > 1) There be variable (say) %o - this be the obscured password, ie what > password_query returns. > > 2) 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".
signature.asc
Description: This is a digitally signed message part