Dynamic sql table name variable

Text version of the video
http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-table-name-variable.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-table-name-variable_20.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

In this video we will discuss how to pass table name dynamically for stored procedure in sql server. This is one of the sql questions that is very commonly asked.

I have a web page with a textbox. When I enter a table name in the textbox and when I click “Load Data” button, we want to retrieve data from that respective table and display it on the page.

Copy the SQL Script to create the tables from my blog using the link below
http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-table-name-variable.html

Create the following stored procedure. Notice we are passing table name as a parameter to the stored prcoedure. In the body of the stored procedure we are concatenating strings to build our dynamic sql statement. In our previous videos we discussed that this open doors for SQL injection.

Create procedure spDynamicTableName
@TableName nvarchar(100)
As
Begin
Declare @sql nvarchar(max)
Set @sql = ‘Select * from ‘ + @TableName
Execute sp_executesql @sql
End

So the obvious question that comes to our mind is, why are we not creating parameterised sql statement instead. The answers is we can’t. SQL Server does not allow table names and column names to be passed as parameters. Notice in the example below, we are creating a parameterised query with @TabName as a parameter. When we execute the following code, the procedure gets created successfully.

Create procedure spDynamicTableName1
@TableName nvarchar(100)
As
Begin
Declare @sql nvarchar(max)
Set @sql = ‘Select * from @TabName’
Execute sp_executesql @sql, N’@TabName nvarchar(100)’, @TabName = @TableName
End

But when we try to execute it we get an error – Must declare the table variable “@TabName”
Execute spDynamicTableName1 N’Countries’

Add a Web Page to the project that we have been working with in our previous video. Name it “DynamicTableName.aspx”. Copy and paste the HTML from my blog using the link below
http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-table-name-variable.html

Copy and paste the code from my blog in the code-behind page
http://csharp-video-tutorials.blogspot.com/2017/04/dynamic-sql-table-name-variable.html

At this point, run the application and type the following text in the “Table Name” textbox and click “Load Data” 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.
Employees; Drop database SalesDB

One way to prevent SQL injection in this case is by using SQL Server built-in function – QUOTENAME(). We will discuss QUOTENAME() function in detail in our next video. For now understand that by default, this function wraps that string that is passed to it in a pair of brackets.
SELECT QUOTENAME(‘Employees’) returns [Employees]

Modify the stored procedure to use QUOTENAME() function as shown below.

Alter procedure spDynamicTableName
@TableName nvarchar(100)
As
Begin
Declare @sql nvarchar(max)
Set @sql = ‘Select * from ‘ + QUOTENAME(@TableName)
Execute sp_executesql @sql
End

At this point, type the following text in the “Table Name” textbox and click “Load Data” button. Notice you will see a message – Invalid object name ‘Employees; Drop database SalesDB’. Also “SalesDB” database is not dropped.
Employees; Drop database SalesDB

The entire text in “Table Name” textbox is wrapped in a pair of brackets by the QUOTENAME function and is treated as table name. Since we do have a table with the specified name, we get the error – Invalid object name.