如何转义使用ValueList生成的cfparam VARCHAR列表中的逗号

Question

假设有一个SQL表Fruit

id | name
--- ------
 1 | 'apples'
 2 | 'pears'
 3 | 'kiwi'
 4 | 'bananas, peaches and plumbs'

给定以下查询

<cfquery name="qAllFruit" datasource="#DSN#">
    SELECT name FROM Fruit
</cfquery>
<cfquery name="qLeftoverFruit" datasource="#DSN#">
    SELECT name FROM Fruit
     WHERE name NOT IN (<cfqueryparam CF_SQL_TYPE="CF_SQL_VARCHAR" 
                                      value="#ValueList(qAllFruit.name)#" 
                                      list="yes" />)
</cfquery>

然后qLeftoverFruit将错误地返回 1 行：bananas, peaches and plumbs因为扩展的cfqueryparam列表被错误地解释：

WHERE name NOT IN ('apples','pears','kiwi','bananas','peaches and plumbs')

有没有办法在仍然使用 cfqueryparam 标签和ValueList的同时纠正此问题？

更新 以下是可用于重现此问题的要点：http://gist.github.com/a642878c96b82b21b52c

Answer 1

@Daniel孟德尔，我认为问题出在ColdFusion使用的默认分隔符上。您的数据中包含"，"，顺便说一下，CF 中的缺省分隔符是"，"。

像这样更改您的查询 -

WHERE name NOT IN ( <cfqueryparam CFSQLType="CF_SQL_VARCHAR" 
                       value="#ValueList(qTags.tag,';' )#" 
                       list="Yes" separator=";" />
                  )

只需将 valueList 中的分隔符从默认的"，"更改为";"，并将 QueryParam 分隔符设置为";"。

Answer 2

这个问题提供了两个例子。 从 CF 退后一步，考虑在一个查询中单独完成这项工作。 对于问题中的查询：

<cfquery name="qLeftoverFruit" datasource="#DSN#">
SELECT name FROM Fruit
WHERE name NOT IN (SELECT name FROM Fruit)
</cfquery>

使用 github.com 中的示例：

<cfquery name="qTest" dbtype="query">
SELECT id FROM qTags
WHERE tag IN (SELECT tag FROM qTags)
</cfquery>

这些单个查询实质上执行两个查询方法的逻辑。 它消除了使用 cfqueryparam 的需要，处理潜在的巨大列表以及确定"安全"分隔符的陷阱。

但是，我不确定为什么需要此查询。 它应始终返回空查询（来自问题的查询）或所有记录（来自 github.com 的查询）。 两个查询之间是否存在原始问题中未提及的中间步骤？