ColdFusion: Replacement for <cfupdate>

I've been writing ColdFusion for seven years - spanning back more versions than I can remember right now, and I've always held the CFINSERT and CFUPDATE tags in contempt. For the longest time, this was sheer snobbery on my part - anyone who can write actual SQL statements shouldn't depend on the Allaire/Macromedia provided crutches that were what I percevied those tags to be. Any developer that would listen to me would get long rants about how you should write your own INSERT and UPDATE statements inside a CFQUERY, rather than depend on the CFINSERT and CFUPDATE tags. You got much more control, and that usually ended up as more efficient code.

Well, snobbery gave way to genuine dislike for those two tags recently. I was asked to take a look at an existing CF app that was being moved from a CF5 server to a CFMX server. Once of the issues that was occuring was that when a form submited data to the MySQL database, extraneous garbage characters were getting prepended to the text. So data that was entered on the form as:

This is my text information

Was getting inserted into the database as:

??!'This is my text information

Obviously, this was less than ideal.

The database in question is MySQL v4.1, and the application server is ColdFusion MX 6.1 running on Windows 2003 Standard Edition. I tried manipulating the form variables prior to inserting the data to the database, and had no success. I then realized that the original developer had used CFUPDATE to insert the form data to the database, rather than custom SQL in a CFQUERY tag. I decided to try a CFQUERY tag instead, but ran into a large problem. The form was dynamic - there was no set list of variables that was going to be submitted. This was one of the few areas that CFUPDATE was actually useful. However, a test SQL statement showed that the culprit was indeed the CFUPDATE statement.

So how to recreate the dynamic nature of CFUPDATE within a standard CFQUERY tag? Like this:

<cfscript>
StructDelete(form, "fieldnames");
</cfscript>
<cfquery name="update_form_apt_condo" datasource="alpha">
update form_apt_condo
set
<cfloop collection=#form# item="ffield">
#ffield# = '#StructFind(form, ffield)#',
</cfloop>
ID = '#form.id#'
where ID = '#form.ID#'
</cfquery>

It's bit of a hack - particularly the redundant ID = '#form.id#' line at the end (that would also be included in the loop). But it does work like a charm. You can also remove any form fields you do not want submitted to the database by including them in the StructDelete statements at the top. You have to remove the fieldnames key from the form struct, or it will throw an error on execution. Also, this statement assumes that the database table has a key named 'ID'. That would have to be adjusted for tables with a different primary key column.