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)?

Parenteau

That is a classical SQL injection. It returns the users where the name is admin OR where 1=1 (since 1 is indeed 1 every record will match). The -- just makes sure eveyrthing after it is seen as a comment – Expeditious

You can remove 1=1 as it does nothing – Holst

@Holst what do you mean? – Expeditious

1 always equals 1 so what's the point of writing it.. if it was id = 1 that would make more sense as he would be looking through it – Holst

That's the entire point @Gadgetster. It certainly does something. ;-) – Expeditious

@Holst it does quite the opposite of nothing – Canoe

Adding an OR True to the end of SQL is a way of ensuring a response comes back from some flavors of SQL servers, even if there are no records found. Commonly it would be used in a loop, so step 1 - make that query, step 2 - iterate over the rows. If there are no records returned, the loop never runs. – Lifesaving

@Expeditious thanks a lot, can you tell me what's the meaning of single quote after -- ? – Parenteau

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.

Cahra

Very well put, not only the logical part - I think I did this well enough - but how this exploit changes the intend of a query and how it could be extended. – Phosphaturia

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.

Phosphaturia

but this is the same as SELECT password FROM tbl_users, so for what reason where statement uses at all? – Hirsute

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.

Humid

IMO, it is better to use parameterisation than escaping and concatenation - the latter should only be used for things that cannot be parameterised, like column names. – Walrus

@Walrus I agree, parameterisation is better than escaping + concatenation as a coding practice because it is harder to make mistakes. I'll edit a reference to it into the answer. – Humid

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)

Mauri

Excuse me, could you help me why following query not working? SELECT name FROM list WHERE name = true -- ' – Parenteau

@Daniyal, if you have a problem, please always explain what happens (error? nothing? crash? etc). In this case it is likely that you are comparing a string column with a boolean constant. – Walrus

@Walrus Thanks a lot MR @Marrcin, excuse me, could you help me why following query returned an empty result set? SELECT name FROM list WHERE name = true -- ' – Parenteau

@Daniyal Look at the manual: In SQL, all logical operators evaluate to TRUE, FALSE, or NULL (UNKNOWN). In MySQL, these are implemented as 1 (TRUE), 0 (FALSE), and NULL. So you've got the same as SELECT name FROM list WHERE name = 1 No one has this name, so no result. – Phosphaturia

@Daniyal: that would be an expected result. You have no rows where that column is equal to boolean true, since it is a string. The --' at the end does nothing in this case - in your original post it has the effect of hiding the remainder of the original author's query, but that does not apply here. – Walrus

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

Hagy

The query is indeed not what the original author intended - it is a popular cracking syntax intended to return rows in all circumstances, taking advantage of a SQL injection vulnerability. In that context, the OR form is what the cracker would want. – Walrus

Oh ok, sorry I am new to this stuff, just tried to help out from the little I know. Thanks for telling me. – Hagy

No worries. You'd be right if the 1=1 preceded the rest of the WHERE clause - having one permanent clause with a number of optional ones is popular for query builder libraries. This is because it simplifies the additional syntax - all new clauses are in the form AND (clause). – Walrus