MySQL Bugs: #39337: MySQL syntax allows direct comparison of strings in WHERE clause

Bug #39337	MySQL syntax allows direct comparison of strings in WHERE clause
Submitted:	9 Sep 2008 10:27	Modified:	9 Sep 2008 17:30
Reporter:	Johannes Dahse	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	direct comparison WHERE

Description:
MySQL allows a direct comparison of strings in a WHERE clause. This can abused by attackers using SQL Injection to trigger an authentication bypass without using an OR operator or similar well known techniques which usually gets detected by filters.

How to repeat:
SELECT * FROM users WHERE username = 'string'='string';
SELECT * FROM users WHERE username = ''='' and password = ''='';

This successfully bypasses authentication (returns true). I am aware that SQL injection is not a problem of MySQL itself, this example should only demonstrate how this can be abused. actually a direct comparison of 2 strings should not be possible.

Many thanks for the hint but to intercept injections is not a job of SQL parser it is a job of the SQL users.

hi, thanks for the quick response. that was just an example for abusing. the actual bug is, that MySQL allows more than one comparison like

select * from users where 1=1=1
select * from users where id=id=1
select * from users where 'a'='b'='c'

and always returns true. I think thats not intended.

Hi! I understand that this behaviour may seem illogical. However, it's not a bug.

The expression 'a'='b'='c' is parsed thus:

('a'='b') = 'c'

well, clearly, 'a'='b' is not true but false.
Now, MySQL does not have a proper boolean type: Instead, TRUE is exactly equal to 1 and FALSE is exactly equal to 0. So, what happens is that

'a'='b' is in fact completely equal to 0.

Now what remains is this expression:

0 = 'c'

At a glance we'd say this is false (and hence, 0). However, the = cannot compare two values of different types directly. So you get an implicit conversion of 'c' to a numeric type that can be compared to the 0 on the left hand side of the =. 

The tricky thing is that 'c' cannot be parsed into a number, and gets converted to the nearest thing, which happens to be 0. 

(You will actually get a warning though: try SELECT 0='c', or SELECT 'a'='b'='c' and then do a SHOW WARNINGS)

So 0 = 'c' becomes 0 = 0, which is obviously true.

Now, you mentioned that the underlying problem is that MySQL allows 'chaining' the =. However, I don't think that is the problem. Personally, I think the problem is the silent conversion from 'c' to 0. IMO, it should be possible to configure the server to either see that as an error (yet another sql_mode setting), or to have the numeric representation evaluate to NULL instead of 0

Another possibility would be for the server to use a true boolean type in computation. Then

'a'='b'='c' would be

FALSE = 'c'

A reasonable implicit conversion from 'c' to a boolean type would be TRUE (and the empty string would be FALSE) which would then give

FALSE = TRUE, which is obviously FALSE.

I hope this helps.

very good explanation, now this makes sense. thank you!

So, it seems to be a bug but it is not. Anyway I think that it's unclear. I have a suggetion. PHP (which is often used with MySQL) does not allow an expression like:

'a'=='b'=='c'

It allows:

'a'==('b'=='c') // returns 0, like in mysql
''==('b'=='c') // returns 1, like in mysql

Second example may seem weird, but it's made clear by (). So, it has the same behavior as MySQL... but the result is clear. Maybe you would like to disallow unclear expressions, too.