A Very SLEEPy MySQL Attack
This article dissects a common MySQL attack vector used in the wild. SLEEP attacks are commonly used to to identify vulnerable MySQL systems or codebases reliant on MySQL. The role of utilities like sqlmap to automatically find vulnerabilities and the absurdity of the SLEEP function (and its associated functions) not requiring privileged permissions is discussed in depth.
Updated 2018–12–02: Added case examples for failures even while using PDO and the issue with assumptions about DROP/DELETE/INSERT/UPDATE permissions. Specifically called out the problem with copy/pasting Google-based solutions.
There comes a time in every sysadmin/DBA/programmer’s life when they review their slow-query-log file and see something like this:
WHERE col.id = (select(0)from(select(sleep(90)))v)/*'+(select(0)from(select(sleep(90)))v)+'"+(select(0)from(select(sleep(90)))v)+"*/;Usually this is about 10–15 minutes after they’ve started receiving reports that their website/product/API is down.
Because someone made it go to bed early: welcome to the terrifying world of SLEEP().
What Does SLEEP() Do?
SLEEP(n) pauses the database for a given number of n seconds.
Not to be confused for the Sleep status in the MySQL console, which describes connection statuses, the SLEEP() function does one thing and does it very well: it keeps things from happening until its timer expires.
What good is it? What’s it for?
A few things! It’s good for purposeful delays (perhaps you’re waiting for locks to expire before doing something like backups?) and hacking.
Wait, “Hacking,” you said?
Yes, hacking.
Scanners Gonna Scan
Scanning websites for vulnerabilities is a trivial exercise in today’s time if the administration team hasn’t put safeties in place.
An excellent tool for security auditing (and hacking by extension) is sqlmap.
This program automatically identifies various endpoints on a website that might be using a MySQL connection underneath the hood and throws garbage, fuzzing data, and SLEEP commands at them like there’s no tomorrow.
The output from these scans often end up, especially in PHP/MySQL projects using the old mysql or mysqli connectors instead of PDO, enumerating a list of vulnerable AJAX commands or GET/POST results.
But how is SLEEP() involved?
By forcing the database to sleep on a public-facing website for a given number of seconds (each number being a unique roll of the dice) sqlmap and its cousins can hit dozens of endpoints at once and if/once the database freezes up count how long its out of action to figure out which of the many commands it tried actually worked.
A Quick Dissection
The example posted at the top of the article is a common enough one that it doesn’t represent a “new threat.” It still works though.
If you execute a SELECT statement where the conditional statement has been replaced with something like the following, then it will absolutely lock up the database:
WHERE col.id = (select(0)from(select(sleep(90)))v)/*'+(select(0)from(select(sleep(90)))v)+'"+(select(0)from(select(sleep(90)))v)+"*/;Notice the clever use of the various operators and delimiters: these are designed to fool custom sanitization routines that programmers relied on rather than a proper PDO (I’m picking on PHP because its easily the the easiest web language to write unsafe MySQL integrations with).
I’ve even seen code that, while not PDO, used at least the mysqli connector instead of the old deprecated mysql but completely forgot to use mysqli_real_escape_string on incoming data from the client.
A Bad PHP Example
Something like this, for instance, is a common enough pattern it deserves recognition for just how easily it can ruin your project:
function lookupProductsByID($product_ids) {
global $conn;
$q = "SELECT * FROM product WHERE id IN (" . implode(',', $product_ids) .")";
$result = mysqli_query($q);...
In the above example a programmer is using the implode() function to create a comma delimited list of IDs.
Note that NO validation was done on the input: that incoming array could be full of SLEEP commands or an instruction that ended up dumping the database table schemas to the browser window. It could inject data into a column used to render the UX, like a cross-site-script.
In this case it would’ve been infinitely better to at least iterate over the elements of the array and validate them. Were you expecting int values? Then use intval() and force each element to prove its int-eyness.
Or use PDO and bind the values which will auto-sanitize and prevent SQL injections.
So PDO Solves Everything?
No. PDO doesn’t solve everything.
User ivanhoe on Hacker News brings up an excellent point that dovetails nicely with the previous section on bad usage of implode().
Prepared statements, accessible for PHP/MySQL using the PDO module, auto-sanitize input through the bindValue() and bindParam() functions (or using a list of ? tokens).
It’s the dynamic composition that puts most naive PHP codebases at risk as mentioned before.
The problem with new generations of web developers is that inevitably they’ll look online for resources to help them out.
Here’s a terrifying example of something that no doubt gets copy/pasted directly into public-facing web-code:
This code written above is one of the top Google results for PDO tutorials and is utterly dangerous. Rather than a lesson about how PDO can be used for the IN() clause, it just “gets the job done at any cost” and avoids the point of PDO altogether.
There’s no function difference in the code above to using the mysql or mysqli connectors.
Now, on the other hand, examine this:
User Your Common Sense on StackOverflow provides an answer that, while still using dynamic construction, using the parameterized question mark tokens and the data as an array pass to execute() to take advantage of PDO’s purpose.
Too bad his dogged corrections of other peoples’ answers got him run off the site!
At Least It’s Just SLEEP( ) and not DROP
One argument I’ve seen is that its better to get the canary-style heads up that someone is attacking your database with SLEEP() instead of waking up to find all your tables missing because of DROP.
To a point I agree, SLEEP() is easy to identify and in the first phases often less-harmful. However, since it can be used in a hundred queries with fuzzing data and the attacker need only count how long the connection paused to figure out which attack vector worked it makes parallelizing vulnerability discovery trivial.
Indeed, this is a core function of sqlmap.
The other issue with this line of thinking is that in production web projects that rely on MySQL for UX/UI the right thing to do is restrict the code to using a user account on the database that only has SELECT permissions: no DROP.
But SLEEP() works using SELECT permissions.
So SLEEP is Public?
Yes, in the sense that the SLEEP() function can be used with SELECT and DO and doesn’t require any privileged permission to use, yes, it’s as public and valid to execute as the “safest” MySQL commands.
It’s only a single SQL injection vulnerabilities away from being able to pause your database execution or discover data (or exfiltrate it) using timing attacks.
In fact, you can’t even disable it without recompiling.
Harkening back to my discussion about Apache and its “less-secure-by-default” approach: why do vanilla MySQL packages come with SLEEP() even enabled? Why not make it an option?
Or, better yet, make it possible to permiss.
The fact that MySQL can handle permissions at the database, table, and column level is excellent. What if we pushed for the ability to handle permissions at the function level as well?
Summary
- MySQL vulnerabilities are simple to enumerate using the
SLEEP()function - Custom PHP/MySQL codebases are soft targets, especially when not using bound values/parameters with PDO
SLEEP()is very rarely used for legitimate purposes- Why not make it optional?
- Or, better yet, improve the permissions for dangerous functions like it and its friends.
