Question - What is SQL injection and how do I protect my application from SQL injection attacks

Applies to

• ASP.NET 2.0

Answer:

If you are generating dynamic SQL queries based on user input, a SQL injection attack can inject malicious SQL commands that can be executed by the database. The injection attack can occur when your application uses user input to construct dynamic SQL statements to access the database or if your code passes string containing unfiltered user input to stored procedures. To prevent SQL injection attacks you need to:

• Constraint Input - Constraining the input requires that you validate the input for type, length, format and range. You could do this by using regular expressions with RegularExpressionValidator validator control.

Here is a sample of a RegularExpressionValidator control to validate a email address

<form id="WebForm" method="post" runat="server">
  <asp:TextBox id="txtName" runat="server"></asp:TextBox>
  <asp:RegularExpressionValidator id="nameRegex"runat="server" ControlToValidate="emailAddress" ValidationExpression"],@"\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*" ErrorMessage="Invalid email address">
  </asp:regularexpressionvalidator>
</form>

• Use type safe SQL parameters - The Parameters collection in SQL provides type checking and length validation. Parameters can be used when invoking stored procedures as well as regular SQL Select, update, insert and delete statements. 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. The following code fragment illustrates the use of the parameters collection

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;

Additional Resources

• For more information on protecting your application from SQL injection attacks, see "How To: Protect from SQL Injection in ASP.NET" at http://msdn.microsoft.com/library/en-us/dnpag2/html/PAGHT000002.asp

Attributes