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

Discuss the many weaknesses of browser security and ways to mitigate the threat

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

Post by akaktsn on Sat Sep 11, 2010 5:59 pm
([msg=45535]see The significance of ' or 1=1- in SQL injection.[/msg])

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
akaktsn
New User
New User
 
Posts: 3
Joined: Mon Sep 06, 2010 2:26 pm
Blog: View Blog (0)


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

Post by sanddbox on Sat Sep 11, 2010 6:13 pm
([msg=45536]see Re: The significance of ' or 1=1- in SQL injection.[/msg])

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).
Image

HTS User Composition:
95% Male
4.98% Female
.01% Monica
.01% Goat
User avatar
sanddbox
Expert
Expert
 
Posts: 2331
Joined: Sat Jul 04, 2009 5:20 pm
Blog: View Blog (0)


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

Post by thetan on Sat Sep 18, 2010 8:37 pm
([msg=46075]see Re: The significance of ' or 1=1- in SQL injection.[/msg])

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.
"If art interprets our dreams, the computer executes them in the guise of programs!" - SICP

Image

“If at first, the idea is not absurd, then there is no hope for it” - Albert Einstein
User avatar
thetan
Contributor
Contributor
 
Posts: 657
Joined: Thu Dec 17, 2009 6:58 pm
Location: Various Bay Area Cities, California
Blog: View Blog (0)



Return to Web

Who is online

Users browsing this forum: No registered users and 0 guests