Preventing SQL Injection Attacks in ASP.NET

Database Management
Included in this article are VB.NET and C# samples that can be used to screen incoming query-string, form and cookie values for potential Sql injection values.  However because valid input data varies from website to website, it is not possible to write a one-size-fits-all screening mechanism.  You can modify the sample code included in this post to tighten or loosen the character sequences as appropriate for your website.

You can find more information on SQL Injection attacks here.

Also as a reminder, if a website makes heavy use of dynamically constructed Sql (as opposed to parameterized Sql or parameterized stored procedures) it is a best practice to escape all single quotes contained in un-trusted web input.  Since it is not possible to make this replacement using the HttpModule/BeginRequest approaches shown below, you can instead scrub a website's code and perform the escaping in all places where dynamic Sql is being built.

    //C# snippet
    private string SafeSqlLiteral(string inputSQL)
    {
      return inputSQL.Replace("'", "''");
    }

    

    'VB.NET snippet
    Private Function SafeSqlLiteral(ByVal inputSQL As String) As String
        Return inputSQL.Replace("'", "''")
    End Function


ASP.NET 2.0 VB.NET

You can screen all incoming query-string, form and cookie values by running code during the BeginRequest event.  This type of code can run on every request when implemented in an HttpModule.  The sample code below defines an HttpModule in the App_Code directory, and then registers the module in web.config so that it runs on every request.  The sample code will check incoming data and automatically redirect to a page called "Error.aspx" if suspicious character sequences are found.

First create a new file in your site's App_Code directory called SampleSqlInjectionScreeningModule.vb.  Then paste the following code into the file:

    Imports Microsoft.VisualBasic

    Namespace Sample
        Public Class SampleSqlInjectionScreeningModuleVB
            Implements IHttpModule

            'Defines the set of characters that will be checked.
            'You can add to this list, or remove items from this list, as appropriate for your site
            Public Shared blackList As String() = {"--", ";--", ";", "/*", "*/", "@@", _
                                                   "@", "char", "nchar", "varchar", "nvarchar", "alter", _
                                                   "begin", "cast", "create", "cursor", "declare", "delete", _
                                                   "drop", "end", "exec", "execute", "fetch", "insert", _
                                                   "kill", "open", "select", "sys", "sysobjects", "syscolumns", _
                                                   "table", "update"}

            Public Sub Dispose() Implements IHttpModule.Dispose
                'no-op
            End Sub

            'Tells ASP.NET that there is code to run during BeginRequest
            Public Sub Init(ByVal app As HttpApplication) Implements IHttpModule.Init
                AddHandler app.BeginRequest, AddressOf app_BeginRequest
            End Sub

            'For each incoming request, check the query-string, form and cookie values for suspicious values.
            Private Sub app_BeginRequest(ByVal sender As Object, ByVal e As EventArgs)
                Dim Request As HttpRequest = TryCast(sender, HttpApplication).Context.Request

                For Each key As String In Request.QueryString
                    CheckInput(Request.QueryString(key))
                Next
                For Each key As String In Request.Form
                    CheckInput(Request.Form(key))
                Next
                For Each key As String In Request.Cookies
                    CheckInput(Request.Cookies(key).Value)
                Next
            End Sub

            'The utility method that performs the blacklist comparisons
            'You can change the error handling, and error redirect location to whatever makes sense for your site.
            Private Sub CheckInput(ByVal parameter As String)
                For i As Integer = 0 To blackList.Length - 1
                    If (parameter.IndexOf(blackList(i), StringComparison.OrdinalIgnoreCase) >= 0) Then
                        '
                        'Handle the discovery of suspicious Sql characters here
                        '
                        'generic error page on your site
                        HttpContext.Current.Response.Redirect("~/Error.aspx")
                    End If
                Next
            End Sub

        End Class
    End Namespace


You then need to register the HttpModule with ASP.NET. 

If you are running ASP.NET 2.0 on IIS6, or ASP.NET 2.0 on IIS7 in Classic Mode, place the bold module registration shown below inside of the system.web/httpModules section:

<system.web>
     …
     <httpModules>
     …
          <add name="SampleSqlInjectionScreeningModuleVB" type="Sample.SampleSqlInjectionScreeningModuleVB"/>
     …
     </httpModules>
     …
