CODEWITHSUNDEEP
sqlpostgresqlcoldfusion
justacoder
justacoder

Reputation: 2704

Coldfusion SQL Error

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

Upvotes: 0

Views: 350

Answers (3)

Leigh
Leigh

Reputation: 28873

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

Upvotes: 3

Eric Belair
Eric Belair

Reputation: 10692

What is the datatype of the column "TransDate"? Perhaps there's an issue with data type conversion?

Upvotes: 0

Henry
Henry

Reputation: 32915

use PreserveSingleQuotes() - http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-6cbf.html

Upvotes: 3

Related Questions