building your SQL outside CFQUERY gotcha

This is the other gotcha that I had today.

I have encountered this before (spent hours on it) but I guess I just forgot about it.

I have encountered a situation today when I need to generate the SQL statement (using CFSAVECONTENT) and this is perhaps the only 2nd time that I need to do this. The main reason why I need to do this is because I want to not only run the query but to actually either save it to file or display it on the browser (although I suppose I can use cfquery result struct maybe?).

So below is the code:

<cfsavecontent>
	INSERT INTO tableName
	(
	<cfset counter = 1>
	<cfloop list=lFieldNames index="fieldName">
		<cfif counter gt 1>,</cfif>
		#fieldName#
		<cfset counter = coounter + 1>
	</cfloop>
	)VALUES(
	<cfset counter = 1>
	<cfloop list=lFieldNames index="fieldName">
		<cfif counter gt 1>,</cfif>
		'#StructFind( stFieldValue , fieldName )#'
		<cfset counter = coounter + 1>
	</cfloop>
	)
</cfsavecontent>
<cfquery name="qInsert" datasource="#thisDSN#">
	#strSQL#
</cfquery>

Running the code I got a database exception. Looking at the exception I found out that all the quotes has been doubled. So this when I remembered the ColdFusion’s function PreservesSingleQuotes. Wrapping the your generated SQL with the function solves the problem.

#PreservesSingleQuotes( strSQL )#