SQL Injection with MySQL SLEEP()

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.

Based on the picture, let’s see a few examples.
We have a simple table:

[sql] 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)
[/sql]

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

[sql] 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)
[/sql]

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

[sql] 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>
[/sql]

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

[sql] 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)
[/sql]

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: https://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?

 

Want to talk with an expert? Schedule a call with our team to get the conversation started.