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

Question

I am looking at a Linux-based perl web application containing an ubiquitous login handler

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

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

It is well known that the DBI documentation recommends changing this code to use the "?" instead of "$ userid" for security.

This code was isolated in a turned off network field, as is, to verify security. . Code like this on an Internet server will eventually be hacked, as there are now bots that scan for this vulnerability. Access control is also ineffective in protecting something important, because known injections can delete databases, insert bad data or new users, or bypass access control to allow writing to a web application.

Since the application can be configured to use PostgreSQL or MySQL, and questions were raised regarding the comparative vulnerability, I tried both databases and tested each configuration with some attempts to implement SQL.

In PostgreSQL, input '; do bad things here; and here; will crash the cgi login as expected and execute bad stuff.

What was unexpected was that MySQL resisted this attack. This made me wonder if there was some kind of setup for DBD :: MySQL or elsewhere that limited preparation for 1 statement per call or was MySQL resistant in some other way.

As far as I understand, MySQL is not resistant to SQL injection in general.

It is not a question solely of methods for eliminating SQL injection; for this, maybe see. How can I avoid SQL injection attacks? .

The question is that MySQL is somehow more resilient than PostgreSQL to SQL injection attack under PERL DBI and why can this be so?

Answer 1

The MySQL client library apparently limits one statement per call by default (I came across it with PHP).

But this should not be the reason for using MySQL over PostgreSQL, since you can still enter using subqueries.

Answer 2

Protection against injection attacks is not the responsibility of the database, it is the responsibility of the developer. If the developer writes code that creates requests by concatenating strings obtained from user input, the received requests will be vulnerable to injection attacks, and all the code spent on sanitation, etc., is IMHO a waste of time. If the code is written to use parameterized queries, and user input is canceled to be used as parameter values, the received queries will be safe enough for injection attacks. (And I would be interested to know how to make an injection attack through the parameter value).

Share and enjoy.

Answer 3

No, in fact, MySQL is almost categorically less secure, in which case it looks as if the preparation instructions are not executed at all on the server.

Support for supported operators (server side preparation) Starting from 3.0002_1, server side prepared default statements (if your server was> = 4.1.3). As of 3.0009, they were disabled by default again due to problems with prepared approval APIs (all other mysql connectors are set this way until C API problems are fixed). the requirement to use prepared statements still remains that you have a server '> = 4.1.3'

To use the server side of the application, all you have to do is set the mysql_server_prepare variable to the connection:

$ dbh = DBI-> connect ("DBI: MySQL: databases = test, host = local; mysql_server_prepare = 1", "," ", {RaiseError => 1, AutoCommit => 1});

• Note: the separator for this parameter is ';'

There are many advantages to using server-side preparation, mainly if you are performing many inserts because a single expression is ready to accept multiple insertion values.

To make sure that the “do the test” step checks to see if server preparation works, you just need to export the env variable MYSQL_SERVER_PREPARE:

export MYSQL_SERVER_PREPARE = ​​1

Presumably, they are prepared on a server in PostgreSQL.

As for security, you are just doing it wrong: use ? , as you said. Otherwise, you simply use the fact that Postgres can prepare several statements: it is not something negative. I suppose MySQL might do it too, the only difference here is DBD :: MySQL claims that API C problems preclude server-side use, so they rely on some other source as authoritative for the server. Currently, DBD :: MySQL probably uses the C function in the mysql library, which precedes the preparation of MySQL on the server side (pre 4.1.3 is my guess).

Answer 4

It might be difficult, if not impossible, to have a common SQL injection disinfectant.

Added [As commented, using the DBI correctly and using the DB client library, injections can certainly be minimized with respect to preparing the SQL statement. However, it is important to keep in mind that user input disinfection also includes application logic that is independent of the database used. For example, using other user credentials can provide a valid and secure statement, but with unintended consequences. In any case, this goes beyond the question asked.]

Deleted [It’s better not to sanitize the entrance yourself, and not have any sense of false security on the client that counteracts these types of attacks. It’s not that you didn’t use them, just don’t assume that they provide more than minimal help against attacks.]