Is MySQL more resistant to SQL injection attack than PostgreSQL (under Perl/DBI) ?

Question

I am reviewing a Linux based perl web application that contains a login handler with the ubiquitous

my $sth = $DB->prepare("SELECT password from passwords where userid='$userid'") or die; $sth->execute or die; ...

where $userid is initialized from (unsafe, unfiltered) web user input.

It is well known that the DBI documentation recommends that this code should be changed to use the placeholder "?" in place of '$userid' for security.

This code was isolated on an off network box, as-is, for the purpose of a security review. Code like this on an internet server will eventually be cracked as there are bots now that scan for this vulnerability. The access control is also ineffective for protecting anything important because known injections can delete databases, insert bad data or new users, or bypass the access control to allow entry to the web application.

As the application can be configured to use either PostgreSQL or MySQL and questions were raised about comparative vulnerability I tried out both databases and tested each configuration with some SQL injection attempts.

Under PostgreSQL an input of '; do bad stuff here; and here; would crash the login cgi as expected and execute the bad stuff.

What was unexpected was that MySQL resisted this attack. This got me to wonder if there was a setting of some sort for DBD::MySQL or elsewhere that limited prepare to 1 statement per call, or was MySQL resistant in some other way.

As I understand it MySQL is not SQL-injection resistant in general.

This is not a question purely about techniques for eliminating SQL injection; for that perhaps see http://stackoverflow.com/questions/2200256/how-can-i-avoid-sql-injection-attacks.

The question is: Is MySQL somehow more resistant than PostgreSQL to SQL injection attack under the PERL DBI and why might this be the case?

Answer 1

The MySQL client library seems to limit to one statement per call by default (I encountered it with PHP).

But that's shouldn't be a reason to use MySQL over PostgreSQL, since you can still inject by using subqueries.

Answer 2

It might be difficult, if not impossible, to have a generic sanitizer against SQL injections.

Added [As commented, using the DBI correctly and with the help of the DB's client library, the injections can certainly be minimized with respect to preparing the SQL statement. However, it's important to keep in mind that sanitizing user input also involves application logic which is independent of the DB used. For example, using another user's credentials may provide a valid and safe statement, but with unintended consequences. Anyway, that's going further than the question asked.]

Removed [You're better off sanitizing the input yourself rather than having any sense of false security on the client's resistance to these types of attacks. Not that you shouldn't use them, just don't assume they provide more than minimal help against the attacks.]

Answer 3

Guarding against injection attacks is not the responsibility of the database, it's the responsibility of the developer. If the developer writes code that creates queries by concatenating strings derived from user input the resulting queries will be vulnerable to injection attacks, and all the code spent on sanitization, etc, is IMHO a waste of time. If the code is written to use parameterized queries, and user input is relegated to being used as parameter values, the resulting queries will be reasonably safe from injection attacks. (And I'd be interested in hearing how it might be possible to do an injection attack through a parameter value).

Share and enjoy.

Answer 4

No, in fact, MySQL is almost categoricially less secure, in this case it appears as if the prepare statements are not done on the server at all.

Prepared statement support (server side prepare) As of 3.0002_1, server side prepare statements were on by default (if your server was >= 4.1.3). As of 3.0009, they were off by default again due to issues with the prepared statement API (all other mysql connectors are set this way until C API issues are resolved). The requirement to use prepared statements still remains that you have a server '>= 4.1.3'

To use server side prepared statements, all you need to do is set the variable mysql_server_prepare in the connect:

$dbh = DBI->connect( "DBI:mysql:database=test;host=localhost;mysql_server_prepare=1", "", "", { RaiseError => 1, AutoCommit => 1 } );

• Note: delimiter for this param is ';'

There are many benefits to using server side prepare statements, mostly if you are performing many inserts because of that fact that a single statement is prepared to accept multiple insert values.

To make sure that the 'make test' step tests whether server prepare works, you just need to export the env variable MYSQL_SERVER_PREPARE:

export MYSQL_SERVER_PREPARE=1

Presumably, they are prepared on the server in PostgreSQL.

So far as security is concerned, you're simply doing it wrong: use ? as you've said. Otherwise you're just exploiting that Postgres can prepare multiple statements: and that isn't something negative. I believe MySQL can probably do this too, the only difference here is DBD::MySQL is claiming that C API issues preclude the use of the server-side prepares so they're relying on some other source as being authoritative for the server. Right now DBD::MySQL is probably using C function in a mysql library that predates MySQL having server-side prepares (pre 4.1.3 is my guess).