My mistake, however it wouldn't solve my particular problem because this
particular app is recording a 'history' of changes {"x changed from y
to z"). Yes, I know I could do this with triggers / stored procedures
but in this case it would be awfully complicated, so instead I am
evaluating the form returns and only updating fields which have changed
[to acceptable values], and also writing to a history log. The entire
thing is configured from a single structure of parameters so it is
fairly simple to manage.
To update only the fields with valid alterations I am building a query
string on the fly "fieldx = 'newvalue', fieldy = 'other new value',
fieldz = new date" etc. which is eventually put into the UPDATE query.
Single quotes input by the user in text fields were causing problems
because of their reserved use in SQL but not any other characters I can
find.
Jochem van Dieten wrote:
>
> Richard Meredith-Hardy wrote:
> >
> > <CFQUERYPARAM only works after the WHERE, this is not the problem I
> > have....
>
> That is incorrect, you can use it anywhere.
>
> > Second off, in a dynamic query like below, you need the
> > preservesinglequotes() so the single quotes 'surrounding' the text value
> > are not escaped automatically, however this will also preserve single
> > quotes in the body of the text value which will cause an error. The fix
> > is to escape any single quotes in the body of the text value before you
> > give it to preservesinglequotes(). Once I realized what I wanted, a very
> > simple UDF did the trick (easier to read and I will use it a lot):
> >
> > <CFSCRIPT>
> > function EscapeSingleQuotes(x)
> > {
> > return replace(x,"'","''","all");
> > }
> > </CFSCRIPT>
> >
> > <CFLOOP LIST="#form.rowlist#" INDEX="idx">
> > <CFSET tempVar = "fieldstr = '#EscapeSingleQuotes(form["fieldstr_" &
> > idx])#'">
> >
> > <CFQUERY NAME="uddata" DATASOURCE="#myDS#">
> > UPDATE testtable
> > SET #preservesinglequotes(tempVar)#
> > WHERE rowref = #idx#
> > </CFQUERY>
> > </CFLOOP>
>
> Escaping single quotes often is not enough, many database have
> escape characters like "\". You really should use cfqueryparam:
>
> <CFLOOP LIST="#form.rowlist#" INDEX="idx">
> <CFQUERY NAME="uddata" DATASOURCE="#myDS#">
> UPDATE testtable
> SET fieldstr = <cfqueryparam cfsqltype="cf_sql_varchar"
> value="#form["fieldstr_" & idx]#">
> WHERE rowref = <cfqueryparam cfsqltype="cf_sql_integer"
> value="#idx#">
> </CFQUERY>
> </CFLOOP>
>
> Jochem
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm?link=i:4:137438
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
