Use type safe SQL parameters

From Guidance Share

Jump to: navigation, search

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

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;


References

Personal tools