Dynamic sql table name variable
Suggested Videos
Part 142 - Dynamic SQL in Stored Procedure
Part 143 - Sql server query plan cache
Part 144 - exec vs sp_executesql in sql server
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. Here is what we want to do.
Part 142 - Dynamic SQL in Stored Procedure
Part 143 - Sql server query plan cache
Part 144 - exec vs sp_executesql in sql server
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. Here is what we want to do.
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.
For the purpose of this demo, we will use the following 2 tables.
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)
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 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.
by :- kudvenkat
No comments:
Post a Comment