SQL Injection

SQL injection attacks can occur when your application uses input to construct dynamic SQL statements to access the database. SQL injection attacks can also occur if your code uses stored procedures that are passed strings which contain unfiltered user input. SQL injection can result in attackers being able to execute commands in the database using the application login. The issue is magnified if the application uses an overprivileged account to connect to the database.

Note  

Conventional security measures, such as the use of SSL and IPSec, do not protect you against SQL injection attacks.

Preventing SQL Injection

Use the following countermeasures to prevent SQL injection attacks:

• Constrain input .

• Use type safe SQL parameters .

Constrain Input

Validate input for type, length, format, and range. If you do not expect numeric values, then do not accept them. Consider where the input comes from. If it is from a trusted source that you know has performed thorough input validation, you may choose to omit data validation in your data access code. If the data is from an untrusted source or for defense in depth, your data access methods and components should validate input.

Use Type Safe SQL Parameters

The Parameters collection in SQL provides type checking and length validation. If you use the Parameters collection, input is treated as a literal value and SQL does not treat it as executable code. An additional benefit of using the Parameters collection is that you can enforce type and length checks. Values outside of the range trigger an exception. This is a healthy example of defense in depth.

Important  

SSL does not protect you from SQL injection. Any application that accesses a database without proper input validation and appropriate data access techniques is susceptible to SQL injection attacks.

Use stored procedures where you can, and call them with the Parameters collection.

Using the Parameters Collection with Stored Procedures

The following code fragment illustrates the use of the Parameters collection:

 SqlDataAdapter myCommand = new SqlDataAdapter("AuthorLogin", conn); myCommand.SelectCommand.CommandType = CommandType.StoredProcedure; SqlParameter parm = myCommand.SelectCommand.Parameters.Add(                        "@au_id", SqlDbType.VarChar, 11); parm.Value = Login.Text; 

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 sample above, the input value cannot be longer than 11 characters . If the data does not conform to the type or length defined by the parameter, an exception is generated.

Note that 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. For example, the following code fragment is vulnerable:

 SqlDataAdapter myCommand = new SqlDataAdapter("LoginStoredProcedure '" +                                Login.Text + "'", conn); 

Important  

If you use stored procedures, make sure you use parameters.

Using the Parameters Collection with Dynamic SQL

If you cannot use stored procedures, you can still use parameters, as shown in the following code fragment:

 SqlDataAdapter myCommand = new SqlDataAdapter( "SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id", conn); SqlParameter parm = myCommand.SelectCommand.Parameters.Add("@au_id",                         SqlDbType.VarChar, 11); parm.Value = Login.Text; 

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 adding a number or some other unique value to each parameter name during SQL text concatenation.

Using Filter Routines

Another approach used to protect against SQL injection attacks is to develop filter routines that add escape characters to characters that have special meaning to SQL, such as the single apostrophe character. The following code fragment illustrates a filter routine that adds an escape character:

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

The problem with routines such as this and the reason why you should not rely on them completely is that an attacker could use ASCII hexadecimal characters to bypass your checks. You should, however, filter input as part of your defense in depth strategy.

Note  

Do not rely on filtering input.

Using LIKE Clauses

Note that if you are using a LIKE clause, wildcard characters still need escape characters. The following code fragment illustrates this technique:

 s = s.Replace("[", "[[]"); s = s.Replace("%", "[%]"); s = s.Replace("_", "[_]");