Return to HomePage

---

Note: This document is live on MSDN! See http://msdn.microsoft.com/library/en-us/dnpag2/html/PAGHT000002.asp

How To: Protect from SQL Injection in ASP.NET

J.D. Meier, Alex Mackman, Blaine Wastell, Prashant Bansode, Andy Wigley
Microsoft Corporation
May 2005

Applies To:

* ASP.NET 1.1
* ASP.NET 2.0

Summary

This How To explains a number of ways to protect your ASP.NET application from SQL injection. Techniques include constraining input and using type-safe SQL parameters in your data access code.

Contents

* Overview
* Summary of Steps
* Step 1: Constrain Input
* Step 2: Use the Parameters Collection with Stored Procedures
* Step 3: Use the Parameters Collection with Dynamic SQL
* Additional Considerations
* Summary
* Additional Resources

Overview

A successful SQL injection attack enables a malicious user to execute commands in your application's database by using the privileges granted to your application's login. The problem is more severe if your application uses an over-privileged account to connect to the database. For example, if your application's login has privileges to eliminate a database, then without adequate safeguards, an attacker might be able to perform this operation.
Common vulnerabilities that make your data access code susceptible to SQL injection attacks include:
* Weak input validation
* Dynamic construction of SQL statements without the use of type-safe parameters
* Use of over-privileged database logins

SQL Injection Example

Consider what happens when a user types the following string in the SSN text box, which is expecting a Social Security number of the form nnn-nn-nnnn.

		 ' ; DROP DATABASE pubs  --
	

Using the input, the application executes the following dynamic SQL statement or stored procedure, which internally executes a similar SQL statement.

		 // Use dynamic SQL
		 [SqlDataAdapter] myCommand = new [SqlDataAdapter(]
		           "SELECT au_lname, au_fname FROM authors WHERE au_id = '" + 
		           SSN.Text + "'", myConnection);
	

		 // Use stored procedures
		 [SqlDataAdapter] myCommand = new [SqlDataAdapter(]
		                                 "LoginStoredProcedure '" + 
		                                  SSN.Text + "'", myConnection);
	

The developer's intention was that when the code runs, it inserts the user's input and generates a SQL statement of the form:

		 SELECT au_lname, au_fname FROM authors WHERE au_id = '172-32-9999'
	

However, the code inserts the user's malicious input and generates the following query:

		 SELECT au_lname, au_fname FROM authors WHERE au_id = ''; DROP DATABASE pubs --'
	

In this case, the ' (single quotation mark) character that starts the rogue input terminates the current string literal in the SQL statement. It closes the current statement only if the following parsed token does not make sense as a continuation of the current statement, but does make sense as the start of a new statement. As a result, the opening single quotation mark character of the rogue input results in the following statement:

		 SELECT au_lname, au_fname FROM authors WHERE au_id = ''
	

The ; (semicolon) character tells SQL that this is the end of the current statement, which is then followed by the malicious SQL code:

		 ; DROP DATABASE pubs
	

Note: The semicolon is not necessarily required to separate SQL statements. This is vendor or implementation dependent, but SQL Server does not require them. For example, SQL Server parses the following as two separate statements: *SELECT * FROM ""MyTable"" DELETE FROM ""MyTable""*

Finally, the -- (double dash) sequence of characters is a SQL comment that tells SQL to ignore the rest of the text. In this case, SQL ignores the closing ' (single quotation mark) character, which would otherwise cause a SQL parser error.

		 --'
	

Guidelines

To counter SQL injection attacks, you need to:
* Constrain and sanitize input data. Check for known good data by validating for type, length, format, and range.
* Use type-safe SQL parameters for data access. You can use these parameters with stored procedures or dynamically constructed SQL command strings. Parameter collections such as SqlParameterCollection provide type checking and length validation. If you use a parameters collection, input is treated as a literal value, and SQL Server does not treat it as executable code. An additional benefit of using a parameters collection is that you can enforce type and length checks. Values outside of the range trigger an exception. This is a good example of defense in depth.
* Use an account that has restricted permissions in the database. Ideally, you should only grant execute permissions to selected stored procedures in the database and provide no direct table access.
* Avoid disclosing database error information. In the event of database errors make sure you do not disclose detailed error messages to the user.


Note: Conventional security measures, such as the use of Secure Socket Layer (SSL) and IP Security (""IPSec""), do not protect your application from SQL injection attacks.

Summary of Steps

To protect your application from SQL injection, perform the following steps:
* Step 1: Constrain input.
* Step 2: Use parameters with stored procedures.
* Step 3: Use parameters with dynamic SQL.

Step 1: Constrain Input

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

Constrain Input in ASP.NET Web Pages

Start by constraining input in the server-side code for your ASP.NET Web pages. Do not rely on client-side validation because it can be bypassed easily. Use client-side validation only to reduce round trips and to improve the user experience.
If you use server controls, use the ASP.NET validator controls, such as the RegularExpressionValidator and RangeValidator controls to constrain input. If you use regular HTML input controls, use the RegEx class in your server-side code to constrain input.
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 here:

		 <%@ 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 input is from another source, such as an HTML control, a query string parameter, or a cookie, you can constrain it by using the Regex class from the System.Text.RegularExpressions namespace. 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 database
		 }
		 else
		 {
		     // handle the bad input
		 }
	

For more information about how to constrain input in your ASP.NET Web pages, see How To: Protect from Injection Attacks in ASP.NET.

Constrain Input in Data Access Code

In some situations, you need to provide validation in your data access code, perhaps in addition to your ASP.NET page-level validation. Two common situations where you need to provide validation in your data access code are:
* Untrusted clients. If the data can come from an untrusted source or you cannot guarantee how well the data has been validated and constrained, then add validation logic that constrains input to your data access routines.
* Library code. If your data access code is packaged as a library designed for use by multiple applications, your data access code should perform its own validation, because you can make no safe assumptions about the client applications.

The following example shows how a data access routine 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 case or upper case letters, 
		     // the apostrophe, a dot, or white space. Also check it is 
		     // between 1 and 40 characters long
		     if ( !Regex.IsMatch(userIDTxt.Text, @"^[a-zA-Z'./s]{1,40}$"))
		       throw new FormatException("Invalid name format");
	

		     // Check password contains at least one digit, one lower case 
		     // letter, one uppercase letter, and is between 8 and 10 
		     // characters long
		     if ( !Regex.IsMatch(passwordTxt.Text, 
		                       @"^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{8,10}$" ))
		       throw new FormatException("Invalid password format");
	

		     // Perform data access logic (using type safe parameters)
		     …
		 }
	

Step 2: Use Parameters with Stored Procedures

Using stored procedures does not necessarily prevent SQL injection. The important thing to do is use parameters with stored procedures. If you do not use parameters, your stored procedures can be susceptible to SQL injection if they use unfiltered input as described in the Overview section of this document.
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 parameter is treated as a literal value and not as executable code. Also, the parameter is type and length checked. In the example, the input value cannot be longer than 11 characters. If the data does not conform to the type or length defined by the parameter, the SqlParameter class throws an exception.

Review Your Application's Use of Parameterized Stored Procedures

Using stored procedures with parameter does not necessarily prevent SQL Injection, so you should review your application's use of this type of stored procedure. For example, the following parameterized stored procedure has several security vulnerabilities.

		 CREATE PROCEDURE [dbo.RunQuery]
		 @var ntext
		 AS
		   EXEC @var
		 GO
	

An application that uses a stored procedure similar to the one shown above has the following vulnerabilities:
* The stored procedure executes whatever statement is passed to it. Consider the @var variable being set to the following, which causes the ORDERS table to be dropped:

		 DROP TABLE ORDERS;
	

* The stored procedure runs with dbo privileges.
* The stored procedure's name (RunQuery) is a poor choice. If an attacker is able to probe the database, he will see the name of the stored procedure. With a name like RunQuery, he can guess that the stored procedure is likely to run the supplied query.

