How Declare Dynamic SQL table name variable

Dynamic SQL table name variable

I have a web page with a textbox as shown below. 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.

SQL Script to create the required tables

Create table Countries (

Id int identity primary key,

CountryName nvarchar(50) ) Go

Insert into Countries values (‘USA’) Insert into Countries values (‘India’) Insert into Countries values (‘UK’) Insert into Countries values (‘Australia’) Insert into Countries values (‘Canada’) Go

Create table Employees (

ID int primary key identity,

FirstName nvarchar(50),

LastName nvarchar(50),

Gender nvarchar(50),

Salary int ) Go

Insert into Employees values (‘Mark’, ‘Hastings’, ‘Male’, 60000)

Insert into Employees values (‘Steve’, ‘Pound’, ‘Male’, 45000)

Insert into Employees values (‘Ben’, ‘Hoskins’, ‘Male’, 70000)

Insert into Employees values (‘Philip’, ‘Hastings’, ‘Male’, 45000)

Insert into Employees values (‘Mary’, ‘Lambeth’, ‘Female’, 30000)

Insert into Employees values (‘Valarie’, ‘Vikings’, ‘Female’, 35000)

Insert into Employees values (‘John’, ‘Stanmore’, ‘Male’, 80000) Go

Create the following stored procedure. Notice we are passing table name as a parameter to the stored procedure. 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)

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 following HTML on the page.

<html xmlns=”http://www.w3.org/1999/xhtml”> <head runat=”server”> <title>Employee Search</title> <link rel=”stylesheet” href=”https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css” type=”text/css” /> </head> <body style=”padding-top: 10px”> <div class=”col-xs-8 col-xs-offset-2″> <form id=”form1″ runat=”server” class=”form-horizontal”> <div class=”panel panel-primary”> <div class=”panel-heading”> <h3>Table Lookup</h3> </div> <div class=”panel-body”> <div class=”form-group”> <label for=”inputTableName” class=”control-label col-xs-4″> Table Name </label> <div class=”col-xs-8″> <input type=”text” runat=”server” class=”form-control” id=”inputTableName” placeholder=”Please enter table name” /> </div> </div> <div class=”form-group”> <div class=”col-xs-10 col-xs-offset-2″> <asp:Button ID=”btnLoadData” runat=”server” Text=”Load Data” CssClass=”btn btn-primary” OnClick=”btnLoadData_Click” />

<asp:Label ID=”lblError” runat=”server” CssClass=”text-danger”> </asp:Label> </div> </div> </div> </div>
<div class=”panel panel-primary”> <div class=”panel-heading”> <h3>Table Data</h3> </div> <div class=”panel-body”> <div class=”col-xs-10″> <asp:GridView CssClass=”table table-bordered” ID=”gvTableData” runat=”server”> </asp:GridView> </div> </div> </div> </form> </div> </body> </html>

Copy and paste the following code in the code-behind page.

using System; using System.Configuration; using System.Data; using System.Data.SqlClient;
namespace DynamicSQLDemo {     public partial class DynamicTableName : System.Web.UI.Page     {         protected void btnLoadData_Click(object sender, EventArgs e)         {             try             {                 if (inputTableName.Value.Trim() != “”)                 {                     string strConnection = ConfigurationManager                    .ConnectionStrings[“connectionStr”].ConnectionString;
                    using (SqlConnection con = new SqlConnection(strConnection))                     {                         SqlCommand cmd = new SqlCommand();                         cmd.Connection = con;                         cmd.CommandText = “spDynamicTableName”;                         cmd.CommandType = CommandType.StoredProcedure;
                        SqlParameter param = new                             SqlParameter(“@TableName”, inputTableName.Value);                         param.SqlDbType = SqlDbType.NVarChar;                         param.Size = 100;                         cmd.Parameters.Add(param);
                        con.Open();                         SqlDataReader rdr = cmd.ExecuteReader();                         gvTableData.DataSource = rdr;                         gvTableData.DataBind();                   }                 }                 lblError.Text = “”;             }             catch (Exception ex)             {                 lblError.Text = ex.Message;             }         }     } }
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.