SQL SERVER – One Trick of Handling Dynamic SQL to Avoid SQL Injection Attack?

SQL Server has so many things to learn and I always find it amazing. My conversations with customers often come up with security questions esp around SQL Injection. Many have claimed SQL Injection is a SQL Server problem. It takes quite some time for me to let them know there is nothing about SQL Server and SQL Injection. SQL Injection is an outcome of wrong coding practices. One of the recommendations I give is about not using Dynamic SQL. There might be some situations where you can’t avoid it. My only advice would be, avoid if possible. In this blog, I would demonstrate a SQL Injection problem due to dynamic SQL and a possible solution you can have.

Let’s assume that we have a simple search page where user can use the blank search or provide filter in any field. We have provided two fields to use “First Name” and “Last Name”. The user types something and hits search. Here is our code of stored procedure which fires behind the scene.

USE AdventureWorks2014
GO
CREATE PROCEDURE search_first_or_last
@firstName NVARCHAR(50)
,@lastName NVARCHAR(50)
AS
BEGIN
DECLARE @sql NVARCHAR(4000)
SELECT @sql = ' SELECT FirstName ,MiddleName, LastName' +
' FROM Person.Person WHERE 1 = 1 '
IF @firstName IS NOT NULL
SELECT @sql = @sql + ' AND FirstName LIKE ''' + @firstName + ''''
IF @lastName IS NOT NULL
SELECT @sql = @sql + ' AND LastName LIKE ''' + @lastName + ''''
EXEC (@sql)
END

If I use this string to execute in last name ”;drop table t1–

EXEC search_first_or_last '%K%', ''';drop table t1--'

The dynamic string would be

SELECT FirstName, MiddleName, LastName FROM Person.
Person WHERE 1 = 1 AND FirstName LIKE '%K%' AND LastName LIKE '';DROP TABLE t1--'

Do you see the problem? Yes, users can drop table t1 if code is running under a high privilege account.

One of the solution of the problem would be to use sp_executesql. Here is the better version using

CREATE PROCEDURE search_first_or_last
@firstName NVARCHAR(50)
,@lastName NVARCHAR(50)
AS
BEGIN
DECLARE @sql NVARCHAR(4000)
SELECT @sql = ' SELECT FirstName , MiddleName, LastName' +
' FROM Person.Person WHERE 1 = 1 '
IF @firstName IS NOT NULL
SELECT @sql = @sql + ' AND FirstName LIKE @firstName'
IF @lastName IS NOT NULL
SELECT @sql = @sql + ' AND LastName LIKE @lastName '
EXEC sp_executesql @sql
,N'@firstName nvarchar(50), @lastName nvarchar(50)'
,@firstName
,@lastName
END

Hope you would be able to use this and implement in your project. Are you using these simple techniques in your production code? Have you ever faced similar problems during audit? Do let me know of your learnings.

Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)