Home > DeveloperSection > Blogs > Preventing SQL Injection

Preventing SQL Injection

Posted by  Rohit Kesharwani
on    August-20-2011 10:09 AM

Security in .Net Security in .Net 
Ratings:
1 Comment(s)
 7971  View(s)
Rate this:

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.

Constrain Input:

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   
   following example.

            <form id="WebForm" method="post" runat="server">
                        <asp: TextBox id="txtName" runat="server"></asp:TextBox>
                        <asp: RegularExpressionValidator id="nameRegex" runat="server" 
                        ControlToValidate="txtName" 
                        ValidationExpression="^[a-zA-Z'.\s]{1,40}$"
                        ErrorMessage="Invalid name">
                        </asp: RegularExpressionValidator>
            </form>

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",

                                                      SqlDbType.VarChar, 11);

       parm.Value = Login.Text;

Filtering Input:

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:

  1. Disable ASP.NET request validation by the adding the ValidateRequest="false" attribute to the @ Page directive.
  2. Encode the string input with the HtmlEncode method.
  3. 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.

          <script runat="server">

        void btnSubmit_Click(object sender, EventArgs e)

        {

            // Encode the string input

            StringBuilder sb = new StringBuilder(HttpUtility.HtmlEncode(TextBox1.Text));

            // Selectively allow  and <i>

            sb.Replace("&lt;b&gt;", "<b>");

            sb.Replace("&lt;/b&gt;", "");

            sb.Replace("&lt;i&gt;", "<i>");

            sb.Replace("&lt;/i&gt;", "");

            Response.Write(sb.ToString());

        }

       </script>

 

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)

       {

          if (!System.Text.RegularExpressions.Regex.IsMatch(

          Request.QueryString["Name"], @"^[a-zA-Z'.\s]{1,40}$"))

              Response.Write("Invalid name parameter");

          else

              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.


Re: Prevent SQL Injection in Asp.net

By Arun Singh on   3 years ago
Hi Rohit,
this is awesome !!!!! thanks for sharing with us.















Recent Activities


Kamlakar Singh added new Blog Convert Text Document to PDF File   11 days ago

Kamlakar Singh added new Article Introduction to Backbone.js   11 days ago

Kamlakar Singh added new Question Explain the AdRotator Control.   11 days ago

Don't want to miss updates? Please click the below button!