ADO.NET 1.1 Security Guidelines - Input Validation

From Guidance Share

Jump to: navigation, search

- J.D. Meier, Alex Mackman, Michael Dunner, Srinath Vasireddy, Ray Escamilla and Anandha Murukan


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.

When constraining input, it is a good practice to create a list of acceptable characters and use regular expressions to reject any characters that are not on the list. The potential risk associated with using a list of unacceptable characters is that it is always possible to overlook an unacceptable character when defining the list; also, an unacceptable character can be represented in an alternate format to pass validation.


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;


Use 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.


Use Escape Routines

Another approach used to protect against SQL injection attacks is to develop escape routines that add escape characters to characters that have special meaning to SQL, such as the single apostrophe character. The following code fragment illustrates an escape 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. However, you should use escape routines as part of your defense-in-depth strategy. Escape routines are most useful when parameterized SQL cannot be used and you are forced to use dynamic SQL instead. This is because special characters in input pose a threat only with dynamic SQL and not with parameterized SQL.

Note Do not rely on escape routines alone. Use them in conjunction with input validation.


Personal tools