如何在 ColdFusion 中覆盖 SQL 清理 [英] How to override SQL sanitization in ColdFusion

问题描述

我的不幸任务是清理一堆旧的 ColdFusion 代码.查询无处不在，我正在努力将它们全部转移到常见的 CFC 上以便于维护.

I have the unfortunate task of cleaning up a bunch of old ColdFusion code. Queries are all over the place, I am working on moving them all to common CFCs for easier maintenance.

我遇到了一个问题，因为 cfquery 会自动将单引号转换为双单引号.如何覆盖该行为?

I am running into a problem because cfquery is automatically converting the single quotes to double-single-quotes. How can I override that behavior?

更具体的信息如下.

所以这是我开始的查询:

So here is the query I started with:

<cfquery name="getObjectInfo" datasource="#BaseDS#">
  SELECT groupName AS lastname, '[Group]' AS firstname
  FROM   groups
  WHERE  groups.group_id = #objectreference_id#
</cfquery>

这里奇怪的是，由于我们希望它显示的方式，文字被选择"了(再说一遍，我没有写这个，我只是想稍微清理一下).所以在普通函数中，有一个select子句的可选参数:

The weird thing here is that a literal is being "selected", because of the way we want it displayed (again, I didn't write this, I'm just trying to clean it up a little). So in the common function, there is an optional parameter for the select clause:

  <cffunction name="fSelGroup" access="public" returntype="query"
              hint="Returns query selecting given group.">

    <cfargument name="intGroupID" type="numeric" required="true"
                hint="ID of group to be returned." />
    <cfargument name="strSelectAttributes" type="string" required="false"
                hint="Attributes to be selected in query"
                default="*" />

    <cfquery name="getObjectInfo" datasource="#Application.DataSource#">
      SELECT #Arguments.strSelectAttributes#
      FROM   Groups
      WHERE  Group_ID = #Arguments.intGroupID#
    </cfquery>

    <cfreturn getObjectInfo />

  </cffunction>

问题出在这里:当我为 strSelectAttributes 参数传入 "GroupName AS LastName, '[Group]' AS FirstName" 时，发送到的查询数据库是:

Here is the problem: When I pass in "GroupName AS LastName, '[Group]' AS FirstName" for the strSelectAttributes parameter, the query that is sent to the database is:

SELECT GroupName AS LastName, ''[Group]'' AS FirstName
FROM   Groups
WHERE  Group_ID = 4 

你看，我的引用被清理"成无效查询.

You see, my quotes got "sanitized" into an invalid query.

推荐答案

ColdFusion 不会转义 all 单引号，而只会转义那些通过变量插值到达查询的单引号.这是罪犯:

ColdFusion does not escape all single quotes, but only those that arrive in the query through variable interpolation. This is the offender:

SELECT #Arguments.strSelectAttributes#

这通常是一件有用的事情，也是抵御 SQL 注入攻击的一小道防线.所以第一条规则是(这里和其他地方):不要从变量构建你的 SQL 字符串.

This is usually a helpful thing and a small line of defense against SQL injection attacks. So rule number one is (here and everywhere else): Don't build your SQL string from variables.

如果您确实必须使用变量来构建 SQL 字符串，尽管有所有可能的负面影响，请使用 PreserveSingleQuotes() 函数:

If you positively have to use variables to build an SQL string, despite all the possible negative effects, use the PreserveSingleQuotes() function:

SELECT #PreserveSingleQuotes(Arguments.strSelectAttributes)#

此函数阻止 ColdFusion 自动转义单引号.

This function stops ColdFusion from auto-escaping the single quotes.

顺便说一句，任何其他函数调用都会做同样的事情.试试:

And any other function call does the same thing, by the way. Try:

SELECT #LCase(Arguments.strSelectAttributes)#

这意味着 PreserveSingleQuotes() 实际上只是一个将字符串转换为函数结果的空操作，从而防止发生自动变量插值例程.

which means that PreserveSingleQuotes() is really just a no-op that turns a string into a function result, preventing the automatic variable interpolation routine from happening.

这篇关于如何在 ColdFusion 中覆盖 SQL 清理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！