你如何在ORDER BY子句中使用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.

我正在努力成为一名优秀的CF Web开发人员,并使用围绕所有形成我的SQL查询的FORM或URL元素。

In this case, I'm trying to allow a user to control the ORDER BY clause dynamically.

在这种情况下,我试图允许用户动态控制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>

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.

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

Any suggestions on how to do this safely?

有关如何安全地做到这一点的任何建议?

5 个解决方案

#1

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

遗憾的是,您不能直接在Order By子句中使用CFQUERYPARAM。

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但仍然安全地使用Order By,可以设置CFSWITCH或类似的结构来根据某些条件(例如,URL变量)更改SortBy变量。与往常一样,不要直接从用户传递任何值,只需查看用户的输入并根据该输入从预定的可能值列表中进行选择。然后,只需使用标准语法:

ORDER BY #SortBy#

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

我将扩展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>

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

使用序列引用的序数值的问题是(我相信)执行create table SQL语句时的序数值 - 因此随着时间的推移将列添加到数据库表中,您使用的GUI工具显示列可能不代表其实际序数值。我真的会远离使用cfqueryparam。

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.

我喜欢在请求变量(url,form)中使用数字来指定要排序的列,然后在交换机中使用它并将其转换为实际列名称的想法 - 所以你不要将列名暴露给用户。

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.

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

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

至于在哪里使用cfqueryparam,它可以使用SQL绑定变量...

hth jon

#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).

关于在MySQL的订单条款中使用“cfqueryparam”的评论。是的,我相信MySQL数据源是允许的。虽然使用列序号,而不是列名(它似乎被视为常量字符串)。

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

不幸的是,它似乎根本不适用于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#">

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.

是的,我同意sql注入保护不是cfqueryparam的主要目的。所以绑定变量的描述是一个很好的补充。

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

sortBy和sortDirection通过URL或其他地方进入。

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

我喜欢这个,因为它很干净,你不能通过ORDER BY子句注入任何东西。

Any comments?

#1