I learned alot of this by mistake. I was working for copperfield.com, a large chimney supply company, and for some reason there was this guy out there (a competitor?) who continually hacked my database. It took me forever to figure out how he was doing it. And I am sure you can guess what he was doing. He was using SQL Injections to manipulate my database. The Bastard.
So I read up on this subject, bought some security books, and eventually locked him out. There are a ton of sites out there on this subject, but here it is in a nutshell.
With SQL Injection, an attacker is able to change or add queries that are sent to a database by playing with the input to the web application (like through a form for example). The attack works when a program builds queries based on strings from the client, and passes them to the database server without handling characters that have special meaning to the server.
I'll walk through a few examples to illustrate the problem. The whole point of this article is simple, do not be lazy when it comes to securing your code. If you understand that fully, and plan to (or already are) writing secure code, no need to read on.
Say that we have an SQL-based user database. To make things simple, we say that the user registers his real name only, and that the Java code that picks up his real name from the registration form and stores it in the database looks like this:
Java
name = request.getParameter("TheName");
query = "INSERT INTO Usr (RealName) VALUES ('" + name + "')";
or in ASP it would look almost identical...
name = request.form("TheName");
query = "INSERT INTO Usr (RealName) VALUES ('" + name + "')";
First let me just say now, please don't code like this. That being said, let's say we registered with the name "Frosty T. Snowman", which would give the database query:
INSERT INTO Usr (RealName) VALUES ('Frosty T. Snowman')
No problem. But then Bugsy O'Gangster comes along. When he tries to register, the database receives the following query:
INSERT INTO Usr (RealName) VALUES ('Bugsy O'Gangster')
The query definetly gives an error message, and our friend Bugsy is unable to register. Why? Because the single quote that is part of his name interferes with the single quotes used to mark the start and end of strings in SQL. The single quote is a metacharacter inside an SQL string constant. If we do not handle such metacharacters somehow, our application will not only produce unfortunate error messages, but it will also be vulnerable to attacks. So let's begin the attack.
First we pretend that we're making a web application that requires users to log-in through a form. Not knowing much about security, we just follow the examples in many of the books out there. Here's a snippet from our vulnerable Java program that I really DID find on a net tutorial:
userName = request.getParameter("SomeUser");
password = request.getParameter("SomePass");
query = "SELECT * FROM Usr "
+ "WHERE UserName='" + userName + "' "
+ "AND Password='" + password + "'";
The program retrieves a user name and a password from the input parameters, builds it's query, and passes the query to a database ( that's not shown in the code). o/ Bag Guy Music Start o/
Enter an attacker (us). We know, or can guess, that one of our users is named "MickeyMouse." So we enter the following in the user name fields, and leaves the password empty:
MickeyMouse' --
As our program just inserts the input unmodified in the query, what eventually is sent to the database looks like this:
SELECT * FROM Usr WHERE UserName='MickeyMouse' --' AND Password=''
The two hyphens (- -) make up an SQL commennt introducer. In the example above, it effectively inactivates the test for a matching password! If a user named "MickeyMouse" exists in the database, the attacker just logged in as that poor Mickey guy without giving any passwords. As I mentioned in my article about XSS, if you Google a bit you will be shocked at the number of sites you can 'log-in-as-anyone-you-want'. Try a search query on something like, "Enter your username" or "Please login".
So, how do we avoid the problem? The most obvious answer most of you are probably thinking is to filter out that damned double hyphen. But, the hyphens are not part of the problem at all.
Say that we're using MS (Microsoft) Access as the database of our application. Access does not understand the SQL comment introducer. Here's the ASP/VBScript equivalent of the above Java code:
userName = Request.Form("user")
password = Request.Form("pass")
query = "SELECT * FROM Usr "
& "WHERE UserName='" & userName & "' "_
& "AND Password='" & password & "'"
We, as the attacker know that regular comments will get us nowhere, so we turn to playing with boolean operator precedence rules. Here's what we enter for the user name this time, still leaving the password empty:
MickeyMouse' OR 'a'='b
You do see where this is going, don't you? Our application puts it all together, and passes this query to MS Access:
SELECT * FROM Usr
WHERE UserName='MickeyMouse' OR 'a'='b'
AND Password=''
And once again we gain access to Mickey's stuff (us bastards!), this time without using the SQL comment introducer. I will do a little explaining for the newbies to SQL. The boolean operators AND and OR are influenced by certain priority rules. The rules state that AND takes precedence over OR, meaning that the AND part will be executed before the OR part. The AND part is
'a'='b' AND Password=''
and it returns FALSE, as 'a' doesn't equal 'b', and there probably aren't any empty passwords either. The original query is thus reduced to:
SELECT * FROM Usr WHERE UserName='MickeyMouse' OR FALSE
As OR requires just one of the operands to be true, it matches the entry with a user name of "MickeyMouse." And that's it, we just commented out the password test using boollean precedence rules.
So you see ,the hyphens were not the problem at all. Not one bit. The security hole happened because the attacker was able to enter a single quote, and have that quote prematurely terminate the SQL string constant. It's all about parsing. When the SQL parser of the database has just read:
SELECT * FROM Usr WHERE UserName='
that is, all characters up to and including the first single quote, it switches to parsing a string constant. It's in a string context. The quote allows us (the attacker) to switch context, and that is where the problems are.
Based on the code above, it's easy to draw the conclusion that all quotes are bad. Some web programming platforms, like PHP, may automatically escape all incoming quotes for you. The conclusion isn't entirely correct, and escaping all quotes from the client-side is the wrong way to attack the problem. Quotes are bad only when they make a parser switch context, like in our attack above.
In the following example, most characters are bad. And the attack we'll examine will work even if all quote characters are removed from or escaped in the input.
Okay now let's assume we have an ASP program that, among doing many other things, it looks up information on a customer from a given a customer ID. This is done all over the damn net. The customer ID enters the application from the client-side,(in a URL parameter), and gets stored in the custId variable. The program prepares a database query by including customerId in a query string:
customerId = Request.QueryString("id")
query = "SELECT * FROM Customer WHERE CustId=" & customerId
There are no quotes in this query, so customerId is supposed to be numeric. But thanks to VBScript being typeless (and many many other scripting languages), an attacker may include more than just decimal digits in the custId variable by changing the id parameter to something like this (which is the most common SQL attack in my opinion ):
1; DELETE FROM Customer
Our program concatenates the bad guy's input, and passes the following query to the database:
SELECT * FROM Customer WHERE CustId=1; DELETE FROM Customer
The first thing that happens is obvious, it returns a query based upon CustomerID equalling 1. Then a DELETE statement that doesn't look good at all: if the database user used by the application has proper access, every single row will be deleted from the database! I'll say it again, that Bastard!
Of course, the second query could have been anything, not only DELETE. The attacker could, for instance, have used INSERT or UPDATE to put false information into the database. We could do anything we want here. Almost any valid SQL will work.
With MS SQL Server you can even leave the semicolon. Up to you.
What was the darn problem this time? We had no troublesome quote characters. We'll have to turn to parsing and contexts again. When the SQL parser of the database has just read:
SELECT * FROM Customer WHERE CustId=
It turns to a numeric context: it parses a number. Escaping of quotes will not be enough, as any non-numeric character will make the database server leave the numeric context, and start looking for SQL keywords again. The correct approach is to make sure the data we insert in the query is actually a number, and nothing else. But I'll delay the correct approach even further as I am havinf fun playing the attacker, and continue with examples from applications that don't do it correctly.
So far we've seen how to change queries, and how to manipulate info inside the database. But how about extracting information to gain access to something we're not supposed to see, huh? Most people suggest adding a SELECT statement after a new semicolon. But in most cases that approach won't work, as the new SELECT typically will be returned in a second record set. The application is most likely written to accept only one set.
However, SQL has another construct that aids the attacker: UNION SELECT. With UNION SELECT, two SELECT statements are treated as one. And best of all, the first SELECT dictates the names of the columns, so the application will not notice that another SELECT is interfering.
Example: We have a PHP program that displays news items. Each news item belongs to one of several categories, and it is possible to instruct the program to pick news from a single category only. Here's an excerpt from the program:
$cat = $_GET["category"];
$query = "SELECT Id,Title,Abstract FROM News "
"WHERE Category=" . $cat;
The URL parameter category, which is supposed to contain an integer, dictates what category to show. Along comes us, the attacker, and we happen to know that this application also has a Usr table, with user names and passwords of registered users. To get to that tempting information, we make sure the category parameter is:
1 UNION SELECT 1,UsrName,Passwd FROM Usr
As always, our application passes incoming data straight to the database, so the poor poor database server eventually executes:
SELECT Id,Title,Abstract FROM News
WHERE Category=1 UNION SELECT 1,UsrName,Passwd FROM Usr
The resulting page will contain news titles and news abstracts, mixed with user names and passwords from the Usr table.
But why did the attacker put that number one inside the query? He did so because UNION SELECT puts a couple of constraints on the second SELECT. First, it must contain exactly the same number of columns as the initial SELECT. And second, the data type of each column in the second SELECT must match the type of each column in the first. The attacker just wants the user name and password, which makes up two columns. So he'll have to add a fictitious third column. He knows that user name and password are character columns, and that title and abstract are also characters. Those two pairs match. As id is numeric, he inserts his randomly chosen number as the first column. By that insertion, both UNION SELECT requirements are met.
There's another exploit that is viscious, and I mean vicious! Just by visiting a URL of a server you can actually shut the SQL server down. Of course I would not recommend playing around with this, but it's your life...do what you want:
http://example/default.asp?id=99;SHUTDOWN
This will, and please trust me on this one, this will shut down a Microsoft SQL Server that does not parse their QueryStrings.
Well, I'd better get back to work (my current client is probably wondering where the hell I am this morning) so I'll end this here. You now know a little about some basic attacks. And the answer to preventing these are simple: write secure code and parse your input.
Take Care,
Trope
Ps. Just kidding about the bail money, so be good think12.
|
Copyright Open Source Institute, 2006 
