MySQL Security – How to Prevent SLEEP Injection

MySQL

I was wondering if someone could help to understand this query, basically I'm aware about SLEEP() function but I've never seen this implementation before.
Why there are no any results returned?

mysql> SELECT fname, lname FROM contacts WHERE fname = 'test' / SLEEP(1) / '';
Empty set, 14 warnings (7.00 sec)

Warnings:

Warning | 1292 | Truncated incorrect DOUBLE value: 'test'
...

Best Answer

No results are returned because, unsurprisingly, there are no records that meet the conditions of the WHERE clause. SLEEP() will return 0 (if not interrupted), and all operands will be cast as doubles for the division operation (hence the warning about truncated incorrect double). In essence, the query is the same as

SELECT fname, lname FROM contacts WHERE fname = 0 / 0 / 0;

Division by zero results in null, so you're basically looking for records where fname = NULL, which is never true.

Related Solutions

Mysql – How long is “too long” for MySQL Connections to sleep

mysqld will timeout database connections based on two server options:

Both are 28,800 seconds (8 hours) by default.

You can set these options in /etc/my.cnf

If your connections are persistent (opened via mysql_pconnect) you could lower these numbers to something reasonable like 600 (10 minutes) or even 60 (1 minute). Or, if your app works just fine, you can leave the default. This is up to you.

You must set these as follows in my.cnf (takes effect after mysqld is restarted):

[mysqld]
interactive_timeout=180
wait_timeout=180

If you do not want to restart mysql, then run these two commands:

SET GLOBAL interactive_timeout = 180;
SET GLOBAL wait_timeout = 180;

This will not close the connections already open. This will cause new connections to close in 180 seconds.

MySQL: many Sleep processes

To me, it seems like an application connecting using the 'mastersuser' account is not properly closing connections, so yes I'd say it's a problem. They likely disappear after reaching the wait_timeout or interactive_timeout setting.

Best Answer

Related Solutions

Mysql – How long is “too long” for MySQL Connections to sleep

MySQL: many Sleep processes

Related Question