SQL Injection with MySQL SLEEP()

Posted in: MySQL, Technical Track

Recently we’ve received an alert from one of our clients that running threads are high on one of their servers. Once we logged in, we noticed that all the selects were waiting for table level read lock. We scrolled through the process list, and found the selects which were causing the problems. After killing it, everything went back to normal.
At first we couldn’t understand why the query took so long, as it looked like all the others. Then we noticed, that one of the WHERE clauses was strange. There, we found a SLEEP(3) attached with OR to the query. Obviously, this server was the victim of a SQL injection attack.

What is SQL injection?

I think most of us know what SQL injection is, but as a refresher, SQL injection is when someone provides malicious input into WHERE, to run their own statements as well.
Typically this occurs when you ask a user for input, like username, but instead of a real name they give you a MySQL statement that will be run by your server without you knowing it.
Exploits of a Mom
Based on the picture, let’s see a few examples.
We have a simple table:

mysql> describe post;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| test  | varchar(127)     | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from post;
+----+--------+
| id | test   |
+----+--------+
|  1 | text1  |
|  2 | text2  |
|  3 | text3  |
|  4 | text4  |
|  5 | text5  |
|  6 | text6  |
|  7 | text7  |
|  8 | text8  |
|  9 | text9  |
| 10 | text10 |
+----+--------+
10 rows in set (0.00 sec)

Lets run a select with LIKE, which we know for sure won’t have a match:

mysql> select * from post where test like '%nomatch%';
Empty set (0.00 sec)

But what, happens if we don’t filter the inputs and someone wants to get all the data?
mysql> select * from post where test like '%nomatch ' || '1==1' && '1%';
+----+--------+
| id | test   |
+----+--------+
|  1 | text1  |
|  2 | text2  |
|  3 | text3  |
|  4 | text4  |
|  5 | text5  |
|  6 | text6  |
|  7 | text7  |
|  8 | text8  |
|  9 | text9  |
| 10 | text10 |
+----+--------+
10 rows in set, 2 warnings (0.00 sec)

That was a very mild injection, but it could be much more malicious: we could drop another table!

mysql> show tables;
+----------------------+
| Tables_in_injecttest |
+----------------------+
| game                 |
| post                 |
+----------------------+
2 rows in set (0.01 sec)

mysql> select * from post where test like '%nomatch'; drop table game;-- %';
Empty set (0.00 sec)

Query OK, 0 rows affected (0.28 sec)

mysql> show tables;
+-----------------------+
| Tables_in_inject_test |
+-----------------------+
| post                  |
+-----------------------+
1 row in set (0.00 sec)

mysql>

If we don’t know the name of the table, we can still cause trouble by blocking access to the database
If we insert SLEEP() in the WHERE part, then it will be executed for every matching row… if we inject it like: “OR SLEEP(n)”, it will be executed to every row in the table!
Okay, this will be “just” a long running select. It shouldn’t cause much trouble thanks to InnoDB and transaction isolation, unless something needs a table lock.

Some common examples of what causes table locks are:

  • explicit lock table
  • insert/update/delete on MyISAM
  • ALTER table on InnoDB

Once statements start waiting for lock on the table, all proceeding selects will wait for the previous locking statement to finish

Terminal 1:
mysql> select * from post where test like '%nomatch ' OR sleep(300) AND '1%';
….
Terminal 2:
mysql> alter table post engine=innodb;
...
Terminal 3:
mysql> select SQL_NO_CACHE count(*) from post;
…
Processlist:
+----------+----------------------+-----------+--------------------+---------+-------+---------------------------------+-----------------------------------------------------------------------+
| Id       | User                 | Host      | db                 | Command | Time  | State                           | Info                                                                  |
+----------+----------------------+-----------+--------------------+---------+-------+---------------------------------+-----------------------------------------------------------------------+
| 17170817 | root                 | localhost | janitest           | Query   |    19 | User sleep                      | select * from post where test like '%nomatch ' OR sleep(300) AND '1%' |
| 17170918 | root                 | localhost | janitest           | Query   |    11 | Waiting for table metadata lock | alter table post engine=innodb                                        |
| 17170957 | root                 | localhost | janitest           | Query   |     4 | Waiting for table metadata lock | select * from post                                                    |
+----------+----------------------+-----------+--------------------+---------+-------+---------------------------------+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)

