Dynamic SQL in Stored Procedure

Create Procedure spSearchEmployees

@FirstName nvarchar(100) = NULL,

@LastName nvarchar(100) = NULL,

@Gender nvarchar(50) = NULL,

@Salary int = NULL

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

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

<html xmlns=”http://www.w3.org/1999/xhtml”>

<title>Employee Search</title>

<h3>Employee Search Form</h3>

</div>

<label for=”inputFirstname” class=”control-label col-xs-2″> Firstname </label>

</div>

</div>
<div class=”form-group”>

<label for=”inputLastname” class=”control-label col-xs-2″> Lastname </label>

<div class=”col-xs-10″> .

</div>
<div class=”form-group”>

<label for=”inputGender” class=”control-label col-xs-2″> Gender </label>

</div>
<div class=”form-group”>

<label for=”inputSalary” class=”control-label col-xs-2″> Salary </label>

</div>

</div>
<div class=”panel panel-primary”>

<h3>Search Results</h3>

</div>

<asp:GridView CssClass=”table table-bordered” ID=”gvSearchResults” runat=”server”> </asp:GridView>

</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 DynamicSQLInStoredProcedure : System.Web.UI.Page {

protected void Page_Load(object sender, EventArgs e) {

}
        protected void btnSearch_Click(object sender, EventArgs e)         {             string connectionStr = ConfigurationManager                 .ConnectionStrings[“connectionStr”].ConnectionString;             using (SqlConnection con = new SqlConnection(connectionStr))             {                 SqlCommand cmd = new SqlCommand();                 cmd.Connection = con;                 cmd.CommandText = “spSearchEmployeesGoodDynamicSQL”;                 cmd.CommandType = CommandType.StoredProcedure;
                if (inputFirstname.Value.Trim() != “”)                 {                     SqlParameter param = new SqlParameter(“@FirstName”,                         inputFirstname.Value);                     cmd.Parameters.Add(param);                 }
                if (inputLastname.Value.Trim() != “”)                 {                     SqlParameter param = new SqlParameter(“@LastName”,                         inputLastname.Value);                     cmd.Parameters.Add(param);                 }
                if (inputGender.Value.Trim() != “”)                 {                     SqlParameter param = new SqlParameter(“@Gender”,                         inputGender.Value);                     cmd.Parameters.Add(param);                 }
                if (inputSalary.Value.Trim() != “”)                 {                     SqlParameter param = new SqlParameter(“@Salary”,                         inputSalary.Value);                     cmd.Parameters.Add(param);                 }
                con.Open();                 SqlDataReader rdr = cmd.ExecuteReader();                 gvSearchResults.DataSource = rdr;                 gvSearchResults.DataBind();             }         }     } }
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 susceptible 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 concatenating strings. Using parameters to create dynamic sql statements prevents sql injection.