A Better Blacklist Function for SQLi
Please note - I have not changed my stance on the use of CFQUERYPARAM. The real "fix" for injection is validation routines for form inputs and binding variables using Cfqueryparam. A blacklist function (a function that checks for "known bad" input) is useful in that it provides protection on the perimeter. It can help you intercept hack attempts before they reach your DB - where presumably they would fail in any case. They are also useful for thwarting immediate threats if you discover a security flaw that might take some time to fix. The recent spate of attacks caused a proliferation of blacklist techniques from simple to complex. In my own post on the vulnerability of using string concatenated SQL I published a snippet that made use of the iSQLInject function from CF Lib. There is a better approach however.
Now it turns out that Mary Jo Sminkey (creator of the popular CF Webstore) has a regular expression that she uses for this purpose. Gabriel Read (of Evolution 7) has added to it and I think the result is a better approach than the isSQLInject( ) function (which can generate false positives rather easily). The link to Mary Jo's Regex and function can be found here. In fact, Mary Jo has provided an include that you can just drop into your Application.cfm page (or you might need to fiddle with OnRequest() if you are using application.cfc). It will automatically examine the URL, Form, CGI and Cookie Scopes for you.
Mary Jo's function utilizes ColdFusion's "ReFindNoCase()" function against a complex regex pattern. Gabriel Read took it a step futher and is using a Java class - java.util.regex.Pattern. Here's his approach.
// Short list of db objects to protect
DBObj.short = 'database|function|procedure|role|table|trigger|user|view';
// Sql Threat Indicators
blackList = '@@|' &
'(?:alter.*?(#DBObj.short#))|' &
'cast.*?\(|' &
'char.*?\([\w]{2}\)|' &
'(?:create.*?(#DBObj.short#))|' &
'(?:declare.*?@|cursor)|' &
'delete.*?from.*?_|' &
'(?:drop.*?(#DBObj.short#))|' &
'exec.*?\(|' &
'insert.*?values.*?\(+?|' &
'schema[^\w]+?|' &
'sysObjects|' &
'truncate.*?table|' &
'update.*?set+?|' &
'[sx]p_[\w_]+?|' &
'\''.*?-{2}|-{2}.*?\''' &
'/\*.*?\*/';
// Build the java pattern matcher
rePattern = createObject('java', 'java.util.regex.Pattern');
rePattern = rePattern.compile(blackList);
reMatcher = rePattern.matcher('');
result = reMatcher.reset(lcase('truncate')).find();
</cfscript>
The "result" in the code of above returns either YES or NO. Why would you use this approach? Without testing I can't be sure, but I suspect that this approach might be beneficial in that this fairly large regex does not have to be compiled with each new check. Once the pattered has been added to the matcher object you can just use the reset().find() function as many times as needed for form, url, cgi and cookie variables - like so:
<cfif reMatcher.reset(lcase(url[uItem])).find()>
<h4>Intruder... exterminate...exterminate....</h4>
<cfabort>
</cfif>
</cfloop>
15 Comments
-
Hi Mark...just to correct you, the RegEx was *always* Gabriel's work, not mine. Regular expressions give me headaches. ;-) I just used what he posted to CF-Talk to create a complete tool from it that made it easy to just drop right into an application to use as a scope scanner. Just to give proper credit where it is due! I'll update it later today with his new version, once I run some tests on it. -
Version 2 of the SQL Injection scanner is now available here:
http://www.cfwebstore.com/index.cfm?fuseaction=pag...
I loaded the scanner object into Application scope, so it will need that available in order to work. I've not done a ton of testing on this just yet, so use at your own risk! -
There was a little mistake in the scanner I posted earlier that could cause it to hang, if anyone downloaded it before, please grab the updated copy.
-
I switched to using the java pattern matcher from the native cf refind due to an issue with long strings and excessive backtraces overflowing the java stack and causing CF to sporadically hang. Someone can correct me if they think that I'm wrong, but I've found CF's regex capabilities quite poor.
(and thanks to Mark & Mary Jo for the credits) -
I can say that in some basic iteration tests I found your new version much more efficient. I'm sure a good deal of this also had to do with my loading the whole thing into application scope so the RegEx search didn't have to be recompiled each time, but the difference was more than a factor of 6, so a pretty obvious improvement.
-
Just a quick note to anyone that is using this RegEx, or my tool, that I have still been seeing a number of false positives with it. I've passed on an example to Gabriel (let me know if you didn't get it!) so if he's able to address that, I'll update the tool and post a note here when/if it is available. One of my customers using it mentioned a problem with it blocking a string with some single quotes in it, but I personally haven't been able to reproduce that myself. In any case, if you use this, do be aware that it still may block some legitimate content.
-
I've noticed a lot of people dropping by my site to download the SQL injection tool, so wanted to let you know that a new version is available. This uses a new RegEx provided by Luis Melo which in testing seems to have far fewer false positives than I was seeing before. I've also made some modifications to address a possible thread safety issue, and I changed the output message to address any possible false positives (which hopefully won't occur, but if they do, I prefer to give the user some useful information for what to do next). As before, you can just cfinclude the file wherever is appropriate for your application. Just be sure you are adding those cfqueryparams as well! Download is here:
http://www.cfwebstore.com/index.cfm?fuseaction=pag... -
I am getting an error when trying to place the code within my Application.cfc (Unable to complete CFML to Java translation). Where is the proper place to put the code? -
Just tried it with a plain vanilla page, plain vanilla Application.cfm, on CF8, and the code is not catching anything.
-
@Connie - please make sure when testing it that you use a valid SQL injection string on the URL. The RegEx is fairly intelligent and will ignore normal SQL words like "drop" in strings unless they are formatted in such a way that they could be dangerous (such as following special characters such quotes, semicolons, etc.)
-
I still get regular hits to download this tool, so thought I'd drop by and mention I have an updated version 4 that we are using with good success. It's passed through some pretty vigorous PCI compliance scanning and seems to be doing a good job at blocking everything it needs to. This version includes an additional check for XSS attempts, and corrects the issue we were getting with some weird false positives. As mentioned at the top of the thread, this is no substitution for good coding, cfqueryparam use and proper security, but should provide a nice top layer of protection as well as give some stop-gap measures to older sites waiting to upgrade. Same link as posted before is still valid.
-
Mary Jo, that's wonderful news. I have seen the great blocking affects of the previous version since we put it into place. I get notified anytime someone tries to inject our site. The script provides a great additional layer of protection.
-
FYI,
Just downloaded from your site, where the page says the script is version 4, but the comments in the file only mention version 3. Also, the web page shows the old date. Is this the latest version? -
Never mind. I see that each portion has it's own revision history.
-
@Connie - oops, thanks for letting me know, I've updated the version date on the download. And I added the revision history at the top for the entire tool.
Sites to Watch
Aggregators
RSS
Related Blog Entries