</system.web>


However if you are running ASP.NET 2.0 on IIS7 in Integrated Mode, you instead need to place the bold module registration shown below inside of the system.webServer/modules section:

<system.webServer>
     …
     <modules>
          …
          <add name="SampleSqlInjectionScreeningModuleVB" type="Sample.SampleSqlInjectionScreeningModuleVB" preCondition="managedHandler"/>
          …
     </modules>
     …
</system.webServer>


 
ASP.NET 2.0 C#

You can screen all incoming query-string, form and cookie values by running code during the BeginRequest event.  This type of code can run on every request when implemented in an HttpModule.  The sample code below defines an HttpModule in the App_Code directory, and then registers the module in web.config so that it runs on every request.  The sample code will check incoming data and automatically redirect to a page called "Error.aspx" if suspicious character sequences are found.

First create a new file in your site's App_Code directory called SampleSqlInjectionScreeningModule.cs.  Then paste the following code into the file:

    using System;
    using System.Data;
    using System.Configuration;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;

    namespace Sample
    {
        public class SampleSqlInjectionScreeningModuleCS : IHttpModule
        {

            //Defines the set of characters that will be checked.
            //You can add to this list, or remove items from this list, as appropriate for your site
            public static string[] blackList = {"--",";--",";","/*","*/","@@","@",
                                               "char","nchar","varchar","nvarchar",
                                               "alter","begin","cast","create","cursor","declare","delete","drop","end","exec","execute",
                                               "fetch","insert","kill","open",
                                               "select", "sys","sysobjects","syscolumns",
                                               "table","update"};

            public void Dispose() {
                //no-op
            }

            //Tells ASP.NET that there is code to run during BeginRequest
            public void Init(HttpApplication app)
            {
               app.BeginRequest += new EventHandler(app_BeginRequest);
            }

            //For each incoming request, check the query-string, form and cookie values for suspicious values.
            void  app_BeginRequest(object sender, EventArgs e)
            {
                HttpRequest Request = (sender as HttpApplication).Context.Request;

                foreach (string key in Request.QueryString)
                    CheckInput(Request.QueryString[key]);
                foreach (string key in Request.Form)
                    CheckInput(Request.Form[key]);
                foreach (string key in Request.Cookies)
                    CheckInput(Request.Cookies[key].Value);       
            }

            //The utility method that performs the blacklist comparisons
            //You can change the error handling, and error redirect location to whatever makes sense for your site.
            private void CheckInput(string parameter)
            {
                for (int i = 0; i < blackList.Length; i++)
                {
                    if ((parameter.IndexOf(blackList[i], StringComparison.OrdinalIgnoreCase) >= 0))
                    {
                        //
                        //Handle the discovery of suspicious Sql characters here
                        //
                        HttpContext.Current.Response.Redirect("~/Error.aspx");  //generic error page on your site
                    }
                }
            }

        }
    }


You then need to register the HttpModule with ASP.NET.

If you are running ASP.NET 2.0 on IIS6, or ASP.NET 2.0 on IIS7 in Classic Mode, place the bold module registration shown below inside of the system.web/httpModules section:

<system.web>
     …
     <httpModules>
          …
          <add name="SampleSqlInjectionScreeningModuleCS" type="Sample.SampleSqlInjectionScreeningModuleCS"/>
          …
     </httpModules>
     …
</system.web>


However if you are running ASP.NET 2.0 on IIS7 in Integrated Mode, you instead need to place the bold module registration shown below inside of the system.webServer/modules section:

<system.webServer>
     …
     <modules>
          …
          <add name="SampleSqlInjectionScreeningModuleCS" type="Sample.SampleSqlInjectionScreeningModuleCS" preCondition="managedHandler"/>
          …
     </modules>
     …
</system.webServer>


 
ASP.NET 1.1 VB.NET

You can screen all incoming query-string, form and cookie values by running code during the BeginRequest event.  A central location to register this code is in a website's global.asax file.  The sample code below will check incoming data and automatically redirect to a page called "Error.aspx" if suspicious character sequences are found.
First you will need to add a new namespace import at the top of your global.asax file:

    <%@ Import namespace="System.Globalization" %>

