SQL or SQL Server solutions for SQL injection
I work with SQL Server 2005 and Windows Server 2000 and am wondering if there are any "automatic" ways to block SQL Injection attacks when I maintain my code.
Some have suggested that there are ways:
- Put some kind of ISAPI or HTTP module that filters the request and requests for injection oriented characters and doesn't execute the request before it even hits the application. Most of these solutions are IIS 6 or higher. I'm running 5.
- Ensure that each command object only runs one SQL command at a time.
Any other ideas for my config?
source to share
When I had a bunch of injection 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 of the xss and sql attacks. Below is the code as well as the configuration section required to be used by the website. It should be placed in the project and compiled into the WebSecurityFilter.dll file, which should then be listed by the web project (or otherwise removed in the bin directory).
This will only work with asp.net, so hopefully your site is based on asp.net (I asked in the comment but didn't get a response).
Web config section (add it to <httpModules> <system.web> section:
<add name="SecurityHttpModule" type="WebSecurityFilter.SecurityHttpModule, WebSecurityFilter" />
Code for the 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"">" +
@"<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 everything is formatted well ...
Tonight I will try to post a link to the complete project in zip.
source to share
This is the suggested solution:
Ensure that each command object only runs one SQL command at a time.
does not actually prevent injection. For example, a login request could be entered by an attacker to log on without credentials. Consider:
"SELECT COUNT(*) FROM Users WHERE UserId = '{0}' AND PassHash = '{1}'"
This template can be entered using the UserId from:
' OR 1=1 --
Yielding:
"SELECT COUNT(*) FROM Users WHERE UserId = '' OR 1=1 --' AND PassHash = 'sdfklahsdlg'"
Focus on fixing the vulnerability in your code.
source to share