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

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.

Slides

All SQL Server Text Articles

All SQL Server Slides

All SQL Server Tutorial Videos

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

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'

Set @sql = @sql + ' and FirstName=''' + @FirstName + ''''
Set @sql = @sql + ' and LastName=''' + @LastName + ''''
Set @sql = @sql + ' and Gender=''' + @Gender + ''''
Set @sql = @sql + ' and Salary=''' + @Salary + ''''

Execute sp_executesql @sql
End
Go

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'


Execute sp_executesql @sql,
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.

[SpainLord]

this is exactly what i was looking for since i had just spent a lot of time making a dynamic store procedure and then noticed it was easy to sql inject it
THANKS A LOT

[manojg]

These contents are as valuable as GOLD.

[owhbbuz]

Thank you so much for your precious time you took to provide us a wonderful explanation on Dynamic SQL with respect to SQL injection !!!

[raqibul]

The World is still beautiful because of some people like U.

[gopalpandurangan]

Excellent video about dynamic sql. Easy to understand.

[krishnaraovasudevan]

Sir, first hats off to your teaching . I am not a programmer but your video makes me take calls to develop. Consequently I have one wherein I have to insert values from one table to another table. I have to create a stored procedure to create dynamic rows to match columns which is dynamically created by the trigger, this module is taken care of by another developer. So my project is to create rows to match columns in another table . Hope I am clear in saying my issue. Look forward to your support

[vicomtethierry]

Thanks a lot, Really clear, easy to understand and really helpful

[sabitkondakc]

Hello Venkat, I appreciate your help with these materials, especially SQL injection, neat and in detail, are you on Udemy platform? I would love to see your other courses ...

[krishna.sacharya]

Dear Sir, Thank you so much, for helping us, and your genius, its very easy to understand and am proud to be Student of a Teacher like you, and I need to understand Grant and Revoke Concepts. Am sorry i coun't find out vide, if you have already posted a video on these topic. if not, please post a video on Grant and Revoke. Your Student. Thank you Again.

[martingold]

Do you have an example where you have multiple records being parsed in for a parameter like having multiple firstnames for the @firstname parameter?

[Lordpm]

Hi, how would you do this if the number of parameters is dynamic and dependent on the user?

[PinasPiliNa]

Thanks. Hope you can do some Real World Tutorials ON Accounting System. Like Producing Financial Statements, Balance Sheet, Profit and Loss Statements etc.

[justanothercivicowner]

Hey so I am learning dynamic sql for my work but I do have some questions. Is there any benefits to this as oppose to creating an api and doing all your logic there?

[SertuncSELEN]

God protect you for doing such useful things before everything.I have a question.I use but when I run it through sql it drops again. Is it normal?

my code

EXEC ]
@FirstName = N'sertunc',
@LastName = N' ' drop.... '

[WisdomWomenWarrior]

can anyone suggest indept Dynamic sql videos or tutorials