As we see in the example, ALTER table will wait until it can get a lock on post table, and this blocks every other select from now on to the table.
Or, if you are using MyISAM table, a simple update/insert will block access to the table, because it needs table level lock during them.

How can we defend ourselves from SQL injection?

There are several ways to secure yourself from SQL injection.

  • First of all, validate the input. If you expect only letters and numbers, filter it with regexp for example, to make sure there are no special characters there. Also escape the inputs on application side; programming languages have built-in function to do that (eg.: mysql_real_escape_string() in PHP)
  • Use prepared statement! It won’t allow 2 clause if you specified only 1. When you use prepared statements, the variables are transmitted as MySQL variables. Even if the string is not escaped, it will end up in one variable, and MySQL treats is as a longer string.
    (For more details see: http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html )
  • Use a tool like MySQL Enterprise Firewall, which is a plugin for MySQL and can filter your statements to make sure there are no things like: || 1==1

I would like to start a little talk about this, so if you encountered SQL injection before, would you share it with us, how they did it, or in general how do you prevent SQL injections in your application?

 

email

Interested in working with Janos? Schedule a tech call.

3 Comments. Leave new

Jan Steinman
March 3, 2016 1:44 am

Unless you are using an interface that allows multiple statements, you are safe from that subset of injection attacks, no? For example, the PHP mysql() interface and the default mysqli() interface does not allow multiple statements, so it would seem queries with a semicolon injected would simply fail.

Of course, using “OR SLEEP()” could still cause you trouble…

Also one defense Janos does not mention is to always make sure the MySQL user has no higher permissions than necessary. Little Bobby Tables could not cause such havoc unless the MySQL user running the query had DROP permission — which it seems would be very rare a need. 99% of database access will only need SELECT permission, and 99% of the remaining 1% should not need DROP permission!

Remember, MySQL “users” are really roles, not actual user logins.

Reply
Federico Razzoli
March 3, 2016 1:30 pm

Unfortunately (or fortunately?), no privilege is required to execute native functions (including SLEEP).

The SLEEP() attack can be dangerous. Think about websites: if SQL is injected into the SQL queries that read data necessary to make the home page appear, and there is no caching system, no user will be able to see the home page for X seconds.

If you don’t trust your applications, IMHO, there is only one way to prevent those attacks: on MariaDB and old MySQL versions, it’s MaxScale; on MySQL 5.7 it’s the query rewriting plugin.

Another way could be only GRANTing the permission to execute stored procedures… but if you can do that, your company has control on the applications, so simpler solutions are possible.

Reply

I’ve written about MySQL sleep() attacks in the wild a few years back, in 2014. You may find my post here: https://www.saotn.org/mysql-sleep-attacks/ . Unfortunately, I still see this a lot. But a new vulerability in MySQL’s Procedure Analysis function opens up a whole new attack vector: if you’re on MySQL <= 5.5.45, you may find your mysqld crashing because of SQL-i attacks exploiting this vulnerability.

Osanda Malith found this vulnerability, that is assigned CVE-2015-4870, and he wrote up an article and Poc at https://osandamalith.wordpress.com/2016/05/29/mysql-dos-in-the-procedure-analyse-function-cve-2015-4870/.

PS: in your first point of protecting against SQL injection, you mention mysql_real_escape_string(). Please note that ext/mysql in PHP is deprecated, and thus mysql_real_escape_string as well. Replace your functions with MySQLi and mysqli_real_escape_string().

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *