ADO.NET 2.0 Security Guidelines - SQL Injection

From Guidance Share

Jump to: navigation, search

- J.D. Meier, Alex Mackman, Blaine Wastell, Prashant Bansode, Chaitanya Bijwe


Constrain and Sanitize Input Data

Check for known good data by validating for type, length, format, and range. If you do not expect numeric values, then do not accept them. Where possible, reject entries that contain binary data, escape sequences, and comment characters.

Consider where the input comes from. If it is from a trusted source that you know has performed thorough input validation, you might choose to omit data validation in your data access code. If the data is from an untrusted source or for additional protection, your data access methods and components should validate input.

Use Type-Safe SQL Parameters for Data Access

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. You can use type-safe SQL 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.

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

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

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

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", 
 myDataAdapter.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);
 myDataAdapter.SelectCommand.Parameters["@au_id"].Value = SSN.Text;

Avoid Dynamic Queries that Accept Untrusted Input

Avoid creating dynamic queries directly from user input because this increases the risk of SQL injection. If the whole query or part of the query is built from user input, then malicious input can easily result in modified queries.

Constraining input might not be effective with dynamic queries, so you should avoid creating dynamic queries if the input is untrusted. Even the use of stored procedures cannot guarantee protection from attacks if the stored procedure creates dynamic queries within it. An example could be a stored procedure which takes the whole Where clause as a parameter and then appends it to the rest of the query before executing it.

With Dynamic SQL, Use Character Escaping to Handle Special Input Characters

If you must use dynamic SQL and need to deal with input characters that have special meaning to SQL Server such as the single quote character, you need to use character escaping. This is sometimes necessary because in some situations, you may not be able to use parameterized SQL. For example, you might need to accept a table name or column name as a parameter.

Start by creating a list of known acceptable characters. Use a regular expression to reject any input that contains characters other than those defined in the list. Then, use an escape routine that inserts an escape character or character sequence in front of the special character to make it harmless. The escaping technique ensures that the character no longer has meaning to SQL Server and is considered to be normal text. The following code is an example of an escaping routine.

private string SafeSqlLiteral(string inputSQL)
 return inputSQL.Replace("'", ""); 

The following example shows how to use this routine to dynamically construct a query. Notice that the dynamic SQL statement is wrapped inside single quotation marks.

string strSQL = 
 "SELECT * FROM authors WHERE au_id = '" + 
 SafeSqlLiteral(Request.QueryString("input")) + "'";

Note If you use parameters with dynamic SQL or stored procedures, no further effort is required to handle special characters. Special characters do not pose a problem because the parameters are strongly typed and are not treated as executable SQL.

Use an Account that has Restricted Permissions in the Database

Use an account that has restricted database permissions to limit what an attacker can accomplish with SQL injection. The attacker is restricted by the permissions granted to your application's database login.

Use the following process to limit your application's login permissions in the database:

  1. Create a SQL Server login for the account.
  2. Map the login to a database user in the required database.
  3. Place the database user in a database role.
  4. Grant the database role limited permissions to only those stored procedures or tables that your application needs to access.

Ideally, provide no direct table access, and limit the application's access to selected stored procedures only. If you must grant table access, grant the minimum access that the application requires. For example, do not grant update access if read access is sufficient.

For more information about SQL injection, see "How To: Protect From SQL Injection in ASP.NET," at

Personal tools