Step 3: Use Parameters with Dynamic SQL

If you cannot use stored procedures, you should still use parameters when constructing dynamic SQL statements. The following code shows how to use SqlParametersCollection with dynamic SQL.

		 using System.Data;
		 using [System.Data.SqlClient;]
	

		 using [(SqlConnection] connection = new [SqlConnection(connectionString))]
		 {
		   [DataSet] userDataset = new [DataSet();]
		   [SqlDataAdapter] [myDataAdapter] = new [SqlDataAdapter(]
		          "SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id", 
		          connection);                
		   myCommand.SelectCommand.Parameters.Add("@au_id", [SqlDbType.VarChar,] 11);
		   myCommand.SelectCommand.Parameters[“@au_id"].Value = SSN.Text;
		   [myDataAdapter.Fill(userDataset);]
		 }
	

Using Parameter Batching

A common misconception is that if you concatenate several SQL statements to send a batch of statements to the server in a single round trip, then you cannot use parameters. 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 parameter 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);
		 }
		  . . .
	

Additional Considerations

Other things to consider when you develop countermeasures to prevent SQL injection include:
* Use a least-privileged database account.
* Avoid disclosing error information.

Use a Least-Privileged Database Account

Your application should connect to the database by using a least-privileged account. If you use Windows authentication to connect, the Windows account should be least-privileged from an operating system perspective and should have limited privileges and limited ability to access Windows resources. Additionally, whether or not you use Windows authentication or SQL authentication, the corresponding SQL Server login should be restricted by permissions in the database.
Consider the example of an ASP.NET application running on Windows Server 2003 that accesses a database on a different server in the same domain. The ASP.NET application runs by default in an application pool that runs under the Network Service account. This account is a least privileged account.

To access SQL Server with the Network Service account

1. Create a SQL Server login for the Web server's Network Service account. The Network Service account has network credentials that are presented at the database server as the identity DOMAIN\WEBSERVERNAME$. For example, if your domain is called XYZ and the Web server is called 123, you create a database login for XYZ\123$.
2. Grant the new login access to the required database by creating a database user and adding the user to a database role.
3. Establish permissions to let this database role call the required stored procedures or access the required tables in the database. Only grant access to stored procedures the application needs to use, and only grant sufficient access to tables based on the application's minimum requirements. For example, if the ASP.NET application only performs database lookups and does not update any data, then you only need to grant read access to the tables. This limits the damage that an attacker can cause if the attacker succeeds in a SQL Injection attack.

For more information about how to connect to the database by using a least-privileged account, see How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0.

Avoid Disclosing Error Information

Use structured exception handling to catch errors and prevent them from propagating back to the client. Log detailed error information locally, but return limited error details to the client.
If errors occur while the user is connecting to the database, be sure that you provide only limited information about the nature of the error to the user. If you disclose information related to data access and database errors, you could provide a malicious user with useful information that he or she can use to compromise your database security. Attackers use the information in detailed error messages to help deconstruct a SQL query that they are trying to inject with malicious code. A detailed error message may reveal valuable information such as the connection string, SQL server name, or table and database naming conventions.

Summary

SQL injection can occur when an application uses input to construct dynamic SQL statements or when it uses stored procedures to connect to the database. Successful SQL injection attacks enable malicious user to execute commands in an application's database.
To counter SQL injection attacks, you need to constrain input data, use type-safe SQL parameters for data access, and use a least-privileged account that has restricted permissions in the database. Conventional security measures, such as the use of SSL and ""IPSec"", do not protect your application from SQL injection attacks.

How To: Use Regular Expressions to Constrain Input in ASP.NET 2.0.
How To: Protect From Injection Attacks in ASP.NET 2.0.
How To: Prevent Cross Site Scripting in ASP.NET 2.0
How To: Use Code Access Security with ASP.NET 2.0

---

Comments

<< Add your commments here - you need to login to Channel9 >>

---

Return to HomePage