SQL Injection Attacks – DunDDD 2012

Examples

The examples were run against a copy of the Adventure Works database.

Required Tables

For the Second Order Demo you need the following table added to the Adventure Works database:

CREATE TABLE [dbo].[FavouriteSearch](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](128) NOT NULL,
	[searchTerm] [nvarchar](1024) NOT NULL
) ON [PRIMARY]

GO

Slide Deck

The slide deck is available for download in PDF format.

Further Reading

During the talk I mentioned a lesson from history on why firewalls are not enough.

I also showed XKCD’s famous “Bobby Tables” cartoon, and also a link to further information on dynamic SQL in Stored Procedures.

More information about the badly displayed error messages can be found amongst two blog posts: What not to develop, and a follow up some months later.

I wrote a fuller article on SQL Injection Attacks that you can read here although it is a few years old now, it is still relevant given that SQL Injection Attacks remain at the top of the OWASP list of vulnerabilities.

SQL Server User Group: SQL Injection Attacks

Examples

The examples were run against a copy of the Adventure Works database.

Required Tables

For the Second Order Demo you need the following table added to the Adventure Works database:

CREATE TABLE [dbo].[FavouriteSearch](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](128) NOT NULL,
	[searchTerm] [nvarchar](1024) NOT NULL
) ON [PRIMARY]

GO

Stored Procedure with dynamic SQL

This is the stored procedure from the last demo which shows the Stored Procedure dynamically building a SQL statement that is susceptible to a SQL Injection Attack.

