cfqueryparam / regular expression
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