For those that don’t know, ColdFusion’s cfqueryparam won’t work on a SQL ORDER BY clause, so the following will not work:
This is really nothing new, people have been talking about this for years but with the spate of recent SQL injection attacks (note that I meant to publish this around sept 08 but got sidetracked!) it’s worth mentioning again because there certainly are times where you want dynamic ordering based on a URL or form value.
So what does this mean to you? How are you to manage dynamic ORDER BY values? The most common scenario I’ve come across is to use a list of allowed order by column values as defined by the developer. Something like:
Another approach might be to use a switch with either a literal value or a keyword which might map to a list of options like:
I like this approach as the “mapped” keyword allows you to use order by values which aren’t so simple as a basic literal.
This means when you want to order by multiple columns, use ASC/DESC or a combination of both (which are of course perfectly valid and real-world options) you can define these keywords in your application and still benefit from the safety of possible SQL injection by utilising the cfswitch statement.
Don’t forget to always sanitise ANY USER INPUT!!!
6 Comments
Another interesting way I noticed recently was in Ben Nadel’s discussion on sanitising table names (which of course can apply to column names and other SQL keywords such as ASC/DESC), by using cfparam:
http://www.bennadel.com/blog/1396-Ask-Ben-Dynamic-Table-Names-In-ColdFusion-Queries.htm
e.g.
As Ben also notes in a followup post the regex pattern here matches the entire value (i.e. it implicitly uses the caret (^) to match the start of the string and the dollar sign ($) to match the end):
http://www.bennadel.com/blog/1398-ColdFusion-CFParam-Regex-Validation-Tests-Whole-Value.htm
This is a great post as it is very simple but very often forgotten. The cfqueryparam tag simply parameterizes query values for databases that support it and only in places that they support it.
Query parameters regardless of language can only work in situations where the database server’s sql can support variables. For SQL server (and most databases) this does not include order by or TOP (the other big one that developer’s often forget).
Thanks for a good reminder to all of us.
@Justin, yeah I saw that one but as mentioned by Ben I like the exception handling a bit better when you’re using a simple list. You could always wrap a try/catch around the <cfparam> but I think the “list” way is a bit more readable.
Re: the 2nd post…yep that is cool, I’ll have to remember that!
@Mike, thanks for your comment. Agreed that we all need reminding on these types of things every now and then
I think we need a specific post on <cfprocpram> as well for those using stored procedures. Same in theory but it’s always nice to see examples.
How about this article by Pete Freitag that addresses the times when you can NOT use CFQUERPYPARAM: http://www.petefreitag.com/item/677.cfm
@Sebastiaan - thank for that, Pete is a master and that’s yet another good resource.
Speaking of his last example I prefer this way of handling NULLs in cfqueryparams:
http://www.chapter31.com/2007/02/04/cfqueryparam-and-conditional-handling-of-nulls/