Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

This article points out the following security problem with MySQL:

id | email            | password_reset_token
1  |  | QTlXww)uV!Hdg3U6aGwKV2FPvAqzVgPx
2  |  | 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` = '' 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.


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.

share|improve this question
One workaround would be to use an extremely long numeric string for the token, but that shouldn't be necessary and vastly reduces the key space. – Nathan Long Mar 6 '13 at 14:56
This is a Rails question then. The problem is not with MySQL but the bugs in Rails. Why don't you simply issue a direct select query? – Pacerier Jan 31 '15 at 8:49
up vote 9 down vote accepted

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/ 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.

share|improve this answer
Right. But MySQL could do one of two things before comparing: cast the string to a double, or cast the double to a string. If it does the former, every string in the world that doesn't start with a number will be considered equal to 0. If it does the latter, only '0' will be considered equal to 0. I'm saying the latter is more sensible behavior and asking if I can configure MySQL to do it. Modifying all my queries to cast that way is not an option because most will be dynamically generated by an ORM. – Nathan Long Mar 7 '13 at 19:04
I have updated my answer to address this alternative and to more fully answer the question overall. – Michael - sqlbot Mar 8 '13 at 1:43
Thanks for addressing my suggestion. Seems like the safest strategy is what PostgreSQL does: require explicit casting before any comparison. – Nathan Long May 30 '14 at 13:31
@NathanLong, You can do this natively in MySQL by checking for errors and warnings after each query. As long as you get a single error/warning, it indicates that something unexpected occured. You stop processing and return an error message. – Pacerier Jan 31 '15 at 8:54

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.

share|improve this answer
I agree that "don't send such a query to the database" is wise advice. However, "foo" is not the same as 0, and I want MySQL to recognize that. I think the db is fundamentally doing the wrong thing here. – Nathan Long Mar 6 '13 at 17:07
@NathanLong, It does recognize it, otherwise you wouldn't get a warning. – Pacerier Jan 31 '15 at 8:55

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.