Why does ""=" exploit this MySQL Query?

Question

On a MySQL 5.6 database, I build this simple table and insert a row:

CREATE TABLE `users` ( 
`username` varchar(64) DEFAULT NULL, 
`password` varchar(64) DEFAULT NULL 
);

INSERT INTO users VALUES ('bob', 'pass');

Then I set up a query in PHP like so:

$query = "SELECT * from users where username=\"".$username."\" and password=\"".$password."\"";

When $username and $password are both equal to ""=", the resulting query is SELECT * from users where username="""="" and password="""="". When that's used to query the table set up before, the row in the table is returned.

The question is, how is MySQL evaluating that query such that it considers the query valid and that the WHERE statement is true? Assuming all double-quotes are matched with the nearest untaken adjacent double-quote, I would have expected the query to be interpreted something like this, which looks like it should be considered gibberish:

SELECT * from users where username=""
"="
" and password="
""
=
""

Here's an example of this behavior on a MySQL 5.6 DB: http://sqlfiddle.com/#!9/02e606/2

Answer 1

It's because MySQL allows "" as an alternative for \".

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

In your specific case:

SELECT * from users where username="""="" and password="""=""

would be the same as (if I'm parsing this correctly in my head):

SELECT * from users where (username='"="" and passsword="') = ""

A three-way equality test IS syntactically correct, but does not evaluate as expected

mysql> select 'a' = 'a' = 'a';
+-----------------+
| 'a' = 'a' = 'a' |
+-----------------+
|               0 |
+-----------------+

because that parses as (a=a)=a -> true=a -> false

-- comment follow up for @juan:

mysql> select 'a'='a'='a', 'a'='a'='b', 'a'='b'='a', 'b'='a'='a', 'b'='b'='a';
+-------------+-------------+-------------+-------------+-------------+
| 'a'='a'='a' | 'a'='a'='b' | 'a'='b'='a' | 'b'='a'='a' | 'b'='b'='a' |
+-------------+-------------+-------------+-------------+-------------+
|           0 |           0 |           1 |           1 |           0 |
+-------------+-------------+-------------+-------------+-------------+

It's non-intuitive, because

mysql> select 'a'=('a'='b'), ('a'='a')='b', true='b', 'a'=false;
+---------------+---------------+----------+-----------+
| 'a'=('a'='b') | ('a'='a')='b' | true='b' | 'a'=false |
+---------------+---------------+----------+-----------+
|             1 |             0 |        0 |         1 |
+---------------+---------------+----------+-----------+

--- followup to the followup: again, your original query:

SELECT * from users where username="""="" and password="""=""

will run as

SELECT * from users where (username='"="" and passsword="') = ""
SELECT * from users where (false) = ""
SELECT * from users where true

because false ="" in mysql evaluates to TRUE, therefore ALL rows get included, unless you have a user whose username is literally "="" and password=".

Answer 2

I guess you are doing something like this

Sql Demo

SELECT """="" and password="""=""   -- this is equal to 0
from users 
where (username = "anything") = false;