Dynamic SQL in Stored Procedure

In this video we will discuss, using dynamic sql in a stored procedure and it’s implications from sql injection perspective. We will discuss performance implications of using dynamic sql in a stored procedure in a later video.

Text version of the video
http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-in-stored-procedure.html

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1

Slides
http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-in-stored-procedure_11.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

All SQL Server Tutorial Videos

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists

Consider the following stored procedure “spSearchEmployees”. We implemented this procedure in Part 139 of SQL Server tutorial. This stored procedure does not have any dynamic sql in it. It is all static sql and is immune to sql injection.

Create Procedure spSearchEmployees
@FirstName nvarchar(100) = NULL,
@LastName nvarchar(100) = NULL,
@Gender nvarchar(50) = NULL,
@Salary int = NULL
As
Begin

Select * from Employees where
(FirstName = @FirstName OR @FirstName IS NULL) AND
(LastName = @LastName OR @LastName IS NULL) AND
(Gender = @Gender OR @Gender IS NULL) AND
(Salary = @Salary OR @Salary IS NULL)
End
Go

Whether you are creating your dynamic sql queries in a client application like ASP.NET web application or in a stored procedure, you should never ever concatenate user input values. Instead you should be using parameters.

Notice in the following example, we are creating dynamic sql queries by concatenating parameter values, instead of using parameterized queries. This stored procedure is prone to SQL injection. Let’s prove this by creating a “Search Page” that calls this procedure.

Create Procedure spSearchEmployeesBadDynamicSQL
@FirstName nvarchar(100) = NULL,
@LastName nvarchar(100) = NULL,
@Gender nvarchar(50) = NULL,
@Salary int = NULL
As
Begin

Declare @sql nvarchar(max)

Set @sql = ‘Select * from Employees where 1 = 1’

if(@FirstName is not null)
Set @sql = @sql + ‘ and FirstName=”’ + @FirstName + ””
if(@LastName is not null)
Set @sql = @sql + ‘ and LastName=”’ + @LastName + ””
if(@Gender is not null)
Set @sql = @sql + ‘ and Gender=”’ + @Gender + ””
if(@Salary is not null)
Set @sql = @sql + ‘ and Salary=”’ + @Salary + ””

Execute sp_executesql @sql
End
Go

Add a Web Page to the project that we have been working with in our previous video. Name it “DynamicSQLInStoredProcedure.aspx”. Copy and paste the HTML and code available on my blog at the following link
http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-in-stored-procedure.html

At this point, run the application and type the following text in the “Firsname” text and click “Search” button. Notice “SalesDB” database is dropped. Our application is prone to SQL injection as we have implemented dynamic sql in our stored procedure by concatenating strings instead of using parameters.
‘ Drop database SalesDB —

In the following stored procedure we have implemented dynamic sql by using parameters, so this is not prone to sql injecttion. This is an example for good dynamic sql implementation.

Create Procedure spSearchEmployeesGoodDynamicSQL
@FirstName nvarchar(100) = NULL,
@LastName nvarchar(100) = NULL,
@Gender nvarchar(50) = NULL,
@Salary int = NULL
As
Begin

Declare @sql nvarchar(max)
Declare @sqlParams nvarchar(max)

Set @sql = ‘Select * from Employees where 1 = 1’

if(@FirstName is not null)
Set @sql = @sql + ‘ and FirstName=@FN’
if(@LastName is not null)
Set @sql = @sql + ‘ and LastName=@LN’
if(@Gender is not null)
Set @sql = @sql + ‘ and Gender=@Gen’
if(@Salary is not null)
Set @sql = @sql + ‘ and Salary=@Sal’

Execute sp_executesql @sql,
N’@FN nvarchar(50), @LN nvarchar(50), @Gen nvarchar(50), @sal int’,
@FN=@FirstName, @LN=@LastName, @Gen=@Gender, @Sal=@Salary
End
Go

Summary : Whether you are creating dynamic sql in a client application (like a web application) or in a stored procedure always use parameters instead of concatnating strings. Using parameters to create dynamic sql statements prevents sql injection.