This site is the archived OWASP Foundation Wiki and is no longer accepting Account Requests.
To view the new OWASP Foundation website, please visit https://owasp.org

OWASP Backend Security Project .NET Security Programming

From OWASP
Revision as of 15:00, 19 May 2008 by Carlo.pelliccioni (talk | contribs) (Case 1: INLINE QUERY)

Jump to: navigation, search

Overview

In this section is explained the best solution to avoid one of the most dangerous vulnerabilities of web applications, the sql injection on .NET programming

It will be analized the interaction between a web application written in ASP.NET 2.0 and a SQL Server 2005 data provider.

To understand what is a sql injection, just think about how it sounds. Infact is an “injection of sql code in a context of execution of sql code”.

So, the conditions needed to exploit a web application with this kind of flaw must be:

  • 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 a database called “ExampleDB” in which there are some tables. One of these tables is “Users”. From a web application is possible to query the database to extract information about the users through their name.

The project is simple and is made by one .aspx page with a textbox in which is possible 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 possible to create an aspx page So the focus is on the code that we have to write to interact with the database.

Description

Two approaches are likely: inline query or stored procedure.

Owasp bsp net 1.jpg

Case 1: Inline query

Inline queries are the queries in which is possible to compose a sql statement through string concatenation. By clicking on the first button, the execution of the OnClick event is generated, doing 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); 
           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:

       DbHelper dbHelper = new DbHelper();
       string connectionString = dbHelper.returnConnectionString();
       SqlConnection sqlConnection = new SqlConnection(connectionString); 

This section of code provides the sqlConnection, reading the connectionString from the Web.Config file. This is an important task for the application because it represents the entry point to the database, the credentials of the user that can authenticate on the ExampleDB.

Second Section:

       sqlConnection.Open(); 
       SqlCommand cmd = new SqlCommand("select Name,Surname,Code from  dbo.Users where Name LIKE '%" + 
                                       txtQueryInline.Text + "%'", sqlConnection);			                              
       cmd.CommandType = CommandType.Text; 

In this section is used the SqlCommand class, in which is written the sql code, concatenating with the text to search. The type of the SqlCommand is “Text”, so it's clear that the sql code is provided directly. This code is prone to sql injection, because we can manipulate the statement, injecting in the textbox, for example, the string:

'; sql statement --

where sql statement is any sql code (it is possible to drop tables, add users, reconfigure the xp_cmdshell, etc.)

Third Section:

           DataSet ds = new DataSet();
           SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
           sqlDataAdapter.Fill(ds, "ResultTable");
           gridresult.DataSource = ds;
           gridresult.DataBind();

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.

It is possible to improve this query avoiding sql injection, using the second form of interaction.

Case 2: PARAMETRIZED QUERY + STORED PROCEDURE VULNERABLE

By clicking on the second button, the execution of the OnClick event is generated, doing the following:

protected void btnQueryStoredVuln_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_SearchUserByNameVuln", sqlConnection);  
           cmd.CommandType = CommandType.StoredProcedure; 
           SqlParameter pName = new SqlParameter("@Name", SqlDbType.VarChar, 50);
           pName.Value = txtQueryStoredVuln.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               
       }
}
CREATE PROCEDURE [dbo].[USP_SearchUserByNameVuln]
      @Name varchar(50)
