Search code examples
sqlcoldfusioncfquerycfqueryparam

How do you use cfqueryparam in the ORDER BY clause?

I'm trying to be a good CF web developer and use <cfqueryparam> around all FORM or URL elements that make it to my SQL queries.

In this case, I'm trying to allow a user to control the ORDER BY clause dynamically.

<cfquery datasource="MyDSN" name="qIncidents">
  SELECT IncidentID, AnimalID, IntakeDate, DxDate, OutcomeDate
  FROM Incidents
  WHERE ShelterID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Arguments.ShelterID#">
  ORDER BY <cfqueryparam cfsqltype="cf_sql_varchar" value="#SortBy#">
</cfquery>

When I do this, I get the following error:

The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

Any suggestions on how to do this safely?

Solution

  • Unfortunately, you can't use CFQUERYPARAM directly in the Order By clause.

    If you want to use the Order By dynamically but still do so safely, you can set up a CFSWITCH or similar structure to change your SortBy variable depending on some condition (say, a URL variable). As always, don't pass any values directly from the user, just look at the user's input and select from a predetermined list of possible values based on that. Then, just use the standard syntax:

    ORDER BY #SortBy#