CREATE procedure [dbo].[SearchProducts]
(
  @searchId int
)
AS
BEGIN

  DECLARE @searchTerm NVARCHAR(1024)
  SELECT @searchTerm = searchTerm FROM FavouriteSearch WHERE id = @searchId

  DECLARE @sql NVARCHAR(2000) =
  'SELECT ProductID, Name, ProductNumber, ListPrice
  FROM Production.Product
  WHERE DiscontinuedDate IS NULL
  AND ListPrice > 0.0
  AND Name LIKE ''%'+@searchTerm+'%''';

  EXEC (@sql);

END

 

Slide Deck

The slide deck is available for download.

Further Reading

During the talk I mentioned this lesson from history (why firewalls are not enough), I also showed XKCD’s famous “Bobby Tables” cartoon, and also a link to further information on dynamic SQL in Stored Procedures. More information about the badly displayed error messages can be found amongst two blog posts: What not to develop, and a follow up some months later.

I wrote an article on SQL Injection Attacks that you can read here.

Upcoming talks

My SQL Injection Attack prevention talk is on the road. I’ve already given the talk in Dundee, Newport and Nuneaton. And in the coming months I’ll be delivering it in Glasgow, London and Newcastle.

If you want to come and learn about securing your database from a developer perspective you can come along.

SQL Injection Attack Talk in Nuneaton

I’ll be speaking to VBUG in Nuneaton on the 12th January 2010 on the subject of SQL Injection Attacks and Tips on How to Prevent Them.

More details on this event can be found on the VBUG website.

SQL Injection Attacks and Tips on How to Prevent Them

I’m giving a talk in Dundee on the topic of SQL Injection Attacks. If you are interested in the subject then the registration link is at the bottom of the page.

Wednesday, 28th October 2009 at 19:00 – 21:00
Queen Margaret Building, Dundee University

The Talk

In light of some recent events, such as the man who was convicted of stealing 130 million credit card details through a SQL Injection attack, it is imperative that developers understand what a SQL Injection Attack is, how they are carried out, and most importantly, how to defend your code against attack.

In this talk I’ll demonstrate a SQL Injection Attack on an application in a controlled environment*. I’ll show you where the vulnerable code lies and what you can do to harden it.

Although this talk uses C# as the application language and Microsoft SQL Server 2008 as the database engine many of the concepts and prevention mechanisms will apply to any application that accesses a database through SQL.

* Demonstrating an attack on a real system without the owner’s consent is a breach of the 1990 Misuse of Computers Act, hence the controlled environment.

The Venue

We are meeting in the Queen Mother Building at Dundee University. After the meeting we normally retire to the the bar at Laing’s

The Agenda

18:45 Doors Open
19:00 Welcome
19:10 The Talk (Part 1)
19:55 Break
20:05 The Talk (Part 2)
20:45 Feedback & Prizes
21:00 Repair to the Pub

Registration

Space is limited, we would therefore ask that you sign up.

If you really must do dynamic SQL…

I may have mentioned in previous posts and articles about SQL Injection Attacks that dynamic SQL (building SQL commands by concatenating strings together) is a source of failure in the security of a data driven application. It becomes easy to inject malicious text in there to cause the system to return incorrect responses. Generally the solution is to use parameterised queries

However, there are times where you may have no choice. For example, if you want to dynamically reference tables or columns. You can’t do that as the table name or column name cannot be replaced with a parameter. You then have to use dynamic SQL and inject these into a SQL command.

The problem

It is possible for SQL Server to do that concatenation for you. For example:

CREATE PROCEDURE GetData
	@Id INT,
	@TableName sysname,
	@ColumnName sysname
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @sql nvarchar(max) =
		'SELECT ' + @ColumnName +
		' FROM ' + @TableName +
		' WHERE Id = '+cast(@Id as nvarchar(20));
	EXEC(@sql)
END
GO

This is a simple stored procedure that gets some data dynamically. However, even although everything is neatly parameterised it is no protection. All that has happened is that the location for vulnerability (i.e. the location of the construction of the SQL) has moved from the application into the database. The application is now parameterising everything, which is good. But there is more to consider than just that.

Validating the input

The next line of defence should be verifying that the table and column names passed are actually valid. In SQL Server you can query the INFORMATION_SCHEMA views to determine whether the column and tables exist.

If, for example, there is a table called MainTable in the database you can check it with a query like this:

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'MainTable'

And it will return:

INFORMATION_SCHEMA.TABLES

There is a similar view for checking columns. For example:

INFORMATION_SCHEMA.COLUMNS

As you can see, the INFORMATION_SCHEMA.COLUMNS view also contains sufficient detail on the table so that when we implement it we only have to make one check:

ALTER PROCEDURE GetData
	@Id INT,
	@TableName sysname,
	@ColumnName sysname
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
    BEGIN
        DECLARE @sql nvarchar(max) =
            'SELECT ' + @ColumnName +
            ' FROM ' + @TableName +
            ' WHERE Id = '+cast(@Id as nvarchar(20));
        EXEC(@sql)
    END
END
GO

Formatting the input

The above is only part of the solution, it is perfectly possible for a table name to contain characters that mean it needs to be escaped. (e.g. a space character or the table may share a name with a SQL keyword). To escape a table or column name it is enclosed in square brackets, so a table name of My Table becomes [My Table] or a table called select becomes [select].

You can escape table and column names that wouldn’t ordinarily require escaping also. It makes no difference to them.

The code now becomes:

ALTER PROCEDURE GetData
	@Id INT,
	@TableName sysname,
	@ColumnName sysname
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
    BEGIN
        DECLARE @sql nvarchar(max) =
            'SELECT [' + @ColumnName + '] ' +
            'FROM [' + @TableName + '] ' +
            'WHERE Id = '+cast(@Id as nvarchar(20));
        EXEC(@sql)
    END
END
GO

But that’s not quite the full story.

Really formatting the input

What if you have a table called Cra]zee Table? Okay – Why on earth would you have a table with such a stupid name? It happens, and it is a perfectly legitimate table name in SQL Server. People do weird stuff and you have to deal with it.

At the moment the current stored procedure will simply fall apart when presented with such input. The call to the stored procedure would look like this:

EXEC GetData 1, 'Cra]zee Table', 'MadStuff'

And it gets past the validation stage because it is a table in the system. The result is a message:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Table'.

The SQL produced looks like this:

SELECT [MadStuff] FROM [Cra]zee Table] WHERE Id = 1

By this point is should be obvious why it failed. The SQL Parser interpreted the first closing square bracket as the terminator for the escaped section.

There are other special characters in SQL that require special consideration and you could write code to process them before adding it to the SQL string. In fact, I’ve seen many people do that. And more often than not they get it wrong.

The better way to deal with that sort of thing is to use a built in function in SQL Server called QUOTENAME. This will ensure the column or table name is properly escaped. The stored procedure we are now building now looks like this:

ALTER PROCEDURE GetData
	@Id INT,
	@TableName sysname,
	@ColumnName sysname
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
    BEGIN
        DECLARE @sql nvarchar(max) =
            'SELECT ' + QUOTENAME(@ColumnName) +
            ' FROM ' + QUOTENAME(@TableName) +
            ' WHERE Id = '+cast(@Id as nvarchar(20));
        EXEC(@sql)
    END
END
GO

Things that can be parameterised

There is still something that can be done to this. The Id value is being injected in to the SQL string, yet it is something that can quite easily be parameterised.

The issue at the moment is that the SQL String is being executed by using the EXECUTE command. However, you cannot pass parameters into this sort of executed SQL. You need to use a stored procedure called sp_executesql. This allows parameters to be defined and passed into the dynamically created SQL.

The stored procedure now looks like this:

ALTER PROCEDURE GetData
	@Id INT,
	@TableName sysname,
	@ColumnName sysname
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
    BEGIN
        DECLARE @sql nvarchar(max) =
            'SELECT ' + QUOTENAME(@ColumnName) +
            ' FROM ' + QUOTENAME(@TableName) +
            ' WHERE Id = @Identifier';
        EXEC sp_executesql @sql, N'@Identifier int',
                           @Identifier = @Id
    END
END
GO

This is not quite the end of the story. There are performance improvements that can be made when using sp_executesql. You can find out about these in the SQL Server books-online.

And finally…

If you must use dynamic SQL in stored procedures do take care to ensure that all the data is validated and cannot harm your database. This is an area in which I tread very carefully if I have no other choice.

Try and consider every conceivable input, especially inputs outside of the bounds of your application. Remember also, that defending your database is a multi-layered strategy. Even if you have the best firewalls and security procedures elsewhere in your system a determined hacker may find a way though your other defences and be communicating with the database in a way in which you didn’t anticipate. Assume that an attacker has got through your other defences, how do you provide the data services to your application(s) yet protect the database?

BBC repeating mindless nonsense

I’ve just read a report from the BBC that simply repeats some mindless drivel about SQL Injection Attacks from a spokesman for the US Department of Justice. According to the BBC:

Edward Wilding, a fraud investigator, told the BBC that this method was “a pretty standard way” for fraudsters to try to access personal data.

It “exploits any vulnerability in a firewall and inserts a code to gather information,” he explained.

It, however, does not point out that Mr Wilding is incorrect. It simply regurgitates the standard patter about firewalls without question. SQL Injection attacks have absolutely nothing to do with firewalls. They are all to do with leveraging mistakes in the way the application communicates with the database. An application which already has valid rights to communicate with the database. Firewalls may be in place to stop direct access to the database, but a SQL Injection attack takes a secondary route to get there.

In short, SQL Injection Attacks are the result of poor software development practices. Something, the prevalence of which, I’ve blogged about previously. I’ve also blogged about how to reduce the attack surface of the application with respect to SQL Injection attacks. In fact, it is so mind-numbingly easy to reduce the ability for someone to launch a SQL Injection Attack on an application I’m surprised developers are still allowed to get away with it.

But back to the article:

Mr Wilding said that chip-and-pin did provide some protection against SQL attacks, but there was little consumers could do to protect themselves against this kind of fraud.

You have to be kidding me! Chip-and-pin also has nothing to do with a SQL Injection attack. It cannot protect you from one. A SQL Injection Attack is an attack on a database, not the actual physical card. All chip-and-pin can do is ensure that a person using a credit or debit card knows the pin. Chip and pin is not used in online transactions. It is not used in telephone transactions.

“The real vulnerability, I suspect, is internet and telephone transactions. But this is a failure in the configuration of [corporate] firewalls,” he said.

Back to the firewall nonsense again. I repeat that firewalls cannot protect against SQL Injection Attacks because the route to the database is a valid one via a third process. (My machine runs the first process, the database is the second process the application being attacked is the third process) However, the BBC is still blithely repeating this misattribution of blame.

If blame is to be placed anywhere then it must surely be at the door of the developers who wrote the payment system that could so easily be hacked in order to gain sufficient access to the database to get all this data. This, of course, is if you believe that it was a SQL Injection Attack. The BBC could have got that bit wrong too!

UPDATE (@ 19:30)

I’ve just noticed that the BBC have reworked the article and it was last updated an hour ago. It does now contain a side box that I didn’t see before that at least, in some small way, explains that a SQL Injection Attack are “weaknesses in companies’ programming which allows them to get behind firewalls”. The main article now contains the paragraph:

The method is believed to involve exploiting errors in programming to get behind compnay [sic]  frewall’s [sic] and accessing [sic] data.

As you can see by the spelling and grammatical mistakes it must have been rather hastily put together. One quote from Mr Wilding has also changed from being reported as:

“The real vulnerability, I suspect, is internet and telephone transactions. But this is a failure in the configuration of [corporate] firewalls,” he said.

to:

“The real vulnerability [for cardholders], I suspect, is Internet and telephone transactions using credit cards were most vulnerable, he said, though added it was a failure of corporations, not customers.

Yet again, this looks like it was hastily put together because of the poor punctuation.

Come on, BBC, this is not journalism but reactive rubbish! Do you even understand what you are actually reporting?

A fantastic cartoon on SQL Injection Attacks

Just in time for the SQL Injection Attack micro presentation that I’m doing.

xkcd.com

From: xkcd.org

Technorati Tags: , ,

SQL Injection Attacks

Every day I see messages on various forums asking for help with SQL. Nothing wrong with that. People want to understand how something works, or have a partial understanding but something is keeping them from completing their task. However, I frequently also see messages that have SQL statements being built in C# or VB.NET that are extremely susceptible to injection attack. Sometimes it is from the original poster and, while they really need to learn to defend their systems, that is fine as they are trying to learn. Nevertheless there is also a proportion of people responding to these questions that give advice that opens up gaping security holes in the original poster’s system, if they follow that advice.

Consider this following example:

C#

static DataSet GetCustomersFromCountry(string countryName)
{
    SqlConnection conn = new SqlConnection("Persist Security Info=False;"+
        "Integrated Security=SSPI;database=northwind;server=(local)");
    string commandText = string.Format("SELECT * FROM Customers WHERE Country='{0}'",
        countryName);
    SqlCommand cmd = new SqlCommand(commandText, conn);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    return ds;
}

VB.NET

Function GetCustomersFromCountry(ByVal countryName As String) As DataSet
    Dim conn As SqlConnection = New SqlConnection("Persist Security Info=False;" + _
        "Integrated Security=SSPI;database=northwind;server=(local)")
    Dim commandText As String = String.Format( _
        "SELECT * FROM Customers WHERE Country='{0}'", _
        countryName)
    Dim cmd As SqlCommand = New SqlCommand(commandText, conn)
    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
    GetCustomersFromCountry = New DataSet
    da.Fill(GetCustomersFromCountry)
End Function

What happens here is that what ever the value of countryName is will be inserted (injected, if you prefer) directly into the SQL string. More often than not I see examples of code on forums where there has been absolutely no checking done and the developer has used countryNameTextBox.Text directly in the string format or concatenation statement. In these cases just imagine what the effect of various unrestricted text box entries might be.

For instance, imagine the values a malicious user might put in the text box on a web form. What if they type ';DROP TABLE Customers;-- ?

That would expand the full SQL Statement passed by the .NET application to be

SELECT * FROM Customers WHERE Country='';DROP TABLE Customers; -- '

So, no more customers (at least in the database… But how long in real life?)

Some people might then say, sure, but who in their right mind would give that kind of access on a SQL Server to the ASP.NET account? If you ask that question then you cannot have seen the number of people who post code with the connection strings clearly showing that, firstly, they are using the sa account for their web application and, secondly, by posting their problem to a forum they have given to the world the password of their sa account.

Some others might say, yes yes yes, but wouldn’t an attacker would have to know what the overall SQL statement is before they can successfully inject something? Not so, I say. If you look at code posted on forums it becomes obvious that the vast majority of values from textboxes are inserted right after an opening apostrophe, like the example above. Based on that assumption, all an attacker needs to do is close the apostrophe, add a semi-colon and then inject the code they want. Finally, just to make sure that any remaining SQL from the original statement is ignored they add a couple of dashes (comment markers in SQL)

These defenders-of-bad-SQL-because-you-can-never-completely-secure-your-system-anyway-so-why-bother will often follow up with, okay okaay! But the attacker would have to know the structure of the database as well! Well, maybe not. Normally there are common table names. I’m sure most people that have been dealing with databases for a few years will have come across many with tables with the same names. Customers, Users, Contacts, Orders, Suppliers are common business table names. If that doesn’t work it may be possible to inject an attack on sysobjects. Often an attacker just gets lucky or notices a quirky output when entering something unusual and uses that to work on cracking the web site or database.

So here I present three tips for improving the security of your SQL Server database. In no particular order, they are: Use parameterised queries. Login using an appropriate account and grant only the permissions necessary. Use stored procedures.

* Using parameterised queries is really very simple, and it can make your code easier to read, and therefore to maintain. Parameters also have other advantages too (for instance you can receive values back from parameters, not just use them for sending information into the query). The previous code example can be changed very easily to use parameters. For instance:

C#

static DataSet GetCustomersFromCountry(string countryName)
{
    SqlConnection conn = new SqlConnection("Persist Security Info=False;"+
        "Integrated Security=SSPI;database=northwind;server=(local)");
    string commandText = "SELECT * FROM Customers WHERE Country=@CountryName";
    SqlCommand cmd = new SqlCommand(commandText, conn);
    cmd.Parameters.Add("@CountryName",countryName);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    return ds;
}

VB.NET

Function GetCustomersFromCountry(ByVal countryName As String) As DataSet
    Dim conn As SqlConnection = New SqlConnection("Persist Security Info=False;" + _
        "Integrated Security=SSPI;database=northwind;server=(local)")
    Dim commandText As String = "SELECT * FROM Customers WHERE Country=@CountryName"
    Dim cmd As SqlCommand = New SqlCommand(commandText, conn)
    cmd.Parameters.Add("@CountryName", countryName)
    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
    GetCustomersFromCountry = New DataSet
    da.Fill(GetCustomersFromCountry)
End Function

* The application should be set up to use a specific account when accessing the SQL Server. That account should then be given access to only the things it needs. For instance:

GRANT SELECT ON Customers TO AspNetAccount

It is generally unwise to GRANT permission ON someObject TO PUBLIC because then everyone has the permission.

* My final tip is to use only stored procedures for selecting and modifying data, because then the code that accesses the tables is controlled on SQL server. You then do not need to grant access directly to the tables, only the stored procedures that are called. The extra protection then comes by virtue of the fact that the only operations that can be performed are those that the stored procedures allow. They can perform additional checks and ensure that relevant related tables are correctly updated.

NOTE: This was rescued from the Wayback Machine. The original post was dated Saturday, 25th September 2004.

Tags:


Original posts:

Excellent post Colin. I’d always wondered what a SQL Injection attack was without actually bothering to Google for it – and now I know.

9/26/2004 6:08 AM | Rob

this is great job, colin. i’ve just know the words ‘SQl injection’ but dont know what it exactly means. now i’ve got it. i can defend my own database.

thanks

10/3/2004 3:41 PM | Fired Dragon

I’ve seen people who’ve read this article thinking they can’t do it because you’ve only given .NET syntax. You should probably emphasize that ‘classic’ ADO also has Command objects with a Parameters collection, as does the more obscure underlying OLE DB object model. You can also use parameters with ODBC. There’s no excuse – parameters are cleaner, more secure, and less prone to error than building a SQL string. The slight disadvantage is that the code becomes a little less portable – SQL Server uses the @param notation, Oracle uses :param, and the OleDbCommand and OdbcCommand both use positional parameters marked with a ?

12/18/2004 1:01 AM | Mike Dimmick

I just can’t get this to work. I do exactly as the example shows, and the query doesn’t replace the parameters with the values to search for. The query came up with nothing, until I entered a row in the database with the name of the parameter as a field. It finds that, so it just doesn’t parse the command. I don’t get it.

7/27/2005 10:46 AM | Vesa Ahola

Since I cannot see your code I cannot see what is going wrong. However, I wrote a longer article about the subject over on codeproject.com. Perhaps that may help. See:

http://www.codeproject.com/cs/database/SqlInjectionAttacks.asp

7/27/2005 4:28 PM | Colin Angus Mackay

UPDATE: Sql Injection Attacks

As a follow up to my post on preventing SQL Injection Attacks a couple of months ago I just found this little nugget, I Made a Difference[^], and it shows what can be achieved if you don’t secure against SQL Injection attacks – and with only 3 hours of effort. Obviously, if you have access to the source code you will be able to launch an attack much quicker.

The original link seems to have disappeared. See the Wayback Machine for an archived copy, or the quoted section below:

Two weeks ago, I taught a Guerilla .NET course for DevelopMentor in Boston. Two or three days ago, a student who listened to me rant about SQL Injection attacks during the Code Access Security module lecture sent us (myself and the other two instructors) the following. It’s obviously been edited to protect the guilty:

“Hi, Ted. I want to thank you for the short primer on SQL injection attacks at the Guerrilla course in Woburn this month. We have a vendor who supplies us with electronic billing and payment services. (We send them billing data, and they present the bills to our customers and take the payments for us.) The week after the Guerrilla class I began to lose confidence in their application for various reasons, like seeing errors that included partial SQL statements, and in one case, a complete SQL statement that was accidentally left on a page from a debugging session. I told our company’s business manager that I was 80% confident that I could hack into their site using SQL injection. He called the vendor, who swore up and down that after spending $83,000 on firewalls that no one could ever hack into their site, and that we should go ahead and try.

“After three hours and a Google search on SQL injection, I was running successful queries from their login page and I had their server emailing us the query results via xp_sendmail. I was also able to confirm that the SQL Server login they use for their application has sa rights. I got a list of their clients, and was able to create tables in their databases.

“The vendor promised that by the next morning everything would be fixed. So the next morning at 8:00 am I tried again. I was no longer able to get results via xp_sendmail, but I was able to shutdown their SQL Server service by sending a shutdown command. I followed that up with a friendly call to their tech support line to let them know that they needed to restart SQL Server–I didn’t want to be too malicious. The guy at the other end of the line apparently had been there the entire night changing code and rolling out pages. He threatened to get on a plane, come to my office, and beat me up.”

“The disturbing thing about the incident is that there is enough data in the vendor’s database to allow someone to commit identity fraud or steal credit card and bank account numbers. And they are not a mom and pop shop either–their client list includes F—-, D—-, D—-, and V—-. [These are names that you would recognize, dear reader.] If I had been malicious I could have stolen data from any of those companies.”

Hunh. Three hours and a Google search was all it took. Anybody still think firewalls are the answer? “Security is a process, not a product.” — Bruce Schneier, Secrets and Lies.

NOTE: This was rescued from the Google Cache. The original date was Wednesday 17th November, 2004.

Tags:

Follow

Get every new post delivered to your Inbox.

Join 26 other followers