What is SQL Injection Attack

Let us understand SQL injection attack, with an example. I have an Employee Search Page as shown in the image below. This webform has a very simple functionality. You enter the ID of the employee, you want to search and click the Search Employee button. If a match is found in the database, we show the employee record in the GridView.

Employee Search Page

The HTML for the Employee Serach Page is shown below. As you can see from the HTML, the Employee Serach Page contains TextBox, Button and a GridView control.


Employee Search Page HTML


The codebehind page for the EmployeeSearchPage is shown below. 

Employee Search Page Code Behind

The Button1_Click event handler has the required ADO.NET code to get data from the database. This code is highly susceptible to sql injection attack and I will never ever have code like this in production environment. The second line in Button1_Click event handler, dynamically builds the sql query by concatenating the Employee ID that we typed into the TextBox. 

So, for example, if we had typed 2 into the Employee ID textbox, we will have a SQL query as shown below.
Select * from Employees where Id=2

If a malicious user, types something like 2; Delete from Employees into the TextBox, then we will have a SQL query as shown below.
Select * from Employees where Id=2; Delete from Employees

When this query is executed, we loose all the data in the Employees table. This is SQL Injection Attack, as the user of the application is able to inject SQL and get it executed against the database. It is very easy to avoid SQL Injection attacks by using either parameterized queries or using stored procedures.

You may be thinking, how will the user of the application know the name of the table. Well, one way is to simply guess or inject a sql syntax error. The injected SQL syntax error causes the page to crash and can possibly reveal the name of the table as shown below. However, proper exception handling and custom error pages can be used to prevent the end user from seeing the yello screen of death. The screen shot below shows the table name Employees.

Page crash revealing Employees table name

To solve SQL injection attack, create a Stored Procedure as shown below. 
Create Procedure spGetEmployees
@Id int
as
Begin
Select * from Employees where Id=@Id
End

Modify the codebehind page for the EmployeeSearchPage, to use the stored procedure as shown below 

using System; using System.Data; using System.Data.SqlClient; namespace TestWeb {     public partial class EmployeeSearch : System.Web.UI.Page     {         protected void Page_Load(object sender, EventArgs e)         {                      }         protected void Button1_Click(object sender, EventArgs e)         {             // Create the SQL Connection object.              SqlConnection con = new SqlConnection             ("server=localhost; database=TestDB; integrated security=SSPI");             // Create the SQL command object. Pass the stored procedure name              // as a parameter to the constructor of the SQL command class             SqlCommand cmd = new SqlCommand("spGetEmployees", con);             // Create the SQL parameter object, specifying the name and the value              // we want to pass to the SP.             SqlParameter paramId = new SqlParameter("@Id", txtEmployeeId.Text);             // Associate the Id parameter object with the command object, using             // parameters collection property of the SQL Command object.              cmd.Parameters.Add(paramId);             // Specify the command type as stored procedure. This tells the command             // object, that the command is a SQL stored procedure and not an adhoc sql query             cmd.CommandType = CommandType.StoredProcedure;             // Open the connection             con.Open();             // Execute the command and assign the returned results as the data source for              // the employyes girdview             gvEmployees.DataSource = cmd.ExecuteReader();             // Call the DataBind() method, to bind the results to the employees grid view control             gvEmployees.DataBind();             // Finally close the sql server connection object             con.Close();         }     } }