what's the meaning of 'admin' OR 1=1 -- '

Question

The following query return all the passwords in the table tbl_user but I can not understand why this is happening.

SELECT password FROM tbl_users WHERE name = 'admin' OR 1=1 -- '

Please help me to understand this part of the query: 'admin' OR 1=1 -- '

Can you introduce other threats like this (website, book, etc)?

Answer 1

This is a classic SQL injection.

See this fiddle while I explain it: SQLfiddle

In this example, there are 5 users being added to the table. Your query is then run. The expected result would be to return the password value for the admin user only.

However, by adding 1=1, which is a true statement, all passwords are returned.

Another way to help visualize this, is to add parenthesis so that you can see how everything is evaluated.

SELECT pass FROM users WHERE (user_name = 'admin')              OR (1=1) -- '
                                 ^ Pulls only the admin user        ^ Pulls everything because 1=1

So, we are selecting the password from the table where the user name is admin. We are also pulling the password from the table where ever 1=1 - which is always true. Each row is evaluated to true, thus all passwords are returned.

The final -- ' is used to comment out the rest of your query.

SELECT pass from users WHERE user_name = 'admin' or (1=1) -- 'and permission='superadmin'

Normally, (if the 1=1 hadn't been injected), you'd pull the password for the user with user_name of admin and superadmin permissions. You've now commented that out, and it isn't executed. This is how the entire table of passwords can be returned.

Answer 2

The result of a logical OR is as following:

a     | b     | a OR b
-----------------------
false | false | false
false | true  | true
true  | false | true
true  | true  | true

The result of a OR b evaluates to true, if one of the operands is true.

1 = 1 evaluates to true

=>

(any expression) OR 1 = 1 evaluates to true

=>

name = 'admin' OR 1 = 1 

evaluates to true for every row of your table

Result:

SELECT password FROM tbl_users WHERE name = 'admin' OR 1=1 -- '

will return the passwords for all users, not only for admin, because as stated by PeeHaa

--

is the begin of a sql comment.

Answer 3

There are 2 possible confusions I can imagine you experiencing here. The first is, as others have mentioned, expr1 OR expr2 returns true whenever either expr1 or expr2 is true. Since 1=1 is always true, your WHERE statement will be true for every record in the table.

The second thing you might be confused about is that last -- ' in the query. The -- is the SQL equivalent of // in PHP; it indicates that the rest of the line is a comment and should be ignored. So the SQL interpreter is only reading SELECT password FROM tbl_users WHERE name = 'admin' OR 1=1 and ignoring the rest of the line, which is why that trailing single quote isn't causing a syntax error.

The only security risk here is if you are passing unescaped user input to SQL. Always escape any user input with mysqli_real_escape_string or an equivalent function before using it in an SQL query.

Edit: As pointed out in the comments, parameterized queries are generally a better practice than escaping each input element manually. PHP's PDO extension is a good place to start with this approach.

Answer 4

The last part -- ' is comment, so MySQL doesn't care. So we have no left

SELECT password FROM tbl_users WHERE name = 'admin' OR 1=1

In this query 1=1 is true because 1 is the same is 1. It could be here of course another true expressions as for example 2=2 or 'a'='a' - the result will be always the same.

So your query could look like this:

SELECT password FROM tbl_users WHERE name = 'admin' OR true

Operator OR works this way that if any of conditions is true it means that the whole expression is true. In expression name = 'admin' OR true one expression is true (true is true) so this whole expressions is true

So the query now could be

SELECT password FROM tbl_users WHERE true

Now if we look at WHERE part we have WHERE true. It means de facto there is no condition, so we can change query into:

SELECT password FROM tbl_users

So as you see your query simple get column password for each records in your table (probably for all users)

Answer 5

I think you are looking for 'AND' instead of 'OR'

'OR' means that one of the conditions (WHERE name = 'admin' OR 1=1) must be true, and in this case, it is returning everything that has name equal to 'admin', or that has 1 equal to 1.

Try using this instead:

SELECT password FROM tbl_users WHERE name = 'admin' AND 1=1