Search code examples
mysqlsqlsql-injection

Why this sql is correct? (sql injection)

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?

Solution

  • 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.