user6765139 user6765139 - 2 months ago 4
MySQL Question

Why does ""=" exploit this MySQL Query?

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

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 |
+---------------+---------------+----------+-----------+