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.

Follow

Get every new post delivered to your Inbox.