|
Avoid Dynamic Queries that Accept
Untrusted Input
Applies to
ADO.NET 2.0
What to do
Avoid constructing SQL queries in code that include user input; instead,
prefer parameterized stored procedures that use type safe SQL
parameters. If stored procedures cannot be used, use parameterized
dynamic SQL statements.
Why
Database queries constructed dynamically from user input are susceptible
to SQL injection. A successful SQL injection attack enables a malicious
user to execute commands on the application's database instance by using
the privileges granted to the application's login. Using stored
procedures with parameterized SQL is recommended because SQL parameters
are type safe and treated as literal values by the database, not as
executable code. Parameters are also checked for type and length.
When
Always use parameterized SQL queries. Avoid constructing SQL queries
directly from any input, including form fields, query string parameters,
and cookies.
How
To protect an application from SQL
injection, perform the following steps:
1. Use parameters with stored procedures.
Stored procedures alone will not prevent SQL injection. Lack of
parameter usage means that stored procedures will be susceptible to SQL
injection, especially if they use unfiltered input. The following code
shows how to use SqlParameterCollection when calling a stored procedure:
using System.Data;using System.Data.SqlClient;
using (SqlConnection connection = new SqlConnection(connectionString)){
DataSet userDataset = new DataSet(); SqlDataAdapter myCommand = new
SqlDataAdapter( "LoginStoredProcedure", connection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
myCommand.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);
myCommand.SelectCommand.Parameters["@au_id"].Value = AuthId.Text;
myCommand.Fill(userDataset);}
In this case, the @au_id parameter is treated as a literal value and not
as executable code. Also, the parameter is checked for type and length.
In the preceding code example, the input value cannot be longer than 11
characters. If the data does not conform to the type or length defined
by the parameter, the SqlParameter class throws an exception.
2. Use parameters with dynamic SQL.
If stored procedures are unavailable, 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",
connection); myCommand.SelectCommand.Parameters.Add("@au_id",
SqlDbType.VarChar, 11); myCommand.SelectCommand.Parameters["@au_id"].Value
= AuthId.Text; myDataAdapter.Fill(userDataset);
}
Problem Example
<<Provides problem example which the guideline can address.>>
Solution Example
<<Provides solution example which the guideline can
address.>>
Additional
Resources
Protect From SQL Injection in ASP.NET
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/PAGHT000002.asp
Protect From Injection Attacks in ASP.NET
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000003.asp
How to Use Regular Expressions to Constrain Input
in ASP.NET
http://msdn2.microsoft.com/en-us/library/ms998267.aspx
Related Items
|
