[thelist] CF query tip

[Judah McAuley]

<tip type="ColdFusion query escaping" author="Judah McAuley">
One of the nice things about ColdFusion is that it automatically escapes
strings that you are going to insert into a database.
One of the really irritating, want to smack their programmers about the
head things about ColdFusion is that it only *almost always* escapes
strings.  ColdFusion often has problems escaping strings correctly when
evaluating dynamic expressions within a <cfquery> block.

Example:

<cfif StructKeyExists(form, "fieldnames")>
	<cfloop list="#form.fieldnames#" index="field">
		<cfif FindNoCase( "cat_", field)>
			<cfset currentname = "name_" & ListLast(field, "_")>
			<cfif form[field] neq form[currentname]>
				<cfquery name="update_category" datasource="#request.datasource#"
dbtype="#request.dbtype#">
					UPDATE faq_category SET category = '#form[field]#'
					WHERE id = '#ListLast(field, "_")#'
				</cfquery>
				<cfset msg="Changes saved.">
			</cfif>
		</cfif>
	</cfloop>
</cfif>

This checks to see if the form structure exists, loops over the form, finds
category related fields, and updates them in the database.  Nice
straight-forward logic.  But if you submit a category that has an
apostrophe in it, (say Bob's FAQ) then the line UPDATE faq_category SET
category = '#form[field]#' will evaluate to UPDATE faq_category SET
category = 'Bob's FAQ' and will throw a database error.

There are two ways to handle this.

1) Set a variable before the <cfquery> so that the value you want to insert
is evaluated prior to being put in the where clause
<cfset tempvar = form[field]>
<cfquery name="update_category" datasource="#request.datasource#"
dbtype="#request.dbtype#">
	UPDATE faq_category SET category = '#tempvar#'
	WHERE id = '#ListLast(field, "_")#'
</cfquery>

2) Escape the string yourself manually replacing a single quote with two
single quotes
<cfquery name="update_category" datasource="#request.datasource#"
dbtype="#request.dbtype#">
	UPDATE faq_category SET category = '#Replace(form[field],"'","''","ALL")#'
	WHERE id = '#ListLast(field, "_")#'
</cfquery>

As far as I can tell, there is no reliable way to tell when CF is going to
forget to escape strings.  It's a hazard of interpreted languages and CF
appears to just occasionally get lost in its order of operations.  So if
you start seeing occasional, unexplained DB errors in code that otherwise
works, check to see if the data to be inserted has apostrophes in it and
implement solution 1 or 2 above.
</tip>