The examples were run against a copy of the Adventure Works database.
- Basic Demo (ASP.NET MVC / C# / Visual Studio 2010)
- Second Order Demo (WinForms / C’# / Visual Studio 2010)
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
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.