The significance of ' or 1=1- in SQL injection.

[akaktsn]

I have been reading about SQL all day. I understand the basic commands such as SELECT, UPDATE, INSERT..etc but can someone please explain the significance of "' or 1=1-" when injecting. Thank you for your time--Aka

[sanddbox]

Let's look at how a web application would usually query a database. Let's say an application has a page where you can find information about a user. Client-side, the user would enter the username through a form (ex: "sanddbox"). Server side, code similar to this would be ran:

Code: Select all

$statement = "SELECT * FROM users WHERE name = '" + $userName + "';"

For normal input, such as "sanddbox", the SQL query would be:

Code: Select all

SELECT * FROM users WHERE name = 'sanddbox' ;

The command would then be executed and the information would be given.

However, what if a user put SQL syntax into the form? Let's use your ' or 1 = 1 -- example.

The new statement would become:

Code: Select all

SELECT * FROM users WHERE name = ' ' or 1 = 1--' ;

The two symbols "--" together comment out the rest of the line, so the query executed would be:

Code: Select all

SELECT * FROM users WHERE name = ' ' or 1 = 1

The program would read that as "where the name is empty OR 1 is 1". Because 1 is 1, the query would return all of the rows, giving you information on ALL of the users.

On a login page, it would log you in regardless of whether the password is correct (I would demonstrate this, but I am out of time).

[thetan]

I consider the mere possibility of SQL injection a failure of engineering.

Like most things in new advances in tech. When SQL API's were first designed no attention was paid to security at all, it almost wasn't even an after thought. As a result, SQL injection is possible.

SQL, like the last letter in the name suggests, is a language. That being said, one can effectively think of it as a limited scripting language for moving crap around in a DB. I emphasize the word scripting because it's the important factor in what makes SQL injection possible.

Like all scripting languages, every time it is ran from source it must first be lexically analyzed into a stream of tokens which is then parsed into an AST (abstract syntax tree) which is then stepped through by the runtime. This is roughly how most non compiled scripting languages work on run. The exception would be non scripting languages such a C/C++ which are compiled once instead, meaning the parsing/compilation process only happens once.

SQL's original weakness to injection had everything to do with bad engineering. It's the mistake of letting the user add an arbitrary string of ascii to the buffer before it is lexically analyzed and then parsed. If user data were to be referenced via symbol tables from the AST after compilation has taken place, then SQL injection would of never have been possible.

Thankfully, MySQL and others have followed suite and created a mechanism known as prepared statements ( http://dev.mysql.com/tech-resources/art ... ments.html ). They work by first compiling the query and then binding user data after the compilation process. The result is that since compilation has already taken place, the user data is just that, user data and can do absolutely nothing to modify the structure of the underlying AST.

You can think of non prepared statements as an eval() statement in JavaScript, eval() effectively parses all data within it's argument as if it were native code. Traditional SQL works in a similar manner.

However, prepared statements work like GCC or VS, which is to say it's a compiler that compiles the query before hand and passes the user data in later.