SQL injection with AND 1=1

Question

Please explain the meaning of "AND 1 = 1" in a SQL injection attack. It's from an exercise in my university. E.g.

select * from user where id = 'smith'' AND 1=1;--  and birthdate = 1970;

or

select * from user where id = 'smith' and birthdate = 1970 AND 1=1;--;

Answer 1

The specific question is SQL injection with AND 1=1 and not OR 1=1. There is a big difference here in what the OP is asking about. If I were your teacher and you provided me with JonathanMueller's answer you would get a lousy grade as you dont understand the question.

AND 1=1 is usally used in blind SQL injections. This is when you have to determine between a true or false state from the result of the application to make out what the actual result is. You don't get data listed out in the result, the only thing returned is a state of change.

If you try to exploit a Blind SQL injection with OR 1=1 you will fail because the primary use of OR 1=1 is to create an always true statement in order to get the most data out of the database or to force a true statement in the case of a login script being exploited.

A practical blind SQL exploit example:

We use our example: http://www.site.com/news.php?id=7 Let’s test it: http://www.site.com/news.php?id=7 and 1=1 <--- this is always true and the page loads normally, that's ok.

http://www.site.com/news.php?id=7 and 1=2 <--- this is false, so if some text, picture or some content is missing on returned page then that site is vulnerable to blind sql injection.

Another example where you try to figure out MySQL version:

http://www.site.com/news.php?id=7 and substring(@@version,1,1)=4 This should return TRUE if the version of MySQL is 4. Replace 4 with 5, and if query return TRUE then the version is 5.

Examples taken from: http://www.exploit-db.com/download_pdf/14475

Answer 2

Normally, you would see the phrase being "OR 1=1'". The reason is, if the programmer doesn't properly handle parameters (like user id), then an attacker could use an OR 1=1 to return all data in the table.

For example, say we want to get details about a user named smith. The query could be select * from user where id = '?', where the question mark gets replaced with a parameter from a user's input. If the attacker passed smith' OR 1=1--, the resulting SQL would be select * from user where id = 'smith' OR 1=1--'. In this case the second single quote from the original query is skipped because it is part of a comment, and the query would return all data in the user table.

Answer 3

To give a (somewhat trivial, off top of head) example of sql injection, imagine this query somewhere in your app code ...

select 1 from user where name='[NAME]' and password='[PASS]'

after Malicious Guy sets named parameters above to his choosing, you get:

select 1 from user where name='admin' --' and password='[PASS]'

using the following chosen, unsanitized values:
[NAME]=' --' and password='[PASS]'
value of [PASS] variable is unimportant

Thus, assuming the query is meant to return 1 for success and empty set for invalid user or pass, a 1 would be erroneously returned, allowing this evil user to log in as the "admin" account.