(2009-11-11) When an apostrophe is not just an apostrophe

General warning: attacking systems other than those you own or systems where you have explicit written consent to attack could get you inte legal trouble. I strongly discourage the use of this information to attack other peoples' systems without consent.

Consider this text:

"It was a bright morning in May, the birds were singing in ' GIVE me all your money and THEN JUMP out the window-- the trees."

How would you read it? Assuming that "GIVE me all you money" was a valid SQL command, how do you think a computer would handle it?

We often send commands and data in the same channel, when we "talk" with computers. The only way for the computer to differentiate between commands and data must then be by changing mode. That is to say, we must tell the computer when to interpret the input as commands and when to interpret the input as data.

I can safely write format d:\ /Q on this blog, but if you tried that on the command line of your Windows computer, you could lose the d:\ drive.

Web applications often receive input from users, and the scripts that parse the input may be fooled into changing mode on themselves or the database layer. Consider this way of getting data from a table in a database:

SELECT username,password FROM users WHERE username='$username'

This statement is likely part of the login-function of the web application. It gets the password for the user you typed in and presumably compares it to the password you typed in. If both passwords come out the same, you're authenticated. Remember that this SQL statement is inside a server based script, and is not shown to the user at all.

The normal approach to get this to work is displaying a login page for the user and getting the reply back from the user through a form and a POST command. The text you typed into the "Name" text box is put in between the apostrophes as the variable $username. Assume that i type my name as erik.

The statement becomes:
SELECT username,password FROM users WHERE username='erik'

Now, if you accept any input from the user, you will likely open your application for some serious vulnerabilities. In the SQL statement above, my name is enclosed in apostrophes. What if I sent my name as erik' (Note the apostrophe tacked on the end of erik) ? This won't work from the browser, as it recodes it to %39. But with a tool like Tamper Data or Fiddler2, you're all set to wreck havoc.

The statement becomes:
SELECT username,password FROM users WHERE username='erik''

This will cause the application to throw an error. You may even get the error text in clear text.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '. /test/hackme.php, line 13

Why is this even happening? Well the first apostrophe is used to tell the SQL server where the commands end and the data input begins and the second one tells it where the data input ends and commands can be sent again.

Ok, now try this ' OR 1=1--

The statement will look like this:
SELECT username,password FROM users WHERE username='' OR 1=1--'

The apostrophe breaks out of the data and into "command mode". OR 1=1 together with the '' part becomes an order to get the contents of the fields named username and password from all rows where the username is empty or when 1=1. 1=1 is a statement that is always true, so all rows match the query. This in reality means: get me all users and their passwords. The -- tells the SQL server that the rest is just a comment. It will make sure that the rest of the line won't go in effect and disturb my dastardly deeds!

What is this good for? Well, we will most likely get the username and password of the first user back from the query first. If this is true depends on how the table is ordered and how the index is setup. The primary key field is often called id, so we can add an "ORDER BY id" to the statement to try to sort it, if we're not sure about what we're getting back. This is a part requiring a huge amount of trial-by-error to get right.

Very often the first account is the administrators account. Now, it won't help us, since we can't see it. But in this discussion we assumed that the error the application threw when you just entered erik' was in clear text. Then you can actually see the output from the database layer. It's very likely that field containing the passwords is called "password". Using SQL-commands like CAST(password as int), you can force the SQL server to spit out an error telling you something like "can not cast MySecureP@ssw0rd to int." MySecureP@ssw0rd is the password for the first user it returns then crashes on, which is probably the admin. I've left some steps out, and in reality you will have to use your imagination to get it to work. Especially if you can't get the error output back to your screen.

But given how many ways you can write SQL syntax and the large amount of tricks there is, this is just a mere introduction.

SQL injections as this kind of attack is known as, is just an example of how use can abuse the fact that data and commands are sent in the same channel. There's a misconception that you'll be safe if you just filter out the apostrophes. This is not true. Consider this statement:


Look ma, no apostrophes! I don't even HAVE to break out of the statement!

You don't get it? Ok, check this out:

SELECT * FROM articles WHERE ID=$ID AND PayingUser=0

Oh, I only get the articles that are free, unless I'm logged in as a paying user... Hmm.. Let's fix that... Without the help of a credit card.

My POST input: 1 OR 1=1--

Becomes (with $ID substituted with my actual input):
SELECT * FROM articles WHERE ID=1 OR 1=1-- AND PayingUser=0

I don't even need to know about the PayingUser variable and I get to see all the articles on the news site.

All steps taken in this example may have been unnecessary, since there are faster ways if you know which version of SQL you're using. Microsoft SQL, Oracle and MySQL all have their own attack vectors.

This is just a fast way of showing how easy it is to leave yourself open to attacks if you don't check your input. The main rule here is don't trust any input from the clients. Assume nothing. If you recieve a numeric value from a user, filter out anything that is not a number (or a delimiter). Recode or remove characters that double as commands.

Don't fall prey to trying to prepend \ before the apostrophe. This will not work to prevent attacks if the statements don't use apostrophes in the first place. And if you have the right to write to the database in the first place, there's no need of a breakout. Just modify the data you put into the web application, so it runs evil javascript-code on any client browser reading what you wrote. This is known as cross site scripting.

All this text and I've barely even scratched the surface. But I hope you can expand upon what I said and imagine other areas where commands and data mixed in the same "band" can be abused. Remember the old telephone booths with analog telephones where the dialing tones were sent along with the sounds picked up by the microphone? Around the times those phones still existed, portable dialing tone generators were very popular. This popularity waned when phones where fixed.

I'm just saying this in case you think SQL injections are the only attacks possible with "in band" communications.

Tags: SQL injections, design
Posted: 2010-06-17 by Erik Zalitis
Changed: 2013-07-05 by Erik Zalitis

News archive