This article points out the following security problem with MySQL:
USERS
id | email | password_reset_token
1 | one@example.com | QTlXww)uV!Hdg3U6aGwKV2FPvAqzVgPx
2 | two@example.com | 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` = 'one@example.com' 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.
Best Answer
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.If you supply the 0 in a string context, this evaluates to false.
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.update, to more fully answer the question:
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:
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, sinceUUID()
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: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...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.