SQL Injection through mysql_query [duplicate]

Question

I'm working on a site that has been hacked through SQL Injection (at first glance only db entries are corrupted with cross-site scripting) the potential vulnerability I found after looking at the code is that there's a lot of mysql_query call whose inputs are not escaped at all.

The good old :

$query = "SELECT * FROM mytable where name LIKE '%".$_GET['name']."%'"; /*HACK HERE*/
mysql_query($query, $connection);

Nevertheless I can't find how can we do something cool from that injection vulnerability (by cool I mean something like an INSERT or an UPDATE). I've tried to build a statement like this one :

SELECT * FROM mytable where name LIKE '%' AND WHERE id IN (INSERT INTO secondtable (id,description) VALUES (15, 'Fifteenth description');--%'

No success. I guess that the INSERT has nothing to do here.

I'm escaping all user's inputs in the code right now but I've not really get how hackers have penetrated this site, then I'm not 100% sure that my fix will do the job. Any brilliant suggestions ?

Thanks

Answer 1

Interesting that your question hasn't received many (correct) answers yet!

As you discovered, usual PHP MySQL APIs like mysql_query, mysqli::query etc. only execute the first SQL statement in case one passes several of them (separated by semicolons), as would an attacker using the most common class of SQL injections.

Defender tip: banish mysqli::multi_query and friends from your code; the minute performance improvements are not worth the risk.

Does this clever move by PHP-folk completely close all attack channels on some code that goes like "SELECT yadda yadda" . $_GET["untrusted"]? Not quite. As knittl remarks, even a pure-DQL SELECT can be used to escalate one's privileges by UNION ALL SELECT'ing from any nearby interesting table, including but not limited to the passwords table. There are cheat sheets out there giving enough tips and tricks to basically extract the entire database this way.

Defender tip: apply defense-in-depth with known-good techniques:

• input validation
• whitelist indirection in an associative array
• prepared statements (if you can make sure that they are effective and not just string escaping in disguise!)
• an ORM
• protective encoding (e.g. Base64) when the untrusted input cannot be satisfactorily sanitized (e.g. a blog post that may legitimately contain SQL-sensitive punctuation)
• or as a last resort only, string escaping

Next, one may observe that not all DQL is side-effect free, in particular when it ends with INTO DUMPFILE somethingsomething.

Defender tip: always configure secure_file_priv in your MySQL / MariaDB server.

Last but not least, even an attacker who is in a position to inject arbitrary SQL is limited by the authority granted to the Web app as a whole.

Defender tip: secure your app by applying POLA.

• Only GRANT the Web app's MySQL user as much authority as it needs. It is not a bad idea to design your app so that it requires no DDL at all. If you must provide a “back up / restore DB” feature or some such from the Web UI, use a separate MySQL user for that.
• Automate backups, even though they are useless — restores are what matters.

Answer 2

Depending upon the version of mysql you are using, and the setup of the connection, mysql_query may allow more than one statement.

You should look at how the connection is being created, and for any usage of mysql_set_server_option.