What is SQL Injection Attack

                                                                                                                                         Previous.....
                                                                                                                                                      Next.....

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

Here is a youtube video that I have recorded on SQL Injection. Hope, you will find it useful.

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

Explain Dependency Injection with an example

One of the very common interview questions, asked these days. This is the most common approach used today to solve dependencies between objects. In many of the enterprise class ASP.NET application, Dependency Injection is a common standard to follow. Let us understand Dependency Injection with an example.

In the example above, Employee class depends on EmployeeDAL class to get the data from the database. In GetAllEmployees() method of theEmployee class, we create an instance of the EmployeeDAL (Employee Data Access Layer) class and then invoke SelectAllEmployees() method. This is tight coupling, EmployeeDAL is tightly copuled with the Employee class. Everytime the EmployeeDAL class changes, the Employee class also needs to change. EmployeeDAL cannot be mocked and hence unit testing becomes cumbersome and time consuming. 

The same example can be re-written using dependency injection as shown below. First thing to notice is that, we are using interface types instead of concrete types. Using interfaces help us to plugin any implemenation of the interface, with less or no code modification at all. We are not creating the instance of the EmployeeDAL in the Employee class, instead we are passing it as a parameter to the constructor of the Employee class. As, we are injecting an instance of a class into a class that depends on it, we can call this process as Dependency Injection.

Dependency Injection is of 2 types.
1. Constructor Injection
2. Setter Injection.

We have already seen how to use Constructor Injection in the example above. An, example for Setter Injection is shown below. We are injecting an object instance through the Setter property, instead of a constructor. Hence, we call Setter Injection. It is very important to use the propertyEmployeeDataObject to access the instance of IEmployeeDAL, rather than the private variable employeeDAL. The property checks to see ifemployeeDAL is null, and throws the exception accordingly.

You can also see this link for SQL injection.. http://venkataspinterview.blogspot.in/2011/07/what-is-sql-injection-attack.html