Why does SELECT 'a'='b'='c' return 1 in MYSQL?

Question

I was doing some homework for my security class involving SQL injections. I found that I could do a shorter SQL injection than the typical ' OR '1'=1 example. Instead I could do '='. Typing this is in to the password field of a typical login boxes gives a SQL query like this:

SELECT * FROM Users WHERE username='user' AND password=''='';

It turns out that password=''='' evaluates to 1, allowing the SQL injection to work.

After doing some more testing, I saw that if I test if a string is equal to 0, it returns 1:

SELECT 0='a';

So in my example, password='' would evaluate to 0 and 0='' would end up evaluating to 1.

My testing showed me how this is happening, but I want know why this happens (i.e why is 0='a' true?.

Answer 1

As documented under Type Conversion in Expression Evaluation, comparisons between a string and an integer will be performed numerically:

• In all other cases, the arguments are compared as floating-point (real) numbers.

Therefore, the operands are converted to floating-point numbers and then compared.

Conversion of a string to a float will consider every numeric character (and the first period or exponentiation character) encountered up to the first non-numeric character. Therefore 'hello' or 'a' will be truncated to '' (and thereby cast to zero) whereas '123.45e6foo789' would truncate to '123.45e6' (and thereby cast to 123,450,000).

Thus one can see how 0='a' is true: it is compared as 0=0.

That password=''='' is true (provided that password is a non-empty string, or a non-zero numeric) comes about because the first comparison results in zero (false), which forces the second comparison to be performed numerically (thus converting '' to zero for comparison with the zero result of the first comparison).