QueryParam Scanner- You've got no excuse now
Posted by
Brad Wood
Jul 22, 2008 10:07:00 UTC
This April, Peter Boughton put a little tool on RiaForge called QueryParam Scanner. It does what it says and that means you have no excuse not to batten down the hatches on that old code you've got swept under the rug. It also meant I didn't have any excuses either, so I gave it a run tonight.Whether you wrote it or not, everyone probably has some old code laying around that doesn't use cfqueryparam to protect its cfqueries. I had some ancient stuff. Like, CF 4.5 days. I'm talking pound signs in my cfif statements! In light of the sweeping SQL injection attacks making their rounds recently I think it is very appropriate to bring this to attention.
The cfqueryparam tag in ColdFusion has several purposes.
- Built-in data type checking
- Separates SQL code from parameters
- This encourages your DBMS to cached a reusable execution plan which can improve performance
- It guarantees that parameter values will NEVER spill over into the SQL to be accidentally executed.
- That means your cfquery is immune to pretty much most SQL injection attacks.
JR
"That means your cfquery is immune to pretty much most SQL injection attacks."
Can you give an example of a SQL Injection attack which is not caught by cfqueryparam ?
Brad Wood
@JC: very good question. I chose to respond in the form of another blog post.
http://www.codersrevolution.com/index.cfm/2008/7/22/When-will-cfqueryparam-NOT-protect-me
samuel
>>every single ColdFusion variable in a cfquery is safely wrapped in cfqueryparam
couple stupid questions:
-should we do the same for internal variables, like order_id from internal queries, not just form/url stuff? I assume yes anyway?
-I assume if we have some fixed entries - like a 0 or something, not a variable - we don't need to wrap those in cfqueryparam?
thanks
samuel
Btw, I meant the 2nd question for the sake of speeding up the query. Not sure how that works.
Brad Wood
@Samuel: Those aren't stupid questions.
In your first question you asked if you should use cfqueryparam around CF variables that come from a trusted source like the database. For starters, remember that if another attack was to put malicious content into a database column like order_id, it could longer be trusted. However, if the column was of type int or uniqueidentifier, then you would be pretty safe from the SQL injection side. I'm still going to recommend using cfqueryparam around those values anyway though for the potential performance benefit your SQL can get.
You see, when your database executes a SQL statement, it generates an execution plan that best fits that statement and it caches that plan in memory for later use (so it doesn't have to be generated again which is costly). The cached execution plan will ONLY be used for other queries that are the EXACT same. Keep in mind also that your database server has a limit to how many plans it will cache. If you output an order number directly into the query and then view different 100,000 orders, your SQL server will try and cache 100,000 different plans. Needless to say, that is a waste of resources, and it won't keep that many plans anyway. Chances are, other useful plans will get pushed out of memory to make room. However, if all your inputs are parameterized, then all those SQL statements are now IDENTICAL and share the same plan:
Select order_id From orders Where order_id = ?
Does that make sense. Maybe I should do a separate blog post about that... Cached plans CAN be a detriment to performance, but that's another matter all together that I wouldn't worry about for now.
For your second question-- should you use cfqueryparam around hard-coded static values in your query. The answer is you can, but you do not need to. Since these values never change there is no risk of injection, and they execution plan will not be affected.
Hope that helps you Samuel.
samuel
For the 2nd: "I meant the 2nd question for the sake of speeding up the query." Not injection.
Thanks.
Brad Wood
cfqueryparaming a static variable will have no affect on performance. That's what I meant when I said the "execution plan will not be affected" meaning the query will do the exact same thing with or without it.
Dave Neale
Brad I've tried the slashes both ways but I jusr get "no querys" found no matter what directory I search???
Brad Wood
@Dave: Sorry to hear you are having problems. Are you on Windows or Linux? Make sure your path is a fully quialified path that starts with a drive letter (if Windows) D:\inetpub\mysite
or /var/wwwroot/mysite/
Also, what file extention does your site use? This tool checks .cfm and .cfc, but I don't think it picks up .cfml without some modification.
Additionally, if you have all your queries in CFCs outside your web root in a custom tags folder, you need to specify that address.
shariff
hi all iam getting an error Element INFO is undefined in RESULTS.
The error occurred in E:\qpscanner\parsed\scanner.go.cfm: line 40 Called from E:\qpscanner\parsed\scanner.go.cfm: line 34 Called from E:\qpscanner\parsed\scanner.go.cfm: line 5 Called from E:\qpscanner\parsed\scanner.go.cfm: line 1 Called from E:\qpscanner\fusebox5\Application.cfc: line 228 Called from E:\qpscanner\fusebox5\Application.cfc: line 218 Called from E:\qpscanner\fusebox5\fusebox5.cfm: line 57 Called from E:\qpscanner\index.cfm: line 24
38 : <cfset variables.Data = "#Results.Data#" /> 39 : <cfif isDefined("variables.Info")><cfset myFusebox.stack["variables.Info"] = variables.Info ></cfif> 40 : <cfset variables.Info = "#Results.Info#" /> 41 : <cftry> 42 : <cfsavecontent variable="Content"><cfoutput><cfinclude template="../circuits/scanner/dsp_html.cfm"></cfoutput></cfsavecontent>