Next place the following variable definition and private function somewhere in your global.asax file between the <script> tags:

        'Defines the set of characters that will be checked.
        'You can add to this list, or remove items from this list, as appropriate for your site
        Public Shared blackList As String() = {"--", ";--", ";", "/*", "*/", "@@", _
                                               "@", "char", "nchar", "varchar", "nvarchar", "alter", _
                                               "begin", "cast", "create", "cursor", "declare", "delete", _
                                               "drop", "end", "exec", "execute", "fetch", "insert", _
                                               "kill", "open", "select", "sys", "sysobjects", "syscolumns", "table", _
                                               "update"}

        'The utility method that performs the blacklist comparisons
        'You can change the error handling, and error redirect location to whatever makes sense for your site.
        Private Sub CheckInput(ByVal parameter As String)
            Dim comparer As CompareInfo = CultureInfo.InvariantCulture.CompareInfo

            For i As Integer = 0 To blackList.Length - 1
                If (comparer.IndexOf(parameter,blackList(i),CompareOptions.IgnoreCase) >= 0) Then
                    '
                    'Handle the discovery of suspicious Sql characters here
                    '
                    'generic error page on your site
                    Response.Redirect("~/Error.aspx")
                End If
            Next
        End Sub


Lastly place the following function definition somewhere in your global.asax file between the <script> tags.  This is the function definition that tells ASP.NET to run string checks during the BeginRequest event.  If your global.asax file already has a function called Application_BeginRequest, you should instead place the contents of the function definition below into your existing version of Application_BeginRequest.

        Sub Application_BeginRequest(ByVal sender As Object, ByVal e As EventArgs)
           
            For Each key As String In Request.QueryString
                CheckInput(Request.QueryString(key))
            Next
            For Each key As String In Request.Form
                CheckInput(Request.Form(key))
            Next
            For Each key As String In Request.Cookies
                CheckInput(Request.Cookies(key).Value)
            Next
           
        End Sub


ASP.NET 1.1 C#

You can screen all incoming query-string, form and cookie values by running code during the BeginRequest event.  A central location to register this code is in a website's global.asax file.  The sample code below will check incoming data and automatically redirect to a page called "Error.aspx" if suspicious character sequences are found.

First you will need to add a new namespace import at the top of your global.asax file:

    <%@ Import namespace="System.Globalization" %>

Next place the following variable definition and private function somewhere in your global.asax file between the <script> tags:

        //Defines the set of characters that will be checked.
        //You can add to this list, or remove items from this list, as appropriate for your site
        public static string[] blackList = {"--",";--",";","/*","*/","@@","@",
                                             "char","nchar","varchar","nvarchar",
                                             "alter","begin","cast","create","cursor","declare","delete","drop","end","exec","execute",
                                             "fetch","insert","kill","open",
                                             "select", "sys","sysobjects","syscolumns",
                                             "table","update"};

        //The utility method that performs the blacklist comparisons
        //You can change the error handling, and error redirect location to whatever makes sense for your site.
        private void CheckInput(string parameter)
        {
            CompareInfo comparer = CultureInfo.InvariantCulture.CompareInfo;

            for (int i = 0; i < blackList.Length; i++)
            {
                if (comparer.IndexOf(parameter,blackList[i],CompareOptions.IgnoreCase) >= 0)
                {
                    //
                    //Handle the discovery of suspicious Sql characters here
                    //
                    Response.Redirect("~/Error.aspx");  //generic error page on your site
                }
            }
        }


Lastly place the following function definition somewhere in your global.asax file between the <script> tags.  This is the function definition that tells ASP.NET to run string checks during the BeginRequest event.  If your global.asax file already has a function called Application_BeginRequest, you should instead place the contents of the function definition below into your existing version of Application_BeginRequest.

        void Application_BeginRequest(object sender, EventArgs e)
        {
            foreach (string key in Request.QueryString)
                CheckInput(Request.QueryString[key]);
            foreach (string key in Request.Form)
                CheckInput(Request.Form[key]);
            foreach (string key in Request.Cookies)
                CheckInput(Request.Cookies[key].Value);               
        }

Add Feedback