Why this sql is correct? (sql injection)

Question

What does it mean?

SELECT * from users where password = ''*'';

if I check this in mysql workbench I get only one line, although I have lot of users in table.

What exactly does this select?

Answer 1

Interesting question. Let's see what ''*'' does.

mysql> select ''*'';
+-------+
| ''*'' |
+-------+
|     0 |
+-------+

Let's create some users:

mysql> select * from users;
+------+-------+
| id   | name  |
+------+-------+
|    1 | joe   |
|    2 | moe   |
|    3 | shmoe |
|    4 | 4four |
+------+-------+

And test our query:

mysql> select * from users where name = ''*'';
+------+-------+
| id   | name  |
+------+-------+
|    1 | joe   |
|    2 | moe   |
|    3 | shmoe |
+------+-------+

Interestingly enough, user 4 was not selected! But let's try this way:

mysql> select * from users where name = 4;
+------+-------+
| id   | name  |
+------+-------+
|    4 | 4four |
+------+-------+

So, what can we deduct from this?

1. ''*'' somehow means 0 (I am not that fluent in mysql string operators, so let's take it as a fact);
2. MySQL, apparently, does type conversions in this case. So if you query a varchar column against an integer, it tries to convert those strings to ints and see if it's a match;
3. You have only one row whose password begins with 0 or non-digit.