ASP.NET 2.0 Security Inspection Questions - SQL Injection

From Guidance Share

Jump to: navigation, search

- J.D. Meier, Alex Mackman, Blaine Wastell, Prashant Bansode, Jason Taylor, Rudolph Araujo


Contents

SQL Injection Vulnerabilities and Implications

Vulnerability

Implications

Non-validated input used to generate SQL queries

SQL injections can result in unauthorized access, modification, or destruction of SQL data.

Your code is vulnerable to SQL injection attacks wherever it uses input parameters to construct SQL statements. A SQL injection attack occurs when untrusted input can modify the logic of a SQL query in unexpected ways. As you review the code, make sure that any input that is used in a SQL query is validated or that the SQL queries are parameterized. Table 1 summarizes the SQL injection vulnerability and its implications.

The following questions can help you to identify vulnerable areas:

  • Is the application susceptible to SQL injection?
  • Does the code use parameterized stored procedures?
  • Does the code use parameters in SQL statements?
  • Does the code attempt to filter input?


Is the application susceptible to SQL injection?

Pay close attention to your data access code. Scan for the strings "SqlCommand", "OleDbCommand", or "OdbcCommand" to help identify data access code. Identify any input field that you use to form a SQL database query. Check that these fields are suitably validated for type, format, length, and range.


Does the code use parameterized stored procedures?

Check that your code uses parameterized stored procedures and typed parameter objects such as SqlParameter, OleDbParameter, or OdbcParameter. Stored procedures alone cannot prevent SQL injection attacks. The following example shows the use of a SqlParameter.

SqlDataAdapter myCommand = new SqlDataAdapter("spLogin", conn);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parm = myCommand.SelectCommand.Parameters.Add(
                               "@userName", SqlDbType.VarChar,12);
parm.Value=txtUid.Text;
 

The typed SQL parameter checks the type and length of the input, and it ensures that the userName input value is treated as a literal value and not as executable code in the database.


Does the code use parameters in SQL statements?

If the code does not use stored procedures, make sure that it uses parameters in the SQL statements it constructs, as shown in the following example.

select status from Users where UserName=@userName

Check that the code does not use the following approach, where the input is used directly to construct the executable SQL statement by using string concatenation.

string sql = "select status from Users where UserName='"
          + txtUserName.Text + "'";


Does the code attempt to filter input?

A common approach is to develop filter routines to add escape characters to characters that have special meaning to SQL. This is an unsafe approach, and developers should not rely on it because of character representation issues.

Personal tools