AS 
BEGIN 
      SET NOCOUNT ON;				
      DECLARE @StrSQL varchar(max)
      SET @StrSQL = 	
               'SELECT U.Name,U.Surname,U.Code
               FROM dbo.Users U 
               WHERE U.Name LIKE ' + '''%'+  @Name+  '%'''
      EXEC (@StrSql)	
END

This kind of stored procedure are not rare. In this case is possible to compose the statement in many ways using parameters, function and so on.

Altough it is a parametrized query with a stored procedure, as the code shows, it is possible to inject the same string

'; sql statement --

to inject a sql statement. Using the SQL Server function REPLACE, it is possible to “patch” the problem without rewrite the store procedure, replacing all the single quote with a couple of single quote.

ALTER PROCEDURE [dbo].[USP_SearchUserByNameVuln]
     @Name varchar(50)
AS
BEGIN
     SET NOCOUNT ON;				
     DECLARE @StrSQL varchar(max)
     SET @Name = REPLACE(@Name,'''','''''')
     SET @StrSQL = 	
           'SELECT U.Name,U.Surname,U.Code
           FROM dbo.Users U 
           WHERE U.Name LIKE ' + '''%'+  @Name+  '%'''
      EXEC (@StrSql)	
END

This second case explains the fact that the use of parametrized query with stored procedure not always resolve the security flaws caused by sql injection.

Case 3: PARAMETRIZED QUERY + STORED PROCEDURE NOT VULNERABLE

It is possible to modify the way to execute the same query, using parametrized query in conjunction with stored procedures. By clicking on the second button, the execution of the OnClick event is generated, doing 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               
       }
}

First Section:

       DbHelper dbHelper = new DbHelper(); 
       string connectionString = dbHelper.returnConnectionString(); 
       SqlConnection sqlConnection = new SqlConnection(connectionString);

This section is the same of the example above.

Second Section:

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

In this section is used the SqlCommand class as above, but, the type of the SqlCommand is “StoredProcedure” , so the sql code is not provided directly, but there is a procedure inside the database that makes the job. This procedure is called USP_SearchUserByNameNotVuln and accept a Varchar(50) parameter called @Name. The code of the stored is simply:

CREATE PROCEDURE [dbo].[USP_SearchUserByNameNotVuln]
      @Name varchar(50)
AS 
BEGIN 
      SET NOCOUNT ON;				
SELECT
      U.Name,
      U.Surname,
      U.Code
FROM 
      dbo.Users U 
WHERE
      U.Name LIKE '''%' + @Name + '%'''
END

Three steps for each parameter passed to the stored procedure are needed to use the SqlParameter class:

  • Instantiate the parameter with the right name and type used in the stored procedure
  • Assign the value (in this case the value given by the user in the textbox)
  • Add the parameter to the SqlCommand

When the sql command is executed, the parameters will be replaced with values specified by the SqlParameter object.

In conclusion, this kind of query is not prone to sql injection, because it is not possibile to build ad hoc sql statements, due to the correct use of Stored Procedure and the SqlParameter class.

Third Section:

           DataSet ds = new DataSet();
           SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
           sqlDataAdapter.Fill(ds, "ResultTable");
           gridresult.DataSource = ds;
           gridresult.DataBind();

This section is the same of the example above.

INPUT VALIDATION

Another point that is important to consider is the validation of the input. For example in a context in which a user has to insert (or select) some data in (or from) the database let’s think about the worst case, that is a user that can digit anything and submit anything to the server. To avoid this the only choiche is to validate the user's input. Two strategies are possible:

1. White list

2. Black list

The first solution answers at the condition: "deny all, except what is explicitly signed in the list". The second solution answers at the condition: "allow all, except what is explicitly signed in the list".

The best solution for the security of the application is try to implement a validation of the input based on the first case. This comes more simply with numeric input, range input or strings that follow a specific pattern (for example an e-mail or a date). It becomes more difficult for strings with a not specific pattern (for example strings inserted in a search engine) in which often only a solution based on a black list is reasonably possible.

In a Web Application, there are two kinds of input validation:

1. client validations

2. server validations

There are a couple of reasons to use both types of validation summarized in the following points:

  • client validations increase performance (the application doesn't postback to the server) but cause a false sense of security (the validation can be bypassed intercepting and manipulating the client request).
  • server validations make worse performance but increase the security, because the validation is made by the server.

In ASP.NET to realize these concepts there are the server web control Validators:

  • RequiredFieldValidator
  • CompareValidator
  • RangeValidator
  • RegularExpressionValidator
  • CustomValidator

Technically these objects realize both type of validations: that is if the client support Javascript, the Validator uses first the client validation, and after that the page is validated on then server side too. If the client doesn't support Javascript, the validation is made only on the server side. In this manner the application validate the input in a progressive mode and the design of the application doesn't follow a "Minimum-Denominator-Multiplier" .

To explain the concept in code, it's possible to analyze the CustomValidator, that is the higher generalization because it's possible to use a personalized validation logic.

CustomValidator

We can think symply to a textbox with a button, like the example above, in which it gives the way to search all the suppliers with a specific code (a string of 16 char) For security reasons it is imagined that a specific user can only see the suppliers that have a code in which the first 4 character are "PFHG".

Any other string that doesn't match this pattern has to be exclude from the search (white list approach)

Owasp bsp net 2.jpg

The XML part of the page is like that:

<asp:Label ID="lblQuerySearch" runat="server" Text="Digit the first four numbers"></asp:Label>
<asp:TextBox ID="txtQuerySearch" Width="5em" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidatorQuerySearch" runat="server" ErrorMessage="Required Code" 
ControlToValidate="txtQuerySearch"></asp:RequiredFieldValidator>
<asp:CustomValidator ID="CustomValidatorQuerySearch" runat="server" ErrorMessage="Wrong code"
            ControlToValidate="txtQuerySearch" OnServerValidate="ValidateCode"></asp:CustomValidator>

The control Label and Button are intuitive web controls. It's clear to see that two Validator have been applied to the textbox whose ID is "txtQuerySearch" . The Validators are:

  • RequiredFieldValidator: we don't accept an empty textbox when we submit our request to the server
  • CustomValidator: we would implement some custom logic to our textbox

In fact the CustomValidator tag has a particular event called "OnServerValidate" that we can hook to a custom callback function, whose code is executed on the server side. For this example it's like that:

protected void ValidateCode(object source, ServerValidateEventArgs args)
   {
       try
       {
           string textToValidate = args.Value;
           if (textToValidate.Equals("PFHG"))
               args.IsValid = true;
           else
               args.IsValid = false;
       }
       catch (Exception ex)
       {
           args.IsValid = false;
       }
   }

The function handles the argument "arg" that brings the text inserted by the user. If this text match with our pattern, the argument is valid and the server executes the code associated to the button, querying the database in the same manner seen above; however the code is now conditioned by the statement "if(Page.IsValid)".

protected void btnQuerySearch_OnCLick(object sender, EventArgs e)
   {
       DbHelper dbHelper = new DbHelper(); 
       string connectionString = dbHelper.returnConnectionString();
       SqlConnection sqlConnection = new SqlConnection(connectionString); 
       if (Page.IsValid)
       {
           try
           {
               sqlConnection.Open();
               SqlCommand cmd = new SqlCommand("USP_SearchUserByCode", sqlConnection); 
               cmd.CommandType = CommandType.StoredProcedure;              
               SqlParameter pCode = new SqlParameter("@Code", SqlDbType.VarChar, 4);
               pCode.Value = txtQuerySearch.Text;
               cmd.Parameters.Add(pCode);
               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   
           }
       }
   }

This is only an example but the use of the Validators can be very important in all the contexts in which the protection of a database from malicious input is needed.

References

Tools