What is SQL Injection Attack

SQL Injection Attack

Let us understand SQL injection attack, with an example. I have an 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.

The codebehind page for the EmployeeSearchPage is shown below.

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.

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();
}
}
}