Parameterize your queries without lifting a finger

Tuesday I blogged Peter Boughton's QueryParam Scanner from RiaForge. Today I'm taking a look at Daryl Banttari's Query Parameterizing tool. Daryl's scanner has an interesting twist. Not only does it find unparameterized queries, it will automatically FIX them FOR you! Daryl Banttari works for WebApper and is part of the genius behind SeeFusionHere's the highlights of Daryl's script:

• It's a single stand alone .cfm file
• It will (optionally) drill down recursively from its current location and scan all CFML for cfquery tags with missing cfqueryparam tags
• It automatically skips files starting with an underscore, and folders starting with a period
• The tool gives you the option to check a box next to the queries you want to automatically fix, and submit the form. It will then edit each of those files and wrap your parameters in a cfqueryparam tag!
• It backs up the old file for you in case to need to roll back (test.cfm.old)
• In general the only attribute it uses for the cfqueryparam tag is value, but it will add cfsqltype="CF_SQL_TIMESTAMP" if the column name contains the word "date", or the parameter contains "now()"

Here's some lowlights:

• It runs out-of-the-box on Windows, but has some slash problems on Linux. I was getting some interesting paths (which errored) like /var/wwwroot/bradwood_com/\test.cfm
• By default it only scans .cfm files, not .cfc or .cfml files
• The interface is pretty basic and doesn't allow you to collapse anything forcing you to scroll through a LOT of text
• Unlike Peter's script, it does not have an option to search for ColdFusion variables in the order by clause (which technically aren't parameters, but they ARE very dangerous)
• The date guessing logic will get confused if you have a column name like "update"
• There is no interface to configure options. They are controlled by editing some variable at the top of the script
• Peter's script will let you choose ANY folder on your server to scan while Daryl's will only scan the folder it resides in

One note for BOTH Peter and Daryl's scripts is that neither of them exclude query of queries from their checks. I suppose someone could still jack with your app by injecting code into a qofq, but of there is no connection to your database, they can't do much. What I did was scan an entire site with the setting to default checkboxes checked to off. I then scrolled down the page and inspected the queries one by one and checked the boxes next the ones that showed an acceptable change proposal. The only things I really had to watch out for were date columns. Overall, I'm pretty impressed with Daryl's script. My low-lights are largely aesthetic. A little bit of work could combine the best parts of both scripts. Daryl's script is released as public domain, but I'll probably ask if he minds me fooling with it a bit. Here's a quick bit. The following code will change his script to search .cfml and .cfc files: Change the following code on line 67:

[code]<CFDIRECTORY Action="List" 
	Directory="#CurDir#" 
	Name="Dir" 
	Filter="*.cfm">
[/code]

To the following:

[code]<CFDIRECTORY Action="List" 
	Directory="#CurDir#" 
	Name="Dir"
	Filter="*.cfm|*.cfc|*.cfml">
[/code]

Well people, the only way this could get any easier for you would be for a little gnome to climb into your server and fix all your cfqueries for you. If you want to try out Daryl's scanner, a download link is found at this WebApper blog entry . Also, on a side note-- I scanned BlogCFC version 5.9.004 and it came out squeaky clean. There were a couple false alarms but they turned out to be query of queries. Go Ray!