Cfqueryparam Does Not Work in ORDER by, What Are My Options?
For those that don’t know, ColdFusion’s cfqueryparam won’t work on a SQL ORDER BY clause, so the following will not work:
1 2 3 4 5
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:
1 2 3 4 5 6 7 8 9 10 11 12 13
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
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!!!