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

问题描述

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

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.

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

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:

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

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?

推荐答案

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

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

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

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