The purpose of this post is to demonstrate a simple SQL injection attack.  For information on securing a database against SQL injection, please review this post.

NOTE Implementing an actual SQL injection attack against a system for which you have not been provided explicit authorization may result in your prosecution. This post is intended to educate folks on the basic pattern so that they may plan their defense.  The pattern/technique demonstrated here is highly simplistic and widely demonstrated and discussed in forums related to SQL injection defense.

The first step is to set up a demonstration database.  In this database, a few tables and a stored procedure using dynamic SQL will later be created:

USE master;
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'SqlInjectionDemo') DROP DATABASE SqlInjectionDemo;
GO

CREATE DATABASE SqlInjectionDemo;
GO

Next, a couple tables representing products in an online product catalog will be setup and populated:

USE SqlInjectionDemo;
GO

CREATE TABLE dbo.ProductCategory (ProductCategoryID int not null, Name nvarchar(50));
GO
INSERT INTO dbo.ProductCategory VALUES (1, 'Electronics'), (2, 'Media');
GO

CREATE TABLE dbo.Product (ProductID int not null, ProductCategoryID int not null);
GO
INSERT INTO dbo.Product VALUES (1,1), (2,1), (3,1), (4, 2), (5, 2), (6, 2);
GO

To access the products in the product catalog, a stored procedure will be employed which accepts as its parameter the name of the product category on which to restrict the products returned:

CREATE PROC dbo.spGetProducts @ProductCategoryName NVARCHAR(50)
AS
   DECLARE @sql NVARCHAR(MAX)='';
 
   SET @sql =  'SELECT a.* '+
               'FROM dbo.Product a '+
               'INNER JOIN dbo.ProductCategory b '+
                   'ON a.ProductCategoryID=b.ProductCategoryID '+
               'WHERE b.NAME='''+ @ProductCategoryName +''''

   EXEC(@sql)
GO

The application is intended to call this stored procedure passing the name of a product category.  When this takes place, the proc returns a limited set of data: 

EXEC spGetProducts N'Electronics'
GO

ProductID   ProductCategoryID
----------- -----------------
1           1
2           1
3           1

(3 row(s) affected)

However, using SQL injection techniques, a malicious user could alter the statement logic to return all products:

EXEC spGetProducts N''' OR ''1''=''1'
GO

ProductID   ProductCategoryID
----------- -----------------
1           1
2           1
3           1
4           2
5           2
6           2

(6 row(s) affected)

Going one step further, a malicious user could terminate the original SQL statement and append another one disclosing still more information:

EXEC spGetProducts N''';SELECT * FROM sysobjects WHERE NAME <> '''
GO

ProductID   ProductCategoryID
----------- -----------------

(0 row(s) affected)

name               
--------------------
sysrscols          
sysrowsets         
sysallocunits      
...
(56 row(s) affected)

To defend against this attack, the stored procedure simply needs to be rewritten as follows:

ALTER PROC dbo.spGetProducts @ProductCategoryName NVARCHAR(50)
AS
   SELECT a.*
   FROM dbo.Product a
   INNER JOIN dbo.ProductCategory b
      ON a.ProductCategoryID=b.ProductCategoryID
   WHERE b.NAME = @ProductCategoryName
GO

To be fair to developers, not all dynamic SQL is as easily replaced with parameterized SQL.  Still, avoiding dynamic SQL is the key to preventing SQL injection.

To clean up the demo environment, execute the following statements:

USE master;
GO

DROP DATABASE SqlInjectionDemo;
GO