[Dovecot] Case sensitive nightmare
It took me 4 days to figure this out and I sure hope someone can help me solve it.
My Setup: Postfix + MySQL + dovecot
dovecot.conf:
default_mail_env = maildir:/var/spool/vmail/%d/%n/Maildir password_query = SELECT password FROM mailbox WHERE username='%u' user_query = SELECT maildir, 108 AS uid, 108 AS gid FROM mailbox WHERE username='%u'
The virtual host schema is postfix.admin compatible but I¹m not using it (phpMyAdmin is actually easier to use).
When foo@domain.tld, abrand new account, logs in using an IMAP client (any client; squirrelmail, OE, tbird, Apple...) a maildir is created named domain.tld/foo/Maildir¹ and everything is good. Now I login as Foo@domain.tld and then as fOo@domain.tld and then as foO@domain.tld then so on for every case variation. Each of these accounts can login correctly, they are seen as being the same account by dovecot. On the other hand they are not seen as being the same mailbox! When I check my mail spool I see one maildir for every case variation (domain.tld/foo/Maildir, domain.tld/FOO/Maildir, etc.)
It would seem to me that the queries above would fail for the different variations of the name but they don¹t seem to. Is dovecot converting %u to lower case for the query on the virtual users table but then using it unconverted when looking up the Mairdir for the account? If the username column has the value foo@domain.tld¹ how does SELECT * FROM table WHERE username=¹FOO@domain.tld¹ match the row?
Also puzzling is that I select a column named maildir which has the path to the mailbox in it (using the correct case). Even if SELECT pigs fly¹ AS when WHERE FoO¹=¹foo¹ the maildir path for the account is being returned which is domain.tld/foo/Maildir¹ so how does domain.tld/FoO/Maildir¹ get created?
This is both a pain in the nuts support nightmare but also a security problem (albeit fairly limited). An account named ³barneyrubble² could generate a significant number of maildir directories if you logged in with every variation thereof.
Any assistance would be greatly appreciated. Thanks
Take a look at file variables.txt. It is available in dovecot source tarball in doc/ directory.
-- Kind Regards, Alexander Shikoff minotaur@crete.org.ua
Erik Petersen wrote:
It would seem to me that the queries above would fail for the different variations of the name but they don¹t seem to.
Searches in MySQL are not case sensitive:
http://dev.mysql.com/doc/refman/4.1/en/case-sensitivity.htmlso you can use the information in doc/variables.txt to decide whether you want to treat user names as UPPPER or lower case by default.
John
-- John Peacock Director of Information Research and Technology Rowman & Littlefield Publishing Group 4720 Boston Way Lanham, MD 20706 301-459-3366 x.5010 fax 301-429-5747
On 12/11/05 7:39 PM, "John Peacock" <jpeacock@rowman.com> wrote:
Erik Petersen wrote:
It would seem to me that the queries above would fail for the different variations of the name but they don¹t seem to.
Searches in MySQL are not case sensitive:
http://dev.mysql.com/doc/refman/4.1/en/case-sensitivity.html
True, WHERE 'FOO' like 'foo' is a match but my user_query is WHERE username = '%u'
so you can use the information in doc/variables.txt to decide whether you want to treat user names as UPPPER or lower case by default.
I'm using the binary install on Fedora Core 4 -- yum install dovecot -- (0.99.14). I don't see a variables.txt file in the source tarball. I guess I should build from 1.0 alpha source?
Thanks
Erik Petersen wrote: On 12/11/05 7:39 PM, "John Peacock" <jpeacock@rowman.com> wrote:
Searches in MySQL are not case sensitive:
http://dev.mysql.com/doc/refman/4.1/en/case-sensitivity.html
True, WHERE 'FOO' like 'foo' is a match but my user_query is WHERE username = '%u'
SELECT 'Foo' = 'foo'
gives 1 (true) to me.
You have have to declare your columns as VARCHAR(..) BINARY. Then MySQL will do no case-folding whatsoever.
Best Regards, Michael Paesold
On 12/11/05 11:27 PM, "Michael Paesold" <mpaesold@gmx.at> wrote:
Erik Petersen wrote: On 12/11/05 7:39 PM, "John Peacock" <jpeacock@rowman.com> wrote:
Searches in MySQL are not case sensitive:
http://dev.mysql.com/doc/refman/4.1/en/case-sensitivity.html
True, WHERE 'FOO' like 'foo' is a match but my user_query is WHERE username = '%u'
SELECT 'Foo' = 'foo'
gives 1 (true) to me.
You have have to declare your columns as VARCHAR(..) BINARY. Then MySQL will do no case-folding whatsoever.
My bad. Thanks.
On Sun, 2005-12-11 at 16:34 -0800, Erik Petersen wrote:
password_query = SELECT password FROM mailbox WHERE username='%u'
One way to solve this would be to use:
password_query = select password, username as user from mailbox where username = '%u'
Then Dovecot internally changes the username to same casing as it's in the SQL database.
participants (5)
-
Alexander Shikoff
-
Erik Petersen
-
John Peacock
-
Michael Paesold
-
Timo Sirainen