How do you use cfqueryparam in the ORDER BY clause?

Question

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?

Answer 1

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#

Answer 2

http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html

Answer 3

I'll just expand on Aaron's answer. One of the things that I do is to use listfindnocase() to make sure that the arguments passed to the order by clause are valid:

<cfset variables.safeSortColumn = "name">
<cfset variables.safeSortOrder = "desc">

<cfparam name="url.sortcolumn" type="string" default="#variables.safeSortColumn#">
<cfparam name="url.sortorder" type="string" default="#variables.safeSortOrder#">

<cfif listfindnocase("name,age,address", url.sortcolumn)>
    <cfset variables.safeSortColumn = url.sortcolumn>
</cfif>

<cfif listfindnocase("desc,asc", url.sortorder)>
    <cfset variables.safeSortOrder = url.sortorder>
</cfif>

<cfquery>
select *
from mytable
order by #variables.safeSortcolumn# #variables.safeSortorder#
</cfquery>

Answer 4

Regarding the comment about using "cfqueryparam in the order clause fine with MySQL". Yes, I believe it is allowed with MySQL datasources. Though using the column ordinal, not column name (which seems to be treated as a a constant string instead).

Unfortunately, it does not seem to work at all for MS SQL datasources. At least not from what I can tell.

<!--- this works --->
<cfset url.sortColumnNumber = "3">
<cfquery name="getDataByPosition" datasource="MySQLDSN">
   SELECT  RecordID, ProductName, DateAdded
   FROM TestTable
   ORDER BY <cfqueryparam value="#url.sortColumnNumber#" cfsqltype="cf_sql_integer"> ASC
</cfquery>
<cfdump var="#getDataByPosition#">

<!--- this does NOT work --->
<cfset url.sortColumnName = "DateAdded">
<cfquery name="getDataByName" datasource="MySQLDSN">
   SELECT  RecordID, ProductName, DateAdded
   FROM    TestTable
   ORDER BY <cfqueryparam value="DateAdded" cfsqltype="cf_sql_varchar"> ASC
</cfquery>
<cfdump var="#getDataByName#">

Update: Regarding the comments about ordinal: No, I believe it refers to the column position in the select list, not the underlying table. So it should be fine.

Yes, I agree sql injection protection is not the main purpose of cfqueryparam. So the description of bind variables was a good addition.

Answer 5

the problem with using the ordinal value for a column reference is it is (i believe) the ordinal value at the time the create table SQL statement was executed - so as you add columns to the database table over time, the GUI tool you use to display the columns may not represent its actual ordinal value. i would really stay away from using cfqueryparam for this.

i DO like the idea of using a number in the request variables (url,form) to specify which column to sort by and then use that in the switch and translate it to an actual column name - so you dont expose your column names to the user.

as far as when/why to use cfqueryparam, keep in mind its NOT just about input validation and preventing SQL injection (although that is a very nice bonus) - with cfqueryparam the underlying SQL to the database is sent back through the driver using SQL bind variables - placeholder values, so the databse optimizer can determine which index to use in a more generic format... so when you send a SQL statement like this: SELECT * FROM product WHERE ID=1 and SELECT * FROM product WHERE ID=2 the optimizer runs both times. but with bind variables, the SQL looks like this SELECT * FROM product WHERE ID=? (?=1) and SELECT * FROM product WHERE ID=? (?=2) so the optimizer can used the cached results of the first analysis to know exactly what index to use on the second query. depending on the complexity of the SQL and the database this can be a HUGE savings in time. in my experience its very helpful performance wise with oracle and date/time columns in the where clause.

so as far as where to use cfqueryparam, its where a SQL bind variable can be used...

hth jon

Answer 6

Thought I'd throw lesser code at this problem:

<cfset sortColumns = {IncidentID = "IncidentID", AnimalID = "AnimalID", IntakeDate = "IntakeDate", DxDate = "DxDate", OutcomeDate = "OutcomeDate"}>
<cfset sortDirections = {ASC = "ASC", DESC = "DESC"}>

<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 #sortColumns[sortBy]# #sortDirections[sortDirection]#
</cfquery>

Where sortBy and sortDirection come in via the URL or where ever.

I like this because it's clean and you can't inject anything via the ORDER BY clause.

Any comments?