Prevent sql injection with dynamic sql

Suggested Videos
Part 138 - Dynamic SQL in SQL Server
Part 139 - Implement search web page using ASP.NET and Stored Procedure
Part 140 - Implement search web page using ASP.NET and Dynamic SQL

In this video we will discuss, how to prevent SQL injection when using dynamic SQL. This is continuation to Part 140. Please watch Part 140 from SQL Server Tutorial before proceeding.

In Part 140, we have implemented "Search Page" using dynamic SQL. Since we have used parameters to build our dynamic SQL statements, it is not prone to SQL Injection attack. This is an example of good dynamic SQL implementation.



I have seen lot of software developers, not just the beginners but even experienced developers, buidling their dynamic sql queries by concatenating strings instead of using parameters without realizing that they are opening the doors for SQL Injection.



Here is an example of bad dynamic SQL that is prone to SQL Injection

using System;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Text;

namespace DynamicSQLDemo

{

    public partial class SearchPageWithDynamicSQL : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        { }

        protected void btnSearch_Click(object sender, EventArgs e)

        {

            string strConnection = ConfigurationManager

                .ConnectionStrings["connectionStr"].ConnectionString;

            using (SqlConnection con = new SqlConnection(strConnection))

            {

                SqlCommand cmd = new SqlCommand();

                cmd.Connection = con;

                StringBuilder sbCommand = new

                    StringBuilder("Select * from Employees where 1 = 1");

                if (inputFirstname.Value.Trim() != "")

                {

                    sbCommand.Append(" AND FirstName = '" +

                        inputFirstname.Value + "'");

                }

                if (inputLastname.Value.Trim() != "")

                {

                    sbCommand.Append(" AND LastName = '" +

                        inputLastname.Value + "'");

                }

                if (inputGender.Value.Trim() != "")

                {

                    sbCommand.Append(" AND Gender = '" +

                        inputGender.Value + "'");

                }

                if (inputSalary.Value.Trim() != "")

                {

                    sbCommand.Append(" AND Salary = " + inputSalary.Value);

                }

                cmd.CommandText = sbCommand.ToString();

                cmd.CommandType = CommandType.Text;

                con.Open();

                SqlDataReader rdr = cmd.ExecuteReader();

                gvSearchResults.DataSource = rdr;

                gvSearchResults.DataBind();

            }

        }

    }

}

Since we are  concatenating the user input values to build the dynamic sql statement, the end user can very easily inject sql. Imagine, what happens for example, if the user enters the following in the "Firstname" textbox.

' Drop database SalesDB --

With the above SQL injected into the "Firstname" textbox, if you click the "Search" button, the following is the query which is sent to SQL server. This will drop SalesDB database.

Select * from Employees where 1 = 1 AND FirstName = '' Drop database SalesDB --'

On the other hand, if you use parameters to build your dynamic SQL statements, SQL Injection is not an issue. The following input in the "Firstname" textbox, would not drop the SalesDB database.

' Drop database SalesDB --

The text the user has provided in the "Firstname" textbox is treated as the value for @Firstname parameter. The following is the query that is generated and executed.

exec sp_executesql N'Select * from Employees where 1 = 1 AND FirstName=@FirstName',N'@FirstName nvarchar(26)',@FirstName=N''' Drop database SalesDB --'

We don't have this problem of sql injection if we are using stored procedures. "SearchPageWithoutDynamicSQL.aspx" is using the stored procedure "spSearchEmployees" instead of dynamic SQL. The same input in the "Firstname" texbox on this page, would generate the following. Notice, whatever text we typed in the "Firstname" textbox is treated as the value for @FirstName parameter.

exec spSearchEmployees @FirstName=N''' Drop database SalesDB --'

An important point to keep in mind here is that if you have dynamic SQL in your stored procedure, and you are concatenating strings in that stored procedure to build your dynamic sql statements instead of using parameters, it is still prone to SQL injection. If this is not clear at the moment don't worry, we will discuss an example of this in out next video.

So in summary, while dynamic sql provides great flexibility when implementing complicated logic with lot of permutations and combinations, if not properly implemented it may open doors for sql injection. Always use parameters to build dynamic sql statements, instead of concatenating user input values.

Another benefit of using parameters to build dynamic sql statements is that it allows cached query plans to be reused, which greatly increases the performance. We will discuss an example of this in our upcoming videos.