Coding techniques for protecting against Sql injection

Over the past few weeks there have been reports and commentary about SQL injection attacks being launched against both classic ASP and ASP.NET sites (some details at http://blogs.iis.net/bills/archive/2008/04/26/sql-injection-attacks-on-iis-web-servers.aspx ).

Included in this post 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.

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 ClassEnd 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 bolded 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 bolded 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#

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 bolded 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 bolded 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.

'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

ASP.NET 1.1 C#