SQL Injection Attack Talk

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.

Community, SQL Injection Attack Talk, Talk Examples

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.

Community, SQL Injection Attack Talk

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.

Community, Software Development, SQL Injection Attack Talk

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.

Software Development

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?

Misc

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?