MySQL Injection - Use SELECT query to UPDATE/DELETE

Question

I've got one easy question: say there is a site with a query like:

SELECT id, name, message FROM messages WHERE id = $_GET['q'].

Is there any way to get something updated/deleted in the database (MySQL)? Until now I've never seen an injection that was able to delete/update using a SELECT query, so, is it even possible?

Solution 1

If you say you use mysql_query that doesn't support multiple queries, you cannot directly add DELETE/UPDATE/INSERT, but it's possible to modify data under some circumstances. For example, let's say you have the following function

DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `testP`()
RETURNS int(11)
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
COMMENT ''  
BEGIN      
  DELETE FROM test2;
  return 1;
END //

Now you can call this function in SELECT :
SELECT id, name, message FROM messages WHERE id = NULL OR testP() (id = NULL - always NULL(FALSE), so testP() always gets executed.

Solution 2

Before directly answering the question, it's worth noting that even if all an attacker can do is read data that he shouldn't be able to, that's usually still really bad. Consider that by using JOINs and SELECTing from system tables (like mysql.innodb_table_stats), an attacker who starts with a SELECT injection and no other knowledge of your database can map your schema and then exfiltrate the entirety of the data that you have in MySQL. For the vast majority of databases and applications, that already represents a catastrophic security hole.

But to answer the question directly: there are a few ways that I know of by which injection into a MySQL SELECT can be used to modify data. Fortunately, they all require reasonably unusual circumstances to be possible. All example injections below are given relative to the example injectable query from the question:

SELECT id, name, message FROM messages WHERE id = $_GET['q']

1. "Stacked" or "batched" queries.

The classic injection technique of just putting an entire other statement after the one being injected into. As suggested in another answer here, you could set $_GET['q'] to 1; DELETE FROM users; -- so that the query forms two statements which get executed consecutively, the second of which deletes everything in the users table.

In mitigation

Most MySQL connectors - notably including PHP's (deprecated) mysql_* and (non-deprecated) mysqli_* functions - don't support stacked or batched queries at all, so this kind of attack just plain doesn't work. However, some do - notably including PHP's PDO connector (although the support can be disabled to increase security).

2. Exploiting user-defined functions

Functions can be called from a SELECT, and can alter data. If a data-altering function has been created in the database, you could make the SELECT call it, for instance by passing 0 OR SOME_FUNCTION_NAME() as the value of $_GET['q'].

In mitigation

Most databases don't contain any user-defined functions - let alone data-altering ones - and so offer no opportunity at all to perform this sort of exploit.

3. Writing to files

As described in Muhaimin Dzulfakar's (somewhat presumptuously named) paper Advanced MySQL Exploitation, you can use INTO OUTFILE or INTO DUMPFILE clauses on a MySQL select to dump the result into a file. Since, by using a UNION, any arbitrary result can be SELECTed, this allows writing new files with arbitrary content at any location that the user running mysqld can access. Conceivably this can be exploited not merely to modify data in the MySQL database, but to get shell access to the server on which it is running - for instance, by writing a PHP script to the webroot and then making a request to it, if the MySQL server is co-hosted with a PHP server.

In mitigation

Lots of factors reduce the practical exploitability of this otherwise impressive-sounding attack:

• MySQL will never let you use INTO OUTFILE or INTO DUMPFILE to overwrite an existing file, nor write to a folder that doesn't exist. This prevents attacks like creating a .ssh folder with a private key in the mysql user's home directory and then SSHing in, or overwriting the mysqld binary itself with a malicious version and waiting for a server restart.
• Any halfway decent installation package will set up a special user (typically named mysql) to run mysqld, and give that user only very limited permissions. As such, it shouldn't be able to write to most locations on the file system - and certainly shouldn't ordinarily be able to do things like write to a web application's webroot.
• Modern installations of MySQL come with --secure-file-priv set by default, preventing MySQL from writing to anywhere other than a designated data import/export directory and thereby rendering this attack almost completely impotent... unless the owner of the server has deliberately disabled it. Fortunately, nobody would ever just completely disable a security feature like that since that would obviously be - oh wait never mind.

4. Calling the sys_exec() function from lib_mysqludf_sys to run arbitrary shell commands

There's a MySQL extension called lib_mysqludf_sys that - judging from its stars on GitHub and a quick Stack Overflow search - has at least a few hundred users. It adds a function called sys_exec that runs shell commands. As noted in #2, functions can be called from within a SELECT; the implications are hopefully obvious. To quote from the source, this function "can be a security hazard".

In mitigation

Most systems don't have this extension installed.

Solution 3

It depends on the DBMS connector you are using. Most of the time your scenario should not be possible, but under certain circumstances it could work. For further details you should take a look at chapter 4 and 5 from the Blackhat-Paper Advanced MySQL Exploitation.

Comments

[Simon]

MySQL doesn't support stacked queries

[Pentium10]

You are WRONG! sqlmap.sourceforge.net/doc/… Also please check this: ferruh.mavituna.com/sql-injection-cheatsheet-oku

[rzetterberg]

@Kevin, it does. If you copy that exact query and run it in phpmyadmin for example it will run with no errors. However if you use a mysql library which doesn't support multiple queries you will get an error. For example if you use php's function mysql_query and input that sql query you will get an error that it doesn't support multiple queries in one call, but if you use the mysqli library instead the query runs fine.

[Simon]

mysql_query in PHP doesn't support it :| And that's one of the most used one in PHP-based websites I guess... Is there a way to get around this?

[Pentium10]

any answer that includes "most used" or "I guess" is incorrect.

[rzetterberg]

@Kevin, like I said in my comment, use mysqli instead. php.net/manual/en/class.mysqli.php

[Simon]

@Ancide, It's about someone else his site, but I was just curious if it was possible to DELETE/UPDATE/INSERT without stacked queries (mysql_query)?

[NotMe]

@Kevin: You might want to read this: seclists.org/fulldisclosure/2011/Mar/309 and this: tizag.com/mysqlTutorial/mysql-php-sql-injection.php

[rzetterberg]

@Kevin That I'm not sure about! Maybe someone can verify if it could be done by doing a subquery such as "1 AND (DELETE FROM users)"?