views:

564

answers:

5

I'm working with SQL Server 2005 and Windows Server 2000 and wonder if there are any 'automated' ways of blocking SQL Injection attacks while I shore up my code.

Some have suggested that there are ways to:

  1. Put in some kind of ISAPI or HTTP module that filters request post and querystrings for injection-oriented symbols and fails the request before it even hits the application. Most of these solutions specific IIS 6 or higher. I'm running 5.
  2. Guarantee that each command object runs only a single SQL command at a time.

Any other ideas for my configuration?

+1  A: 

There is no automatic solution to protect against SQL injection in general. SQL injection is an application fault, not a database fault.

The solution is for you to do code review of all cases where you execute SQL that interpolates application data into the query.

Bill Karwin
+1  A: 

This proposed solution:

Guarantee that each command object runs only a single SQL command at a time.

does not actually prevent injection. For example, a login query can be injected by an attacker to log in without credentials. Consider:

"SELECT COUNT(*) FROM Users WHERE UserId = '{0}' AND PassHash = '{1}'"

This template can be injected with a UserId of:

' OR 1=1 --

Yielding:

"SELECT COUNT(*) FROM Users WHERE UserId = '' OR 1=1 --' AND PassHash = 'sdfklahsdlg'"

Focus your effort on eliminating the vulnerability from the code.

recursive
+1  A: 

Make sure all your database calls use either stored procedures or parametrized queries.

Kevin Tighe
+2  A: 

When I had a bunch of injection attack attempts on my server, I was worried that they were taking up unnecessary resources. I wrote (hacked!) an HttpModule in c# that would filter out most xss and sql injection attacks. The code is pasted below, along with the config section required to make a website use it. It should be put in a project and compiled to WebSecurityFilter.dll, which should then be referenced by the web project (or otherwise dropped in the bin directory).

This will only work with asp.net, so hopefully your site is asp.net based (I did ask in a comment, but got no answer).

Web config section (add it in the <httpModules> section of <system.web>:

  <add name="SecurityHttpModule" type="WebSecurityFilter.SecurityHttpModule, WebSecurityFilter" />

Code for module (SecurityHttpModule.cs):

using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Text.RegularExpressions;

namespace WebSecurityFilter
{
    class SecurityHttpModule : IHttpModule
    {
        class RegexWithDesc : Regex
        {
            string _errorText;

            public string ErrorText
            {
                get { return _errorText; }
            }

            public RegexWithDesc(string regex, RegexOptions options, string errorText)
                :base(regex, options)
            {
                _errorText = errorText;
            }
        }
        /// <summary>
        /// error text displayed when security violation is detected
        /// </summary>
        private string _errorhtml =
        @"<!DOCTYPE html PUBLIC ""-//W3C//DTD XHTML 1.1//EN"" ""http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd""&gt;" +
        @"<html xmlns=""http://www.w3.org/1999/xhtml"" >" +
        @"<body style=""background:black;"">" +
        @"<table style=""width:100%"" >" +
        @"<tr><td align=""center"">" +
        @"<div style=""border:3px solid red;text-align:center;width:95%;color:red;padding:10px;text-decoration:blink;"">" +
        @"SECURITY VIOLATION" +
        @"<br/>" +
        //@"<br/>" +
        //@"go away" +
        //@"<br/>" +
        @"<br/>" +
        @"{0}" +
        @"<br/>" +
        @"</div>" +
        @"</td></tr>" +
        @"</table>" +
        @"</body>" +
        @"</html>";

        // regex for default checks
        // http://www.securityfocus.com/infocus/1768
        static RegexOptions _defaultRegexOptions = RegexOptions.Compiled | RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace;

        RegexWithDesc[] _regexCollection = new RegexWithDesc[] 
        { 
            new RegexWithDesc(@"((¼|<)[^\n]+(>|¾)*)|javascript|unescape", _defaultRegexOptions, "XSS 1"), //3.3
            // new RegexWithDesc(@"(\')|(\-\-)", _defaultRegexOptions, "SQL 1"), //2.1
            new RegexWithDesc(@"(=)[^\n]*(\'|(\-\-)|(;))", _defaultRegexOptions, "SQL 2"),    //2.2
            //new RegexWithDesc(@"\w*(\')(or)", _defaultRegexOptions, "SQL 3"),  //2.3
            new RegexWithDesc(@"(\')\s*(or|union|insert|delete|drop|update|create|(declare\s+@\w+))", _defaultRegexOptions, "SQL 4"),   //2.4
            new RegexWithDesc(@"exec(((\s|\+)+(s|x)p\w+)|(\s@))", _defaultRegexOptions, "SQL 5")    //2.5
        };
        #region IHttpModule Members

        public void Dispose()
        {
           // nothing to do
        }

        public void Init(HttpApplication context)
        {
            context.BeginRequest += new EventHandler(context_BeginRequest);
        }

        void context_BeginRequest(object sender, EventArgs e)
        {
            try
            {
                List<string> toCheck = new List<string>();
                foreach (string key in HttpContext.Current.ApplicationInstance.Request.QueryString.AllKeys)
                {
                    toCheck.Add(HttpContext.Current.ApplicationInstance.Request[key]);
                }
                foreach (string key in HttpContext.Current.ApplicationInstance.Request.Form.AllKeys)
                {
                    toCheck.Add(HttpContext.Current.ApplicationInstance.Request.Form[key]);
                }
                foreach (RegexWithDesc regex in _regexCollection)
                {
                    foreach (string param in toCheck)
                    {
                        string dp = HttpUtility.UrlDecode(param);
                        if (regex.IsMatch(dp))
                        {
                            HttpContext.Current.ApplicationInstance.Response.Write(string.Format(_errorhtml, regex.ErrorText));
                            HttpContext.Current.ApplicationInstance.CompleteRequest();
                            return;
                        }
                    }
                }

            }
            catch (System.Threading.ThreadAbortException x)
            {
                throw;
            }
            catch (Exception ex)
            {
                HttpContext.Current.ApplicationInstance.Response.Write(string.Format(_errorhtml, "Attack Vector Detected"));
                HttpContext.Current.ApplicationInstance.Response.Write(string.Format(_errorhtml, ex.GetType().ToString()));
                HttpContext.Current.ApplicationInstance.CompleteRequest();
                return;
            }
        }

        #endregion
    }
}

Hopefully it all formatted okay...

I'll try to post a link to the complete project in a zip this evening.

Andrew Rollings
Note: if anyone tests this out, and they used to have an old Amiga computer, then the error message may be a little familiar.
Andrew Rollings
+1  A: 

Always sanitize user input

  1. if you disallowed ' you would immediately go some way to making your code safer
  2. if your query expects an integer, make sure that input is an integer. etc
DrG