Prevent SQL Injection in Asp.net
If you generate dynamic SQL queries based on user input, an attacker could inject malicious SQL commands that can be executed by the database.
In this blog I explained various overcome from SQL Injection.
To constrain input, follow these guidelines:
- Use server-side input validation: Do not rely on client-side validation because it is easily bypassed. Use client-side validation in addition to server-side validation to reduce round trips to the server and to improve the user experience.
- Validate length, range, format and type: In most cases, numeric fields should be checked for type and range. To validate the type and range of a numeric input field that uses a server control, you can use a RangeValidator control. The RangeValidator supports currency, date, integer, double, and string data types.
- Use strong data typing: Assign numeric values to numeric data types such as Integer or Double. Assign string values to string data types. Assign dates to the DateTime data type.
Example: Using a RegularExpressionValidator:
To use a RegularExpressionValidator, set the ControlToValidate, Validation-
Expression, and ErrorMessage properties to appropriate values as shown in the
<form id="WebForm" method="post" runat="server">
<asp: TextBox id="txtName" runat="server"></asp:TextBox>
<asp: RegularExpressionValidator id="nameRegex" runat="server"
Use Type-Safe SQL Parameters:
The Parameters collection in SQL Server provides type checking and length validation. If you use the Parameters collection, input is treated as a literal value instead of as executable code. An additional benefit of using the Parameters collection is that you can enforce type and length checks. Values outside the range will trigger an exception. The following code fragment shows using the Parameters collection:
SqlCommand myCommand = new SqlCommand("AuthorLogin @au_id", conn);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parm = myCommand.SelectCommand.Parameters.Add("@au_id",
parm.Value = Login.Text;
Filtering input may also be helpful in protecting against SQL injection by removing escape characters. However, because of the large number of characters that may pose problems. The following example searches for the character string delimiter.
private string SafeSqlInput(string sqlInput)
return sqlInput.Replace("'", "''");
Use a Least-Privileged Database Account:
Your application should connect to the database by using a least-privileged account. If you use Windows authentication to connect, the Windows account should be least-privileged from an operating system perspective and should have limited privileges and limited ability to access Windows resources. Additionally, when you use SQL Server authentication, the corresponding SQL Server login should be restricted by permissions in the database.
Use ASP.NET Request Validation:
This helps prevent users from inserting script into your application. Request validation checks all input data against a hard-coded list of potentially dangerous values. If a match occurs, it throws an exception of type HttpRequestValidationException.
You can disable request validation in your Web.config application configuration file by adding a <pages> element with validateRequest="false" or on an individual page by setting ValidateRequest="false" on the @ Pages directive.
Allowing Restricted HTML Input:
To allow restricted HTML input:
- Disable ASP.NET request validation by the adding the ValidateRequest="false" attribute to the @ Page directive.
- Encode the string input with the HtmlEncode method.
- Use a StringBuilder and call its Replace method to selectively remove the encoding on the HTML elements that you want to permit.
The following code shows this approach. The page disables ASP.NET request validation by setting ValidateRequest="false". It HTML-encodes the input and selectively allows the <b> and <i> HTML elements to support simple text formatting.
void btnSubmit_Click(object sender, EventArgs e)
// Encode the string input
StringBuilder sb = new StringBuilder(HttpUtility.HtmlEncode(TextBox1.Text));
// Selectively allow and <i>
Validate Query String Values:
Validate query string values for length, range, format, and type. We usually do this by using a combination of regular expressions to:
- Constrain the input values.
- Set explicit range checks.
- Specify the explicit type checks performed by converting the input value to its equivalent.
The following code example shows how to use the Regex class to validate a name string passed on a query string.
protected void Page_Load(object sender, EventArgs e)
Response.Write("Invalid name parameter");
Response.Write("Name is” + Request.QueryString["Name"]);
Validate Cookie Values:
Values maintained in cookies, such as query string parameters, can easily be manipulated by a client because the cookies are stored on the client browser. Validate cookie values in the same way as you would for query string parameters. Validate them for length, range, format, and type.