Can I configure MySQL's typecasting to consider 0 != 'foo'?

Question

This article points out the following security problem with MySQL:

USERS
id | email            | password_reset_token
1  | [email protected]  | QTlXww)uV!Hdg3U6aGwKV2FPvAqzVgPx
2  | [email protected]  | CZor5t7WbX#LTeqiG3v@6f3@z#)BfK*n

Here we have nice, random tokens that a user must prove they have in order to reset their account. But the user manages to submit a reset token of 0, so we run this query:

SELECT * FROM `users` WHERE `email` = '[email protected]' AND `password_reset_token` = 0

MySQL converts the token VARCHAR to an INT in order to make the comparison. It considers a VARCHAR that does not start with a number to equal 0. Therefore, an attacker can match any non-numeric string and take over the account.

Even if the reset token starts some numbers, the situation is not much better. MySQL ignores all characters but the initial numbers when making this comparison, so it considers 12blahblah3blah4 to be equal to 12, which makes guessing exponentially easier.

Can I configure MySQL not to do this kind of typecasting? If, for example, it cast INT to VARCHAR instead of vice versa, this attack would not work.

Note

If the query is run with '0' instead of 0, this doesn't work. The article discusses this vulnerability in terms of Ruby on Rails' acceptance of XML, where a type=integer property convinced Rails to send an actual integer in the query.

That bug has been patched in Rails; it now converts all request parameters to strings, so it would never build a query with an integer. But I still think MySQL should be configurable to prevent this.

Answer 1

The problem, though, is that you are supplying a numeric value, unquoted, so MySQL tries to cast the other side of the comparison as a DOUBLE, to compare it with the numeric argument you've supplied... which leaves you with a 0 that's equivalent to 0.

mysql> select 'foo' = 0;
+-----------+
| 'foo' = 0 |
+-----------+
|         1 |
+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

If you supply the 0 in a string context, this evaluates to false.

mysql> select 'foo' = '0';
+-------------+
| 'foo' = '0' |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

So one option you could use to avoid this behavior would be by casting your input value as a CHAR... which, most of the time, it already is... but if not, it evaluates as you would expect.

mysql> select 'foo' = cast(0 as char);
+-------------------------+
| 'foo' = cast(0 as char) |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

---

update, to more fully answer the question:

Can I configure MySQL not to do this kind of typecasting?

No, there does not appear to be any way to avoid this through configuration -- only through an explicit cast of your potentially-numeric-looking string -- to a string, which you've said can't be accomplished since you're using an ORM. The most likely place, if something like this existed, would be SQL Server Mode, and none of those options modify this behavior when it comes to comparisons.

There's a little bit of discussion of implicit conversions like this in Bug #63112:

SELECT orange > banana;

Is this true or false? Well .. most often a banana is longer than an orange. So if lenght is the criteria the above statement is false. But most often an orange is heavier than a banana, so if weight is the criteria the above statement is true.

When comparing different types (orange versus banana, string versus integer) some 'criteria' or 'rule' will have to be used. When MySQL compares different datatypes they are [both] cast to DOUBLE internally before comparison. The integer "1" casts to the double "1" - the string 'a' or 'UUID()' casts to the double "0". [...]

Note that the scope of this bug report is not strictly limited to implicit casting in out context, and the mention of UUID() here is a bit of a distraction, since UUID() can return strings beginning with digits and cast to other numbers... but I think it does serve to confirm that no alternative exits, since nothing is presented other than "don't use different types for comparison if you want to avoid a cast," which we already know. It continues:

To avoid casting to DOUBLE internally ensure that the values compared are same datatype. In your case the id columns are strings and the value you compare with should also be strings (not 1 but '1' - not 2 but '2' etc.) or use cast()/convert() in your statement [...]

Regarding your suggestion that if implicit casting is to be done, then the number should become a string instead of the string becoming a number... I can see your point, to a point, for strict equality comparisons... but there are many other operators besides = and using that logic, a whole new series of problems arises...

mysql> SELECT CAST(100 AS CHAR) < '2';
+-------------------------+
| CAST(100 AS CHAR) < '2' |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

Correct! The string '100' is "less than" the string '2' in an alphabetical sense... in the same way the string 'AXX' is "less than" the string 'B'. So this alternative certainly has its down-sides.

There is, however, a third alternative, which seems like the most accurate logic (though I welcome a constructive objection): If a string that begins with a non-digit character is cast to a DOUBLE (or any other numeric type, for that matter), it seems like the most correct result of that cast would be NULL.

Unfortunately, this isn't the MySQL approach. It seems like this could be fixed in the source, possibly in sql/field.cc where the '0' returned by my_strntod() appears to be simply returned to the caller after throwing the warning if an error occurred, but attempting such a hack this deep in the source is beyond my expertise.

Answer 2

First, if your end query was being generated like that it seems to imply your taking straight user input and pasting it into a query template which itself is the problem for sql injection. Further, even if you were doing that the query template would need to be in quotes for legit requests of charish inputs to even be syntactically valid.

What ever language your application is using, ruby or otherwise, look up how they do parameterized queries and prepared statements. The API will handle all the proper escaping for you.