T-SQL: Dynamic SQL and SQL Injection (sp_executesql)

When we create dynamic SQL code, we can add the parameters in two ways:

1. Concatenate them into the dynamic code with +
2. Use sp_executesql

In this example I will show both scenarios when a SQL injection is involved.

USE AdventureWorks2014;
GO

SELECT DISTINCT FirstName
FROM Person.Person
ORDER BY FirstName;
GO

Concatenate the parameters into the dynamic code with +

DECLARE
	@FirstName VARCHAR(128) = 'Rob'
	, @DynamicSQL1 VARCHAR(MAX);

SET @DynamicSQL1 = '
SELECT DISTINCT FirstName
FROM Person.Person
WHERE FirstName = ''' + @FirstName + '''
ORDER BY FirstName;
'

PRINT @DynamicSQL1;
EXEC (@DynamicSQL1);
GO

Concatenate and SQL injection.

DECLARE
	@FirstName VARCHAR(128) = 'Rob'' OR LEFT(FirstName, 1) LIKE ''A'
	, @DynamicSQL2 VARCHAR(MAX);

SET @DynamicSQL2 = '
SELECT DISTINCT FirstName
FROM Person.Person
WHERE FirstName = ''' + @FirstName + '''
ORDER BY FirstName;
'

PRINT @DynamicSQL2;
EXEC (@DynamicSQL2);
GO

Plug the parameters into the dynamic code with sp_executesql

DECLARE @FirstName VARCHAR(128) = 'Rob'--'Rob'' OR LEFT(FirstName, 1) LIKE ''A';
DECLARE @DynamicSQL3 NVARCHAR(MAX)
DECLARE @Parameters NVARCHAR(500);

SET @DynamicSQL3 = N'
SELECT DISTINCT FirstName
FROM Person.Person
WHERE FirstName = @FN
ORDER BY FirstName;
';

SET @Parameters = N'@FN VARCHAR(128)';

PRINT @DynamicSQL3;
EXEC sp_executesql
	@DynamicSQL3
	, @Parameters
	, @FN = @FirstName;
GO

sp_executesql and SQL Injection

DECLARE @FirstName VARCHAR(128) = 'Rob'' OR LEFT(FirstName, 1) LIKE ''A';
DECLARE @DynamicSQL4 NVARCHAR(MAX)
DECLARE @Parameters NVARCHAR(500);

SET @DynamicSQL4 = N'
SELECT DISTINCT FirstName
FROM Person.Person
WHERE FirstName = @FN
ORDER BY FirstName;
';

SET @Parameters = N'@FN VARCHAR(128)';

PRINT @DynamicSQL4;
EXEC sp_executesql
	@DynamicSQL4
	, @Parameters
	, @FN = @FirstName;
GO

As shown in the example above, it is recommended to execute dynamic SQL only with sp_executesql.

Keep it simple :-)