Revision as of 14:25, 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               
       }

}

References

Tools