[Dovecot] How to prevent SQL injection
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
Quoting 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
Jakob Hirsch:
Quoting 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
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
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.
El Lunes, 29 de Enero de 2007 23:29, Jochen Schulz escribió:
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.
-- Joseba Torre. CIDIR Bizkaia.
Hi Jochen,
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
On Tue, 2007-01-30 at 12:12 +0100, Jürgen Herz wrote:
Hi Jochen,
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.
Quoting 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.
On 30/01/2007, at 11:28 PM, Jakob Hirsch wrote:
Quoting 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
Timo Sirainen wrote:
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.
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.
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
participants (6)
-
David Nugent
-
Jakob Hirsch
-
Jochen Schulz
-
Joseba Torre
-
Jürgen Herz
-
Timo Sirainen