Coldfusion SQL 错误

发布于 2024-11-30 19:01:14 字数 1081 浏览 1 评论 0原文

我们有一个脚本，可以动态循环表单元素并为 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>

插入 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','已确认','ABCD4321',' 0.00','1 主路','已完成','windows-1252','10505','Bob O\'Malley') ------------------------- ---- 执行数据库查询时出错。（错误：“08”处或附近的语法错误）

原文

We have a script that dynamically loops through form elements and creates an INSERT statement for our PostgreSQL database. I'm escaping all single quotes from field values but the query itself still fails. BUT, if I run the query directly in pgAdmin, it works! I am lost as to why.

<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') ----------------------------- Error Executing Database Query. (ERROR: syntax error at or near "08")

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

终陌 2024-12-07 19:01:14

（错误：“08”处或附近的语法错误）

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

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

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

更新：同样使用 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>
)

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

CF automatically doubles (escapes) single quotes in cases like yours to help prevent sql injection. So the final VALUES clause sent to the database is actually more like this. Notice each single quote is doubled?

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

If that is indeed the problem, PreserveSingleQuotes() will fix it. However, it also reverses CF's automatic sql injection protection. Since you are treating all of the values as strings anyway, you may as well try using cfqueryparam instead. You do not get much in the way of type checking. But you can still loop through the fields dynamically, while maintaining sql injection protection. Possibly boosting performance as well.

UPDATE: Also with cfqueryparam, you should not have to worry about manually escaping single quotes anymore.

UPDATE: To use cfqueryparam, populate an array with the existing fields to insert.Then loop through the array to generate your VALUES clause. I made a few assumptions, but this should produce the desired results.

<!--- 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>
)

回复收藏 0 原文