MySQL sleep() command injection attacks: how not validating your PHP user input can lead to Denial of Service (DoS) attacks against websites and back-end database servers. Simply by putting "AND sleep(3)" in the address bar... Here is how to put a MySQL server to sleep, happy SQL injection!
Investigating PHP/MySQL sleep() attacks - SQL Injection
The other day I noticed several hung queries (SELECT statements) on one of the MySQL database servers under my control. All hung queries had in common they were running for a very long time. And, mysqladmin processlist -v showed a sleep() command in the query.
Given the casing of the MySQL sleep command ("SLeeP"), this was obviously done by an sql injection tool of some kind. I could simply kill the MySQL queries and threads and be done with it, but I wanted to be sure this MySQL sleep() attack couldn't happen again.
After killing the MySQL threads I took a quick look at the website. Given I had both the executed query and website HTTP log files available for my investigation, I quickly located the vulnerable PHP script.
The vulnerable line of PHP code was:
$id = ( isset( $_GET["id"] ) ? $_GET["id"] : 0 );
Where $id was directly used in a MySQL query.
Did you notice the lack of input validation? As long as $_GET["id"] is provided in the URL, it's not set to 0. Through my browsers address bar I can add %20AND%20sleep(3) to the URL, to make this query hung, on the server for quite some time.
The MySQL sleep(3) command is executed for every record the query finds. The website's PHP code doesn't use prepared statements, I checked.
Because it's not my PHP code or website, I informed the creator about this vulnerability in his code, and the lack of input validation. Until it's fixed, I'll keep a close watch on this website and database. It may not surprise you that I find more and more of these SQL injection and MySQL sleep() attacks lately.
Securing the vulnerable PHP code
In the piece of PHP code above, $id is easily made more robust and secure by adding (int):
(int)$id = ( isset( $_GET["id"] ) ? $_GET["id"] : 0 );
This will make PHP to cast the input $_GET["id"] value into an integer, this is called Type Juggling or Type Casting. The name of the desired type is written in parentheses before the variable which is to be cast. Of course the customer using this code needs to update the PHP functions to use MySQLi or PDO, e.g. migrate from mysql_connect to mysqli_connect.
And further is the use of Prepared Statements and/or Stored Procedures important.
Kill multiple MySQL threads at once
If you need to kill multiple connection threads in MySQL, you can use the following command to generated a comma separated list of connection id's and kill them with mysqladmin:
mysqladmin processlist | grep database_name | cut -d '|' -f 2 | xargs | tr ' ' ','
mysqladmin kill [your comma separated list of Id's: 1, 2, 3, 6, 77]
This uses mysqladmin and some bash commands to built a comma separated list of connection ID's, that you can copy and paste into mysqladmin kill.
Or you can kill all threads using mysql's command interface and a query:
MariaDB [(none)]> select concat('KILL ',id,';') from information_schema.processlist where user='user';+------------------------+| concat('KILL ',id,';') |+------------------------+| KILL 3763; |+------------------------+1 row in set (0.00 sec)MariaDB [(none)]> select concat('KILL ',id,';') from information_schema.processlist where user='user' into outfile '/tmp/a.txt';Query OK, 1 rows affected (0.00 sec)MariaDB [(none)]> source /tmp/a.txt;Query OK, 0 rows affected (0.00 sec)-- kill queries running longer than 600 seconds:-- select group_concat(concat('KILL ',id,';') separator ' ') from information_schema.processlist where Time > 600 order by Time ASC;
(thank you mysqlperformanceblog.com for this query)
MySQL sleep() injection attacks, the conclusion
This post showed you the importance of validating user supplied input. The lack of input validation (Dutch article) not only makes your website vulnerable to SQL injection attacks (Dutch article) or Cross Site Scripting (XSS - Dutch article), but may also make your web server and/or MySQL database server unresponsive due to these MySQL sleep() command injections.
This'll disrupt the service, not only for your website, but for all users on the same web server and MySQL database server. Especially when a new MySQL vulnerability is found that crashes the MySQL service, like MySQL DoS in the Procedure Analyse Function - CVE-2015-4870.
You wouldn't want to be the one who crashed aan entire database server just because you didn't validate user supplied input, now would you?