asp.net core docker exec vs sp executesql in sql server

Text version of the video
http://csharp-video-tutorials.blogspo...

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

Dot Net & SQL Server Tutorials
https://www.youtube.com/user/kudvenka...

In this video we will discuss the difference between exec and sp_executesql.

In SQL Servere we have 2 options to execute dynamic sql
1. Exec/Execute
2. sp_executesql

We discussed sp_executesql in detail in Part 138 of SQL Server tutorial. Please check out that video if you are new to sp_executesql.

If you do a quick search on the internet for the difference between exec and sp_executesql, you will see that many articles on the web states using exec over sp_executesql will have the following 2 problems
1. It open doors for sql injection attacks
2. Cached query plans may not be reused and leads to poor performance

This is generally true, but if you use QUOTENAME() function you can avoid sql injection attacks and with sql server auto-parameterisation capability the cached query plans can be reused so performance is also not an issue. Let's understand these with examples.

What is exec() in SQL Server
Exec() or Execute() function is used to execute dynamic sql and has only one parameter i.e the dynamic sql statement you want to execute.

As you can see in the example below, we are concatenating strings to build dynamic sql statements which open doors for sql injection.

Declare @FN nvarchar(50)
Set @FN = 'John'
Declare @sql nvarchar(max)
Set @sql = 'Select * from Employees where FirstName = ''' + @FN + ''''
Exec(@sql)

If we set @FN parameter to something like below, it drops SalesDB database

Declare @FN nvarchar(50)
Set @FN = ''' Drop Database SalesDB --'''
Declare @sql nvarchar(max)
Set @sql = 'Select * from Employees where FirstName = ''' + @FN + ''''
Exec(@sql)

However, we can prevent SQL injection using the QUOTENAME() function as shown below.

Declare @FN nvarchar(50)
Set @FN = ''' Drop Database SalesDB --'''
Declare @sql nvarchar(max)
Set @sql = 'Select * from Employees where FirstName = ' + QUOTENAME(@FN,'''')
--Print @sql
Exec(@sql)

Notice with the quotename function we are using a single quote as a delimiter. With the use of this function if there is a single quote in the user input it is doubled.

For example, if we set @FN='John', notice the string 'John' is wrapped in single quotes

Declare @FN nvarchar(50)
Set @FN = 'John'
Declare @sql nvarchar(max)
Set @sql = 'Select * from Employees where FirstName = ' + QUOTENAME(@FN,'''')
Print @sql

When the above query is executed the following is the query printed
Select * from Employees where FirstName = 'John'

Along the same lines, if we try to inject sql, QUOTENAME() function wraps all that input in another pair of single quotes treating it as a value for the FirstName column and prevents SQL injection.

With sql server auto-parameterisation capability the cached query plans can be reused. SQL Server can detect parameter values and create parameterised queries on its own, even if you don't explicitly declare them. However, there are exceptions to this. Auto-parameterisation comes in 2 flavours - Simple and Forced. We will discuss auto-parameterisation in detail in a later video.

Execute the following DBCC command to remove all entries from the plan cache
DBCC FREEPROCCACHE

Execute the following query. Notice we have set @FN='Mary'
Declare @FN nvarchar(50)
Set @FN = 'Mary'
Declare @sql nvarchar(max)
Set @sql = 'Select * from Employees where FirstName = ' + QUOTENAME(@FN,'''')
Exec(@sql)

Execute the following query to retrieve what we have in the query plan cache
SELECT cp.usecounts, cp.cacheobjtype, cp.objtype, st.text, qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY cp.usecounts DESC

Notice we have an auto-parameterised query and at the moment usecounts is 1.

Now change @FN='Mark' and execute the same query. After the query is completed, retrieve the entries from the plan cache. Notice the usecounts for the auto-parameterised query is 2, suggesting that the same query plan is reused.

Along the same lines, if you change @FN='John' and execute the query, you will see that the usecounts is now 3 for the auto-parameterised query.

Summary
1. If you use QUOTENAME() function, you can prevent sql injection while using Exec()
2. Cached query plan reusability is also not an issue while using Exec(), as SQL server automatically parameterize queries.
3. I personally prefer using sp_executesql over exec() as we can explicitly parameterise queries instead of relying on sql server auto-parameterisation feature or QUOTENAME() function. I use Exec() only in throw away scripts rather than in production code.

asp.net core docker exec vs sp executesql in sql server
152 Likes	152 Dislikes
22,473 views views	524K followers
Science & Technology	Upload TimePublished on 18 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,