Security

MySQL sleep() attacks

Sharing is caring!

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… Happy SQL injection!

Investigating PHP/MySQL sleep() attacks

The other day I noticed several hung queries (SELECT statements) on one of the MySQL database servers I administer. 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.

This may interest you:   How to filter web traffic with blacklists

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. Also, the use of Prepared Statements and/or Stored Procedures is important.

Kill multiple MySQL threads at once

Pro Tip: 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)

(thank you mysqlperformanceblog.com for this query)

This may interest you:   "WordPress Plugin Social Media Widget Hiding Spam - Remove it now"

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 (Dutch article) or Cross Site Scripting (XSS – Dutch article), but may also make your web server and/or MySQL database server unresponsive due to 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?

5 comments

    1. Jan Reilink ( User Karma: 0 ) says:

      Hi Idham,
      Like it says in the text: “The MySQL sleep(3) command is executed for every record the query finds”. So, for every record found, MySQL has to sleep 3 seconds. This can easily exhaust all available connections to a database server because of the number of hung queries.

  1. Phil says:

    Hi, i was wandering how i would make my website show me that URL so i can check for the sleep thing. im using wordpress and it keeps giving me a database error. I’ve limited max connections and memory usage and disabled all plugins but i keep getting the error. I’ve done the checking my DB credentials and repairing my database.

    I use the pretty URLs on wordpress so i dont know if that is why i cant find the URL to check it.

    Thanks!

    Phil

Leave a Reply

Your email address will not be published.


*

This site uses Akismet to reduce spam. Learn how your comment data is processed.