andrewlocatelliwoodcock

Thoughts on Software

Introduction to SQL Injection

leave a comment »


I wrote a post recently about creating flexible WHERE clauses in stored procedures to support, amongst other things, requirements for flexible search functionality without resorting to building and executing dynamic SQL against the database. It realised afterwards that the concept of SQL injection is still not universally understood and that it would be a good idea to write a post explaining what it is, what threat it poses and how to guard against it. I will also reveal the worst, most SQL injection friendly piece of code I have ever come across …

SQL injection is a technique whereby an attacker manages to get your database to run SQL of their choosing thereby gaining access to your complete user list, causing the database server to drop a table or even a database and generally causing mayhem. Typically an attacker will append extra instructions or conditions onto a valid SQL statement and when that statement is executed so is the attacker’s extra code. An attacker gaining access to your user account table is bad enough but imagine if you’ve also been sloppy enough to have stored passwords, credit card details, etc. …

But just how does an attacker append extra, malicious statements to your own SQL? The main vector is via dynamically-generated SQL which is sent to a database server for execution.

An example: you intend to return the account details for the current user “X123” from the Accounts table and so dynamically build this SQL statement in your code before running it against your database server:

SELECT UserName, Name, PhoneNumber, Address, Password, CreditCard FROM Accounts WHERE UserId = 'X123'

The results of this operation are a single tuple intended to be seen only by user X123. A naive implementation of this code would ask the user to enter their ID and then simply wrap the ID in single quotes; when the user only enters X123, all is fine. However, this opens up a route for any malicious user who decides to enter their ID as, for example, X123′ OR ‘a’=’a:

SELECT UserName, Name, PhoneNumber, Address, Password, CreditCard FROM Accounts WHERE UserId = 'X123' OR 'a'='a'

Uh-oh. Now we have a statement that returns all this sensitive information for rows where the UserId = ‘X123’ or whenever ‘a’ is equal to ‘a’; ‘a’ is of course always equal to ‘a’, so the database will now happily return this information for everyone in the Accounts table …

One way to guard against this is to attempt to clean up the user ID before adding it to the SQL statement.  A simpler and far more effective approach is to use a stored procedure instead of the dynamically-generated SQL:

CREATE PROCEDURE [dbo].[GetUser]
	@id  VARCHAR(255)
AS
BEGIN

	SELECT
                UserName, Name, PhoneNumber, Address, Password, CreditCard
	FROM
		Accounts
	WHERE
		UserId = @id

END

Not only does this approach have performance benefits but now, if our attacker enters their ID as X123′ OR ‘a’=’a, they will simply receive an empty result set because no member has an id exactly equal to X123′ OR ‘a’=’a.

There is still a possible means of attack here though: if the WHERE clause had read UserId LIKE @id, which might be used to avoid making the code case-sensitive, an attacker could pass an id of ‘%’ which would match and return every row in the table … so, assuming we are using SQL Server, for an additional level of paranoia we could also use the SET ROWCOUNT statement before the select statement:

        SET ROWCOUNT 1      -- Only ever return one row

The above piece of code would guarantee that even if an attacker managed to inject SQL that could return all rows in the table, only a single row would ever be returned.

Another form of SQL injection attack is to trick the database into running malicious update and delete statements: imagine an attacker working out that your database has an Invoices table and setting the paid field of all records in the table to “false” … or deleting your Sales table with all your sales data in it, or even dropping your entire database … Impossible, right ..?

Unfortunately, no, it isn’t. Having harvested all our account data, our attacker now wants to destroy our business by deleting the Accounts table. Using our naive UserId code above, the attacker now enters their ID as X123′; DROP TABLE Accounts resulting in a statement:

SELECT UserName, Name, PhoneNumber, Address, Password, CreditCard FROM Accounts WHERE UserId = 'X123'; DROP TABLE Accounts

Now they would have to do a little more than this to account for the trailing single quote that we add to the end of the entered ID value but you can see the danger here: your attacker now how your users’ credit cards, phone numbers, passwords (which they may well use on other sites) AND they’ve deleted your Accounts table … let’s hope you have a back up somewhere else or you could well be out of business. But again, this attack fails if instead of using dynamic SQL we used the stored procedure given above because no user  has an id exactly equal to X123′; DROP TABLE Accounts.

By now you should be wiser as to what SQL Injection is, the damage it can do and also how to prevent it, so you should now be able to appreciate the story about the worst SQL Injection exposure I ever came across (names withheld to protect the very, very guilty): a software engineer in a company I worked for once sent round an email to the general support email address asking why the query string in their web application wasn’t working … after a bit of investigation, it turned out that they were passing a dynamic SQL statement in the URL’s query string and this statement was passed in the clear to the web server for immediate execution …

I don’t believe that person works at that company any more. Let’s hope they don’t work at yours …

Written by andrewlocatelliwoodcock

June 1, 2011 at 19:55

Posted in SQL Server, T-SQL

Tagged with , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: