asp.net core docker 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.blogspo...

Slides
http://csharp-video-tutorials.blogspo...

All SQL Server Text Articles
http://csharp-video-tutorials.blogspo...

All SQL Server Slides
http://csharp-video-tutorials.blogspo...

All SQL Server Tutorial Videos
https://www.youtube.com/playlist?list...

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenka...

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

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.blogspo...

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

On the code-behind page, use stored procedure spSearchEmployeesGoodDynamicSQL instead of spSearchEmployeesBadDynamicSQL. We do not have to change any other code. At this point run the application one more time and type the following text in the "Firstname" textbox and click the "Search" button.
' Drop database SalesDB --

Notice "SalesDB" database is not dropped, So in this case our application is not succeptible to SQL injection attack.

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.

asp.net core docker Dynamic SQL in Stored Procedure
220 Likes	220 Dislikes
46,147 views views	524K followers
Science & Technology	Upload TimePublished on 11 Apr 2017

Related keywords

wcf vs web api,ado.net core,sql server management studio,webkinz,webadvisor,craigslist nj,wcf one piece,asp.net core 3,ajax players,weber grill parts,webtoon,tutorials by hugo,csharp corner,mvc design pattern,asp.net machine account,servers for minecraft,asp.net core dependency injection,ado.net tutorial,services angular,ajax ontario,asp.net mvc tutorial,asp.net cos'è,csharp assembly,tutorialspoint python,sql join,services briefcase,asp.net core web api,ajax jquery,wccftech,craigslist ny,asp.net zero,csharp foreach,server memes,sql date format,services online,chase,serverless architecture,server resume,wcf c#,server books,tutorialspoint javascript,mvcc connect,ado.net mysql,services technologies gps,ajax deadpool,server jobs,cvs,website,mvc tutorial,costco hours,wcf service application,tutorialspoint spring,serverless,wcf soap,wcf cat,cool math games,wcf test client,services & training hse colombia sas,servicestack,citibank,asp.net core identity,sql union,ajax parking,sql database,asp.net core logging,mvconnect,cunyfirst,asp.net guida,wcf nba,csharp download,wcfi foundation,csharp online,wcf authentication,tutorials near me,http://asp.net,server 2019,chernobyl,ado.net descargar,web of dreams,serverminer,ajax cleaner,ado.net visual studio 2019,webassign,ado.net vs entity framework,ado.net visual studio 2017,csharp list,sql like,asp.net mvc,asp.net core tutorial,sqlite,wcfm,ajax roster,mvc architecture,http://ado.net,asp.net core mvc,ajax soccer,server hosting,wcfi,ajax dish soap,capital one,server rack,tutorialspoint html,csharp interface,craigslist,webroot,tutorialspoint reactjs,ajax request,wcf dragon ball,asp.net core 2.2,tutorialspoint python 3,sql developer,webster,services transmission company sas,sql group by,asp.net core signalr,services manager,mvc framework,ajax paving,mvc near me,tutorialspoint spring boot,mvc map,csharp online compiler,asp.net download,sql between,ado.net c# pdf,services tag dell,wcf 2019 nba,csharp switch,ado.net ventajas y desventajas,csharpstar,wcf tutorial,tutorialspoint,ajax meaning,csharp-video-tutorials.blogspot,tutorials dojo,central park 5,csharp string format,ado.net c#,asp.net core github,server status,ajax fc,server jobs nyc,asp.net core swagger,sql formatter,credit karma,services group,server error in '/' application,services windows,asp.net core 3.0,sql injection,tutorialspoint c#,wcf ria services,calculator,ado.net entity data model,sql insert,tutorialspoint tableau,services google play apk,sqlyog,asp.net core 3 release date,sql server,server job description,tutorials by a,servicenow,webcam,mvc hours,webmd symptom,csharp array,csharp enum,ajax call,asp.net core 2. guida completa per lo sviluppatore,asp.net core,server pro,server status ffxiv,cheap flights,webcrims,asp.net core hosting,services sas,tutorialspoint java,tutorialspoint java compiler,webmd,csharp to json,college football,ado.net dataset,csharp dictionary,cnn,website builder,tutorialspoint sql,asp.net web api,server side rendering,weber grills,sql server 2017,tutorialspoint spark,mvc nj,ado.net oracle,asp.net core download,csharp to vb.net,webster bank,webstaurant,tutorialsystems,ajax post,services fms publish announcement,services.msc no abre,ajax jersey,csharp operator,asp.net core razor pages,server duties,asp.net core environment variables,csharp random,century 21,services consultores,services consulting,mvcsd,services.msc,mvcsc,ado.net pdf,asp.net core configuration,ajax marvel,sql update,asp.net tutorial,mvc medical,ado.net entity data model visual studio 2019,wcfs international curriculum,mvc2,ado.net entity data model visual studio 2017,chase online,wcf api,costco,server jobs near me,webex,sql meaning,sql tutorial,sql commands,ado.net entity framework,ajax tavern,tutorialsteacher,ajax javascript,services desk,ajax greek,csharp tutorial,mvc pattern,ado.net sql server,ado.net connection,asp.net identity,mvcu,asp.net core middleware,wcf web service,mvc webadvisor,web store,mvcc,webmail,mvci,mvctc,