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?
Just add them together: UPDATE LOG SET TIME_EXIT = '2013/02/22' WHERE ID = ( SELECT ID FROM employee ORDER BY ID DESC LIMIT ); But based on that code currently it'll only ever update the last employee , you will need to select the correct employee by using some other identifier to ensure you have the correct one.
SQL injections typically fall under three categories: In-band SQLi (Classic), Inferential SQLi (Blind) and Out-of-band SQLi. You can classify SQL injections types based on the methods they use to access backend data and their damage potential.
MySQL UPDATE statement, you can update the single row using the UPDATE & WHERE clause statement at a time. The DELETE statement is used to remove/delete a specific row or multiple rows using the MySQL DELETE & WHERE clause.
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 JOIN
s and SELECT
ing 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']
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.
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).
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']
.
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.
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 SELECT
ed, 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.
Lots of factors reduce the practical exploitability of this otherwise impressive-sounding attack:
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.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.--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.sys_exec()
function from lib_mysqludf_sys
to run arbitrary shell commandsThere'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".
Most systems don't have this extension installed.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With