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?

Hispanicism

What database are you using? I can use cfqueryparam fine with MySQL 5 – Ravid

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#

Spragens

A good way of thinking about what you can and cannot use a cfqueryparam for is to think of it in terms of the data that is in the database. The sortBy variable is in place of a column, not data in the column. You also could not use a queryparam to parametrize the table or the column list. – Myer

What database are you using? I can use cfqueryparam in the order clause fine with MySQL – Ravid

Answer 2

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>

Pyuria

I agree. That's a good approach. I improved on your code a little bit, adding variables that are explicitly safe and can never hold user-specified values. I didn't think that was worth a separate answer, and it was easier to show you than try to explain in a comment. If you don't like what I did, please roll it back. – Defaulter

Answer 3

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

Syllabub

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.

Amann

Nothing inaccurate about the answer as far as I can see. Anonymous down voter care to leave a comment? – Amann

Perhaps the ORDER BY 3 through seemed confusion. If it works on MySQL and you note that it works on MySQL, this is an elegant solution to the problem – Mold

Actually, using the default database drivers it works with MySQL but not with MS SQL. When it comes to using oridinals, people tend to either love it or hate it. I have mixed feelings. It is more elegant than the alternatives, but not always as intuitive. Looking back, I suspect I posted this to rebut several blanket statements that it is never possible, which is not exactly true. It depends on the driver. Some support it, some do not. – Amann

Answer 5

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?

Freudberg