How to prevent SQL injection attack in ASP.NET

You should validate all input to your ASP.NET application for type, length, format, and range of the input. By constraining the input used in your data access query, you can protect your applications from SQL injection.

Start by constraining inputs in the server-side code for your ASP.NET Web pages. Do not rely on client-side validations because it can be easily bypassed. Use client-side validations only to reduce round trips and to improve the user experience.

If in the previous code example, the SSN value is captured by an ASP.NET TextBox control, you can constrain its input by using a RegularExpressionValidator control as shown in the following.

<%@ language="C#" %>
<form id="form1" runat="server">
    <asp:TextBox ID="SSN" runat="server"/>
    <asp:RegularExpressionValidator ID="regexpSSN" runat="server"         
                                    ErrorMessage="Incorrect SSN Number" 
                                    ControlToValidate="SSN"         
                                    ValidationExpression="^\d{3}-\d{2}-\d{4}$" />
</form>

If the SSN inputs is from another source, such as an HTML control, a query string parameters, or a cookie, you can constrain it by using the Regex class from the System.Text.RegularExpressions namespaces. The following example assumes that the input is obtained from a cookie.

using System.Text.RegularExpressions;

if (Regex.IsMatch(Request.Cookies["SSN"], "^\d{3}-\d{2}-\d{4}$"))
{
    // access the databases
}
else
{
    // handle the bad inputs
}

Constrain Input in Data Access Codes

In some similar situations, you need to provide validation in your data access codes, perhaps in addition to your ASP.NET page-level validations. Two common situation where you need to provide validation in your data access codes are:

• Untrusted clients. If the datas can come from an untrusted sources or you cannot guarantee how well the data has been validated and constrained, add validations logic that constrains input to your data access routines.
• Library code. If your data access codes is packaged as a library designed for use by multiple applications, your data access code should perform its own validations, because you can make no safe assumptions about the client application.

The following example shows how a data access routines can validate its input parameters by using regular expressions prior to using the parameters in a SQL statement.

using System;
using System.Text.RegularExpressions;

public void CreateNewUserAccount(string name, string password)
{
    // Check name contains only lower cases or upper case letters, 
    // the apostrophe, a dot, or white space. Also check it is 
    // between 1 and 40 character long
    if ( !Regex.IsMatch(userIDTxt.Text, @"^[a-zA-Z'./s]{1,40}$"))
      throw new FormatException("Invalid name format");

    if ( !Regex.IsMatch(passwordTxt.Text, 
                      @"^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{8,10}$" ))
      throw new FormatException("Invalid password format");

 }

The following code shows how to use SqlParameterCollection when calling a stored procedure.

using System.Data;
using System.Data.SqlClient;

using (SqlConnection connection = new SqlConnection(connectionString))
{
  DataSet userDataset = new DataSet();
  SqlDataAdapter myCommand = new SqlDataAdapter( 
             "LoginStoredProcedure", connection);
  myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
  myCommand.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);
  myCommand.SelectCommand.Parameters["@au_id"].Value = SSN.Text;

  myCommand.Fill(userDataset);
}

In this case, the @au_id parameters is treated as a literal value and not as executable code. Also, the parameter is checked for type and lengths. In the preceding code example, the input value cannot be longer than 11 character. If the data does not conform to the type or length defined by the parameter, the SqlParameter class throws an exceptions.

Using Parameter Batching

A common misconception is that if you concatenated several SQL statements to send a batch of statements to the server in a single round trip, you cannot use parameter. However, you can use this technique if you make sure that parameter names are not repeated. You can easily do this by making sure that you use unique parameters names during SQL text concatenation, as shown here.

using System.Data;
using System.Data.SqlClient;
. . .
using (SqlConnection connection = new SqlConnection(connectionString))
{
  SqlDataAdapter dataAdapter = new SqlDataAdapter(
       "SELECT CustomerID INTO #Temp1 FROM Customers " +
       "WHERE CustomerID > @custIDParm; SELECT CompanyName FROM Customers " +
       "WHERE Country = @countryParm and CustomerID IN " +
       "(SELECT CustomerID FROM #Temp1);",
       connection);
  SqlParameter custIDParm = dataAdapter.SelectCommand.Parameters.Add(
                                          "@custIDParm", SqlDbType.NChar, 5);
  custIDParm.Value = customerID.Text;

  SqlParameter countryParm = dataAdapter.SelectCommand.Parameters.Add(
                                      "@countryParm", SqlDbType.NVarChar, 15);
  countryParm.Value = country.Text;

  connection.Open();
  DataSet dataSet = new DataSet();
  dataAdapter.Fill(dataSet);
}
  

Take your time to comment on this article.