QueryParam Scanner- You've got no excuse now

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.
We may have gotten away with security through obscurity in the past, but consider this your call to arms. Hackers are getting clever and very persistent and your site will see hack attempts if it hasn't already. There is a whole list of things you can do to protect your database, but I won't go into all that now. I'll suffice it to say that the MOST useful thing you can spend your time on right now is by going through ANY public facing code and making sure that every single ColdFusion variable in a cfquery is safely wrapped in cfqueryparam. So, back to the QueryParam Scanner. I downloaded it from RiaForge and unzipped its contents into my web root. It is self-contained, and doesn't require any special mappings or data sources to work. Simply navigate to the folder you unzipped everything into. Minimally you just need to provide an absolute path to a folder to search. At first I was having problems, but then I realized I was just retarded, and had my slashes backwards. Once I got the path correct, the program fired up and began pointing out all my vulnerable cfqueries. I edited the files one-by-one and re-ran the scanner until everything was gone. Yeah, it was a bit of a pain, but I feel so much better after cleaning up that old stuff. You can put checks in, filter IP addresses, and search for certain words in your query string, but PLEASE use cfqueryparam. It really is the only way to be sure since SQL injection attacks can come in many forms from many IPs. You don't have any excuse now. QueryParam Scanner

 


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>

Site Updates

Entry Comments

Entries Search