Currently, I’m migrating a CF5/Win project to CFMX9/Linux. Apart from the usual path issues, the one who programmed this app yeeears ago did not protect *any single* form- or url-variable inside CFQUERY against misuse or even SQL-Injection. Not one syntax check, no CFQUERYPARAM… *sigh*
Unfortunately, it’s impossible to perform a sitewide search/replace, so I have to open every single file containing CFQUERY tags. To get a list of all the files containing “<cfquery…”, I did a quick
grep -rli “\<cfquery ” > cfqueryfiles.txt
Then, I wrote two tiny regular expressions that make the manual replacing a lot faster:
Step 1: Strings => Varchar
Replace ‘#([^#]*)#’ by <cfqueryparam cfsqltype=”CF_SQL_VARCHAR” maxlength=”50″ value=”#\1#”>
Step 2: Numbers => Big
Replace #([^#]*)# by <cfqueryparam cfsqltype=”CF_SQL_BIGINT” value=”#\1#”>
(Careful! This replaces ANY variable, but does its job inside CFQUERY. Do NOT “replace all”.)
After that, you only have to set the correct sqltypes and/or maxlengths.
I feel your pain, I’ve been in that situation before. Here’s a useful tool for scanning your code and telling you what queries need paraming, http://qpscanner.riaforge.org/. Although it only tells you where the problems are, it doesn’t fix them for you.
Your regex solution seems pretty slick, you should think about expanding on the qpscanner and making a tool that finds AND fixes your queries. 😉
The problem with the above regex solution – and the key reason qpscanner doesn’t do fixing yet – is that regex can’t handle CFML parsing.
Some simple examples:
‘#this#and#that#’
‘apos”#trophe#’
“and ‘#so(on,”‘”)#”
If people are going to rely on qpscanner for auto-fixing, it needs to be near-perfect, and that needs a proper parser rather than simple regex.
@Eric: thanks for the tip – I didn’t know qpscanner at all…
Does it have advantages over a grep search? I could have tweaked the grep expression using regex, but my (indeed rather simple) solution did the trick as far as this project is concerned. I just needed a list of files containing cfquery and was too lazy to replace all variables manually. If you ever needed to type on a german keyboard, you know what I’m talking about 😉
Well,
I’m not as technical oriented as you, but face a similar situation. But I ran into a little frustration trying to follow your blog.
Since there is no “by” in Coldfusion REPLACE, nor in Javascript, it would have been nice to let readers also know how you actually ran the replace command against the suspect files. I hope the reader can come up with an environment for using it (depending on whether they are in Linux or Window – since you left that out as well).
Maybe you used Microsoft Word? (Not available in Linux). I guess you could load the file into memory and use Coldfusion to do the replacements. Personally, I’m looking into using a Linux script for it. First I’d like to test it on some test files to see how it does.
Never assume the reader that needs this has ANY background on HOW to do it.
Thanks for posting this as it may be helpful to some, but if you’re gonna throw a bone, try to leave a little meat on it 😉
Comments on this entry are closed.