[ACCEPTED]-MySQL Injection - Use SELECT query to UPDATE/DELETE-sql-delete

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

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

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

1. "Stacked" or "batched" queries.

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

In mitigation

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

2. Exploiting user-defined functions

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

In mitigation

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

3. Writing to files

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

In mitigation

Lots of factors reduce 10 the practical exploitability of this otherwise 9 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 8 extension called lib_mysqludf_sys that - judging from its 7 stars on GitHub and a quick Stack Overflow search - has at least a few hundred 6 users. It adds a function called sys_exec that runs 5 shell commands. As noted in #2, functions 4 can be called from within a SELECT; the implications 3 are hopefully obvious. To quote from the source, this 2 function "can be a security hazard".

In mitigation

Most systems don't have this extension 1 installed.

If you say you use mysql_query that doesn't support 6 multiple queries, you cannot directly add 5 DELETE/UPDATE/INSERT, but it's possible to modify data under 4 some circumstances. For example, let's 3 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 2 can call this function in SELECT :
SELECT id, name, message FROM messages WHERE id = NULL OR testP() (id = NULL - always 1 NULL(FALSE), so testP() always gets executed.

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