如何在ORDER BY子句中使用cfqueryparam？ [英] How do you use cfqueryparam in the ORDER BY clause?

问题描述

我想成为一个很好的CF web开发人员，并使用< cfqueryparam> 周围的所有FORM或URL元素，使其对我的SQL查询。

在这种情况下，我试图允许用户动态控制ORDER BY子句。

 < cfquery datasource =MyDSNname =qIncidents> 
 SELECT IncidentID，AnimalID，IntakeDate，DxDate，OutcomeDate 
 FROM事件
 WHERE ShelterID =< cfqueryparam cfsqltype =cf_sql_integervalue =＃Arguments.ShelterID＃& 
 ORDER BY< cfqueryparam cfsqltype =cf_sql_varcharvalue =＃SortBy＃> 
< / cfquery> 
  

当我这样做时，会出现以下错误：

由ORDER BY编号1标识的SELECT项包含一个变量作为标识列位置的表达式的一部分。仅当通过引用列名称的表达式进行排序时，才允许变量。

有关如何安全地执行此操作的任何建议吗？

解决方案

不幸的是，您不能在Order By子句中直接使用CFQUERYPARAM。

如果您想动态使用Order By，安全地，您可以设置CFSWITCH或类似的结构，以根据一些条件（例如，URL变量）更改SortBy变量。和往常一样，不要直接从用户传递任何值，只需查看用户的输入并从预定的可能值列表中选择。然后，只需使用标准语法：

  ORDER BY＃SortBy＃
  pre> 
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?
解决方案
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#


                        
这篇关于如何在ORDER BY子句中使用cfqueryparam？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！