[Dovecot] How to prevent SQL injection

[Jochen Schulz]

Hi,

on my way home today I thought a little bit about my setup which
involves user and password lookups in an SQL database (Postgres). I
asked myself whether I need to do anything to prevent SQL injection via
forged user or domainnames.

In the wiki I didn't find anything specific, only
http://wiki.dovecot.org/Variables which mentions that there is the %E
modifier which escapes single quites and backslashes. This appears to be
a good idea but I am asking myself whether I need to do this since it is
not mentioned anywhere. Is anybody able to comment on this?

And BTW, it appears that one can use several modifiers at once. This is
only implicitly mentioned in the wiki (You can apply modifier*s*), but
it appears to work.

J.
--
Ultimately, the Millenium Dome is a spectacular monument of the
doublethink of our times.
[Agree] [Disagree]
<http://www.slowlydownward.com/NODATA/data_enter2.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
Url : http://dovecot.org/pipermail/dovecot/attachments/20070129/f4679428/attachment.pgp

[Jakob Hirsch]

Post by Jochen Schulz
on my way home today I thought a little bit about my setup which
involves user and password lookups in an SQL database (Postgres). I
asked myself whether I need to do anything to prevent SQL injection via
forged user or domainnames.

RTSL! Every sql driver has its own escape function, which is called for
every %var string.

This was discussed before:
http://dovecot.org/list/dovecot/2006-November/017610.html

[Jochen Schulz]

Post by Jakob Hirsch

Post by Jochen Schulz
on my way home today I thought a little bit about my setup which
involves user and password lookups in an SQL database (Postgres). I
asked myself whether I need to do anything to prevent SQL injection via
forged user or domainnames.

RTSL! Every sql driver has its own escape function, which is called for
every %var string.
http://dovecot.org/list/dovecot/2006-November/017610.html

D'ouh! I even remember having read that a while ago before I enabled SQL
authentication. Thanks for me reminding me that all is well. :)

J.
--
Americans have a better life.
[Agree] [Disagree]
<http://www.slowlydownward.com/NODATA/data_enter2.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
Url : http://dovecot.org/pipermail/dovecot/attachments/20070130/17d618e2/attachment.pgp

[Joseba Torre]

Hi,

just cleaning the config file, and I found:

# List of allowed characters in username. If the user-given username contains
# a character not listed in here, the login automatically fails. This is just
# an extra check to make sure user can't exploit any potential quote escaping
# vulnerabilities with SQL/LDAP databases. If you want to allow all
characters,
# set this value to empty.
#auth_username_chars =
abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890.-_@

Aaaaaaaaagur.

Post by Jochen Schulz
Hi,
on my way home today I thought a little bit about my setup which
involves user and password lookups in an SQL database (Postgres). I
asked myself whether I need to do anything to prevent SQL injection via
forged user or domainnames.
In the wiki I didn't find anything specific, only
http://wiki.dovecot.org/Variables which mentions that there is the %E
modifier which escapes single quites and backslashes. This appears to be
a good idea but I am asking myself whether I need to do this since it is
not mentioned anywhere. Is anybody able to comment on this?
And BTW, it appears that one can use several modifiers at once. This is
only implicitly mentioned in the wiki (You can apply modifier*s*), but
it appears to work.
J.

[Jürgen Herz]

Hi Jochen,

Post by Jochen Schulz
In the wiki I didn't find anything specific, only
http://wiki.dovecot.org/Variables which mentions that there is the %E
modifier which escapes single quites and backslashes. This appears to be
a good idea but I am asking myself whether I need to do this since it is
not mentioned anywhere. Is anybody able to comment on this?

Escaping is a nice mitigation.
But the method of choice ist are prepared statements (either in stored
procedures or in the application). This is not only more secure than
dynamically building SQL statements but also a bit faster.
In fact it can accelerate the app even more since no escaping is needed
then.

Hopefully Dovecot is already doing it that way.

J?rgen

[Timo Sirainen]

Post by Jürgen Herz
Hi Jochen,

Post by Jochen Schulz
In the wiki I didn't find anything specific, only
http://wiki.dovecot.org/Variables which mentions that there is the %E
modifier which escapes single quites and backslashes. This appears to be
a good idea but I am asking myself whether I need to do this since it is
not mentioned anywhere. Is anybody able to comment on this?

Escaping is a nice mitigation.
But the method of choice ist are prepared statements (either in stored
procedures or in the application). This is not only more secure than
dynamically building SQL statements but also a bit faster.
In fact it can accelerate the app even more since no escaping is needed
then.
Hopefully Dovecot is already doing it that way.

Last I checked MySQL library didn't support prepared statements at all.
Maybe v5 finally does?

Anyway, other reasons why Dovecot doesn't use prepared statements is
because it limits what you can do with the SQL queries. Some people
really are using for example dynamic table names such as users_%s.

Maybe I'll add support for prepared statements some day, and then make
it optional to use in the SQL queries. I don't think it'll give that big
of a performance increment though, compared to what else is needed to be
done in the authentication.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
Url : http://dovecot.org/pipermail/dovecot/attachments/20070130/2bf9153a/attachment.pgp

[Jakob Hirsch]

Post by Timo Sirainen
Last I checked MySQL library didn't support prepared statements at all.
Maybe v5 finally does?

mysql's C API does it since 4.1 (see
http://dev.mysql.com/doc/refman/4.1/en/c-api-prepared-statements.html et
sqq.). In theory, it should make things faster, but last time I check
(with 5.0, AFAIR), it didn't give any performance advantage (was even
slightly slower), but that may heavily depend on the environment, flags etc.
The nice thing about prepared statements is, IMO, that you don't have to
mess around with the query string.

[David Nugent]

Post by Jakob Hirsch

Post by Timo Sirainen
Last I checked MySQL library didn't support prepared statements at all.
Maybe v5 finally does?

mysql's C API does it since 4.1 (see
http://dev.mysql.com/doc/refman/4.1/en/c-api-prepared-
statements.html et
sqq.). In theory, it should make things faster, but last time I check
(with 5.0, AFAIR), it didn't give any performance advantage (was even
slightly slower), but that may heavily depend on the environment, flags etc.

Yes, it will be slower in many cases because MySQL prepared
statements don't use the query cache (but it depends on whether
queries would get any advantage from caching in the first place).

Regards,
David

[Jürgen Herz]

Post by Timo Sirainen

Post by Jürgen Herz
Escaping is a nice mitigation.
But the method of choice ist are prepared statements (either in stored
procedures or in the application). This is not only more secure than
dynamically building SQL statements but also a bit faster.
In fact it can accelerate the app even more since no escaping is needed
then.
Hopefully Dovecot is already doing it that way.

Last I checked MySQL library didn't support prepared statements at all.
Maybe v5 finally does?

MySQL 5 does but 4.1 already does also.

Post by Timo Sirainen
Anyway, other reasons why Dovecot doesn't use prepared statements is
because it limits what you can do with the SQL queries. Some people
really are using for example dynamic table names such as users_%s.

Yes, prepared statements are a little more complicated in general and
they might even a bit more if they're to be constructed dynamically. But
it can be done without problems.
However, there might security implications if the field name itself is
composed of user input.

Post by Timo Sirainen
Maybe I'll add support for prepared statements some day, and then make
it optional to use in the SQL queries. I don't think it'll give that big
of a performance increment though, compared to what else is needed to be
done in the authentication.

Surely the improved performance doesn't carry weight in the
authentication scenario. What I wrote was just general and when a
statement is reused often.

J?rgen