The Latest...

One of the most important parts of writing database-driven web pages is securing your queries.  Hopefully everyone now is familiar with SQL injection, and each language has its methods for curtailing it.  In ColdFusion, we have <cfqueryparam>, which is super cool, because it's pretty flexible and actually makes writing queries easier.  But the tag is not all-powerful, and one of the places where it's pretty much useless is in the ORDER BY clause.

To get around this, there are a few options.  One is to have various IF statements, which can get quite cluttered.  The method I prefer is to use a ListFind().  You basically supply a list of acceptable values, and if you have a match, you order by it, otherwise you order in some predetermined way.

SELECT firstname, lastname, address
FROM staff
ORDER BY <cfif listfind(URL.order,"firstname,lastname,address")>#URL.order#<cfelse>lastname</cfif>

This works well, except there's one thing I don't like about it.  If you write it in the above way, you're essentially advertising some of your column names.  That gives a wouldbe hacker some extra info he probably doesn't need to have, and that's not necessary.  I prefer something like this:

SELECT firstname, lastname, address
FROM staff
ORDER BY <cfif listfind(URL.order,"f,l,a")>#replacelist(URL.order,"f,l,a","firstname,lastname,address")#<cfelse>lastname</cfif>

This accomplishes the same thing as the first example, but hides the column names.  It also shortens your URL by allowing you to just pass a single letter.  Handy!