如何在ORDER BY子句中使用cfqueryparam？

Question

我正在努力成为一名优秀的CF Web开发人员，并在所有传递到我的SQL查询中的FORM或URL元素周围使用<cfqueryparam>。

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

<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>

当我执行此操作时，会出现以下错误：

按 ORDER BY 编号 1 确定的 SELECT 项包含作为表达式标识列位置一部分的变量。只有在按引用列名称的表达式排序时才允许使用变量。

您有什么安全的建议吗？

- Chris Brandt

---

你使用的是哪个数据库？我可以在MySQL 5中很好地使用cfqueryparam。 - Sam Farmer

Answer 1

很遗憾，您不能直接在Order By子句中使用CFQUERYPARAM。

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

ORDER BY #SortBy#

- CloudyMusic

---

一个好的思考 cfqueryparam 可以和不能用的方法是从数据库中的数据角度来考虑。sortBy 变量代替了列，而不是列中的数据。你也不能使用 queryparam 来参数化表或列列表。 - Ryan Guill

你使用的是哪个数据库？我可以在MySQL中使用cfqueryparam在order子句中。 - Sam Farmer

Answer 2

我会扩展一下Aaron的回答。我的做法是使用listfindnocase()函数来确保传递给order by子句的参数是有效的：

<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>

- rip747

---

我同意。那是一个好方法。我稍微改进了你的代码，添加了明确安全且永远不会保存用户指定值的变量。我认为这不值得单独回答，而且更容易向你展示，而不是在评论中尝试解释。如果你不喜欢我的做法，请回滚它。 - Patrick McElhaney

Answer 3

使用列的序号值作为列引用存在问题，因为它（我相信）是在执行创建表 SQL 语句时的序数值 - 因此随着时间推移向数据库表添加列，用于显示列的 GUI 工具可能不会表示其实际序数值。我真的建议避免在此情况下使用 cfqueryparam。

我确实喜欢使用请求变量（url、form）中的数字来指定按哪一列排序，然后在 switch 中使用它并将其转换为实际的列名 - 这样就不会向用户公开列名。

至于何时/为什么要使用 cfqueryparam，请记住它不仅仅是关于输入验证和防止 SQL 注入（虽然这是一个非常好的额外优势） - 使用 cfqueryparam 后，数据库发送回驱动程序的基础 SQL 使用 SQL 绑定变量 - 占位符值，因此数据库优化器可以以更通用的格式确定使用哪个索引......因此，当您发送像这样的 SQL 语句：SELECT * FROM product WHERE ID=1 and SELECT * FROM product WHERE ID=2 时，优化器会运行两次。但是使用绑定变量，SQL 看起来像这样 SELECT * FROM product WHERE ID=? (?=1) and SELECT * FROM product WHERE ID=? (?=2)，因此优化器可以使用第一次分析的缓存结果来知道在第二个查询上使用哪个索引。根据 SQL 和数据库的复杂性，这可以节省大量时间。在我使用 Oracle 数据库和 where 子句中的日期/时间列时，从性能方面来说非常有用。

因此，在何处使用 cfqueryparam，就是可以使用 SQL 绑定变量的地方。

希望对您有所帮助。 Jon

- Jon Briccetti

Answer 4

关于使用“在MySQL中的order子句中使用cfqueryparam”这一评论。是的，我相信在MySQL数据源中允许使用。但是要使用列序号，而不是列名（似乎被视为常量字符串）。

不幸的是，对于MS SQL数据源来说，它似乎根本不起作用。至少从我所知道的来看是这样的。

<!--- 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#">

更新：关于序数的评论：不，我认为它指的是选择列表中的列位置，而不是底层表。所以这应该没问题。

是的，我同意防止 SQL 注入并不是 cfqueryparam 的主要目的。因此，绑定变量的描述是一个很好的补充。

- Leigh

---

就我所见，答案没有任何错误。匿名的投票者能否留下评论？ - Leigh

也许 ORDER BY 3 看起来有些混乱。如果它在 MySQL 上运行，并且您注意到它在 MySQL 上运行，则这是解决问题的一种优雅方法。 - James A Mohler

实际上，使用默认的数据库驱动程序可以与MySQL一起使用，但不能与MS SQL一起使用。当涉及到使用序数时，人们往往要么喜欢它，要么讨厌它。我有复杂的感觉。它比其他选择更优雅，但并不总是那么直观。回顾过去，我怀疑我发布这篇文章是为了反驳几个笼统的说法，即它从来不可能，这并不完全正确。这取决于驱动程序。有些支持它，有些不支持。 - Leigh

Answer 5

我想用更少的代码解决这个问题：

<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>

sortBy和sortDirection通过URL或其他方式传入。

我喜欢这种方法，因为它很干净，无法通过ORDER BY子句注入任何内容。

有什么评论吗？

- Adrian Lynch