Revision as of 14:37, 11 May 2008

Overview

In this section we would like to explain what is the best solution for .NET programmer to avoid the sql injection when one of the most causes of attacking web applications.

In this context we will analize the interaction between a web application written in ASP.NET 2.0 and a SQL Server 2005 data provider.

If we try to understand what is sql injection, we have to thinking about the words “sql injection”. That is “injection of sql code in a context of execution of sql code”.

So we need both the conditions to try to exploit a web application with this kind of flaw:

• A particular point of the application that accepts input from the (malicious) user, input that will have an interaction with a database
• Input that we can manipulate in a particualr manner, injecting sql code

Imagine we have a database called “ExampleDB” in which we have some tables. One of these tables is “Users”. From a web application we want simply to query the database to extract information about the users through name.

The project is simple, one .aspx page with a textbox in which we have to insert the name of the user and the program will return the information, reading from ExampleDB. It's not important to specify how it's possibile to create an aspx page So the focus is the code that we have to write to interact with the database.

Description

We have two approach: inline query or stored procedure.

Case 1: INLINE QUERY

Inline queries are the queries in which we can compose a sql statement trough string concatenation. If we click the first button, we generate the execution of the OnClick event, that do the following:

protected void btnQueryInline_OnCLick(object sender, EventArgs e)
{
       DbHelper dbHelper = new DbHelper();
       string connectionString = dbHelper.returnConnectionString();
       SqlConnection sqlConnection = new SqlConnection(connectionString); 
       try
       {
           sqlConnection.Open(); 
           SqlCommand cmd = new SqlCommand("select Name,Surname,Code from  dbo.Users where Name LIKE '%"  + 					        txtQueryInline.Text + "%'", sqlConnection); 
           cmd.CommandType = CommandType.Text;
           DataSet ds = new DataSet();
           SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
           sqlDataAdapter.Fill(ds, "ResultTable");
           gridresult.DataSource = ds;
           gridresult.DataBind();
       }
       catch (SqlException ex)
       {
           throw ex;
       }
       finally
       {
           if(sqlConnection != null)
               sqlConnection.Close(); //close the connection               
       }
}

First Section: This section of code provide the sqlConnection, reading the connectionString from the Web.Config file. This is important for the application because it represents the entry point to the database, the credentials of the user that can authenticate to ExampleDB.

Second Section: After we have indicated the credentials, we want to provide the sql code. In this section we use the SqlCommand class, in which we write the sql code, concatenating with the text to search. The type of the SqlCommand is “Text”, so it's clear that we provide sql code directly. This code is prone to sql injection, because we can manipulate the statement inejcting in the textbox, for example, the string:

'; sql statement --

where sql statement is any sql code that we can imagine (we can drop tables, add users, reconfigure the xp_cmdshell ecc...)

Third Section: The third section is useful to represent the result set of the query. It uses the ADO.NET “Dataset”, and an intermediate class called SqlDataAdapter, that adapts the sql data in a form that can be used by the Dataset Object.

We can improve this query avoiding sql injection, using the second form of interaction

Case 2: PARAMETRIZED QUERY + STORED PROCEDURE NOT VULNERABLE

We can modify the manner to execute the same query, using parametrized query in conjunction with stored procedure. If we click the second button, we generate the execution of the OnClick event, that do the following:

protected void btnQueryStored_OnCLick(object sender, EventArgs e)
{
       DbHelper dbHelper = new DbHelper(); 
       string connectionString = dbHelper.returnConnectionString();
       SqlConnection sqlConnection = new SqlConnection(connectionString); 
       try
       {                       
           sqlConnection.Open();
           SqlCommand cmd = new SqlCommand("USP_SearchUserByNameNotVuln", sqlConnection);  
           cmd.CommandType = CommandType.StoredProcedure; 
           SqlParameter pName = new SqlParameter("@Name", SqlDbType.VarChar, 50);
           pName.Value = txtQueryStored.Text;
           cmd.Parameters.Add(pName);
           DataSet ds = new DataSet();
           SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
           sqlDataAdapter.Fill(ds, "ResultTable");
           gridresult.DataSource = ds;
           gridresult.DataBind();
       }
       catch (SqlException ex)
       {
           throw ex;
       }
       finally
       {
           if(sqlConnection != null)
               sqlConnection.Close(); //close the connection               
       }
}

References

Tools