sql - Coldfusion SQL 错误

Question

我们有一个动态循环遍历表单元素并为我们的 PostgreSQL 数据库创建 INSERT 语句的脚本。我正在转义字段值中的所有单引号，但查询本身仍然失败。但是，如果我直接在 pgAdmin 中运行查询，它就可以工作!我不知道为什么。

<cfset queryfields = "--already defined--">
<cfset queryvalues = "" />
 <cfloop list="#fieldList#" delimiters="," index="f">
 <cfif StructKeyExists(form, f)>
 <cfset queryvalues = queryvalues & "'" & Replace(form[f], "'", "\'", "all") & "'," />
 </cfif>
 </cfloop>
 <!--- Remove trailing commma --->
 <cfset queryvalues = Left(queryvalues, len(queryvalues) - 1) />

<cftransaction>
 <cfquery name="qInsertOrder" datasource="#DSN#">
 INSERT INTO foo_trans (#queryfields#)
 VALUES(#queryvalues#)
 </cfquery>
</cftransaction>

INSERT INTO paypal_trans (TransDate,mc_gross,address_status,payer_id,tax,address_street,payment_status,charset,address_zip,first_name) VALUES('08/22/2011','50.00','confirmed','ABCD4321 ','0.00','1 Main Road','Completed','windows-1252','10505','Bob O\'Malley') -------------- -------------- 执行数据库查询时出错。 (错误:“08”处或附近的语法错误)

Answer 1

(ERROR: syntax error at or near "08")

在像您这样的情况下，CF 会自动加倍(转义)单引号，以帮助防止 sql 注入(inject)。所以最终发送到数据库的 VALUES 子句实际上更像这样。注意到每个单引号都加倍了吗？

    VALUES(''08/22/2011'', .. 

如果这确实是问题所在，PreserveSingleQuotes() 将修复它。但是，它也逆转了 CF 的自动 sql 注入(inject)保护。由于您无论如何都将所有值都视为字符串，因此您不妨尝试使用 cfqueryparam。您不会在类型检查方面得到太多帮助。但是您仍然可以动态循环遍历字段，同时保持 sql 注入(inject)保护。也可能提高性能。

更新:同样使用 cfqueryparam，您不必再担心手动转义单引号了。

更新:要使用 cfqueryparam，请使用要插入的现有字段填充一个数组。然后遍历该数组以生成您的 VALUES 子句。我做了一些假设，但这应该会产生预期的结果。

<!--- determine the fields to insert --->
<cfset fieldArray = arrayNew(1)>
<cfloop list="#fieldList#" delimiters="," index="f">
 <cfif StructKeyExists(form, f)>
    <cfset arrayAppend(fieldArray, f)>
 </cfif>
</cfloop>


...
<!--- loop through array to generate VALUES clause --->
VALUES 
(
<cfloop from="1" to="#arrayLen(fieldArray)#" index="x">
    <!--- append separator when needed --->
    <cfif x gt 1>,</cfif>
    <cfqueryparam value="#FORM[fieldArray[x]]#" cfsqltype="cf_sql_varchar">
</cfloop>
)