Preventing SQL Injection in .NET

June 12, 2009 · No CommentsComments Feed

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.

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

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

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); }