dovecot - Mar 2010 - Bug in driver-mysql.c + fix

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

The root of this problem is that mysql_real_connect() needs to be called with
option CLIENT_MULTI_RESULTS
and mysql_next_result() called to retrieve extra results (that will not exist in
the way that we use it).
I attach a patch that fixes this problem -- the patch is against dovecot-1.2.10.
This works for me ... but could probably do with testing by other people.

BTW: I got the same problem with exim this morning, wrote a patch that has now
been accepted.
The dovecot patch is similar.


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.

I will publish my stored procedures when done.

Regards

[**] eg taking ideas from the HTTP codes:
200 - OK
300 5 - try again in 5 minutes
301 2 9 - try again on tuesday at 9am
400 - Login forbidden, no reason given
401 - bad username and/or password
402 - account locked administratively
403 - too many failed login attempts
500 - authentication system error
The above would allow a native language version of auth_reason to be produced

-- 
Alain Williams
Linux/GNU Consultant - Mail systems, Web sites, Networking, Programmer, IT
Lecturer.
+44 (0) 787 668 0256  http://www.phcomp.co.uk/
Parliament Hill Computers Ltd. Registration Information:
http://www.phcomp.co.uk/contact.php
Past chairman of UKUUG: http://www.ukuug.org/
#include <std_disclaimer.h>
-------------- next part --------------
--- driver-mysql.c	2010-03-05 19:38:10.512212871 +0000
+++ driver-mysql.c	2010-03-05 19:38:17.000000000 +0000
@@ -132,9 +132,10 @@
 	}
 
 	alarm(MYSQL_CONNECT_FAILURE_TIMEOUT);
+	/* CLIENT_MULTI_RESULTS allows the use of stored procedures */
 	failed = mysql_real_connect(conn->mysql, host, db->user,
db->password,
 				    db->dbname, db->port, unix_socket,
-				    db->client_flags) == NULL;
+				    db->client_flags | CLIENT_MULTI_RESULTS) == NULL;
 	alarm(0);
 	if (failed) {
 		if (conn->connect_failure_count > 0) {
@@ -429,6 +430,7 @@
 	struct mysql_db *db = (struct mysql_db *)_db;
 	struct mysql_connection *conn;
 	struct mysql_result *result;
+	int nr;
 
 	result = i_new(struct mysql_result, 1);
 	result->api = driver_mysql_result;
@@ -442,6 +444,17 @@
 	case 1:
 		/* query ok */
 		result->result = mysql_store_result(conn->mysql);
+		while((nr = mysql_next_result(conn->mysql)) >= 0) {
+			/* more results? -1 = no, >0 = error, 0 = yes
+			 * Because of the CLIENT_MULTI_RESULTS on mysql_real_connect()
+			 * we need to read extra results - there should not be any.
+			 */
+			if(nr == 0)   /* Just ignore more results */
+				continue;
+
+			result->api = driver_mysql_error_result;
+			goto off;
+		}
 		if (result->result != NULL || mysql_errno(conn->mysql) == 0)
 			break;
 		/* fallback */
@@ -451,6 +464,7 @@
 		break;
 	}
 
+off:
 	result->conn = conn;
 	return &result->api;
 }

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".
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 198 bytes
Desc: This is a digitally signed message part
URL:
<http://dovecot.org/pipermail/dovecot/attachments/20100310/328341a7/attachment-0002.bin>

On Wed, Mar 10, 2010 at 04:18:19PM +0200, Timo Sirainen wrote:
> On Fri, 2010-03-05 at 23:44 +0000, Alain Williams wrote:
...
> > 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?..
I have tried ... see another thread.

See:
	http://dev.mysql.com/doc/refman/5.1/en/mysql-real-connect.html

Just under the table near the top it says:

	If your program uses CALL statements to execute stored procedures, the
CLIENT_MULTI_RESULTS flag must be enabled.
> > 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..
CLIENT_MULTI_STATEMENTS allows multiple statements in one call (you separate by
',').
CLIENT_MULTI_RESULTS does not imply CLIENT_MULTI_STATEMENTS.
Is this what you were concerned about ?

Look at the URL above for details.
> > 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.
That is not how I store passwords - I keep them as DIGEST-MD5, this is:
	md5('username:domain:password')
So I want %o to be that value. Squirrelmail should be able to deduce that from
the line in the dovecot-sql.conf:
	default_pass_scheme="DIGEST-MD5"
> 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".
I am trying to find a definition of the API to plugins, ...
if the SQL stored procedure can return arbitrary variables that can then be used
by PHP plugins then I can do things like issuing a warning about the password
about to expire, number of failed login attempts since the last success, ...
Ie all sorts of things that the authentication stored procedures could store
and manage.

-- 
Alain Williams
Linux/GNU Consultant - Mail systems, Web sites, Networking, Programmer, IT
Lecturer.
+44 (0) 787 668 0256  http://www.phcomp.co.uk/
Parliament Hill Computers Ltd. Registration Information:
http://www.phcomp.co.uk/contact.php
Past chairman of UKUUG: http://www.ukuug.org/
#include <std_disclaimer.h>