Thanks for your answers
<CFQUERYPARAM only works after the WHERE, this is not the problem I
have....
However having struggled with this for quite some time, I have actually
found a solution.
First off the double quotes problem wasn't a query problem, it was a
display problem, easily solved with HtmlEditFormat()
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>
Raymond Camden wrote:
>
> Actually cfqueryparam suffers from the same issue. You still need to use
> a temp var before the actual query.
>
> Richard, does this problem go away if you get rid of the evaluate? You
> don't need it anyway. I notice in one code block you used it, one you
> didn't. Also, why do you use preserveSingleQuotes? Don't you want CF to
> automatically escape thje for you?
>
> ========================================================================
> ===
> Raymond Camden, ColdFusion Jedi Master for Mindseye, Inc
> (www.mindseye.com)
> Member of Team Macromedia (http://www.macromedia.com/go/teammacromedia)
>
> Email : [EMAIL PROTECTED]
> Blog : www.camdenfamily.com/morpheus/blog
> Yahoo IM : morpheus
>
> "My ally is the Force, and a powerful ally it is." - Yoda
>
> > -----Original Message-----
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> > Sent: Wednesday, September 17, 2003 8:13 AM
> > To: CF-Talk
> > Subject: RE: Quotes in dynamic queries
> >
> >
> > look at using cfqueryparam. It will escape stuff
> > automagically. I've also used HTMLEditFormat() before too.
> >
> > Doug
> >
> > >-----Original Message-----
> > >From: Richard Meredith-Hardy [mailto:[EMAIL PROTECTED]
> > >Sent: Wednesday, September 17, 2003 7:31 AM
> > >To: CF-Talk
> > >Subject: Quotes in dynamic queries
> > >
> > >
> > >Dear clever people... there must be a simple solution...??. (CF 5)
> > >
> > >Consider a dynamically generated form which returns many
> > repeating text
> > >fields appended with an index number thus "form.fieldstr_1",
> > >"form.fieldstr_2" Etc. and a single hidden field "form.rowlist"
> > >containing a list of all the index numbers. We can evaluate
> > it easily
> > >by looping over the contents of "form.rowlist".
> > >
> > >This works, but will fail if a single quote is included in a form
> > >value. Known CF problem.
> > >
> > ><CFLOOP LIST="#form.rowlist#" INDEX="idx">
> > > <CFQUERY NAME="uddata" DATASOURCE="#myDS#">
> > > UPDATE testtable
> > > SET fieldstr = '#form["fieldstr_" & idx]#'
> > > WHERE rowref = #idx#
> > > </CFQUERY>
> > ></CFLOOP>
> > >
> > >so we can fix it by taking the evaluation out of the CFQUERY block:
> > >
> > ><CFLOOP LIST="#form.rowlist#" INDEX="idx">
> > >
> > > <CFSET tempVar = form["fieldstr_" & idx]>
> > > <!--- or you could use the less effecient
> > > <CFSET tempVar = evaluate("form.fieldstr_#idx#")> --->
> > >
> > > <CFQUERY NAME="uddata" DATASOURCE="#myDS#">
> > > UPDATE testtable
> > > SET fieldstr = '#tempVar#'
> > > WHERE rowref = #idx#
> > > </CFQUERY>
> > ></CFLOOP>
> > >
> > >This accepts as many single quotes as you like, but both of
> > the above
> > >examples truncate the form value at the first instance of a double
> > >quote ", despite CFDUMP suggesting the entire value is being
> > correctly
> > >returned.
> > >
> > >How to fix this?
> > >
> > >....But I don't really want to do either of the above, but for
> > >reasons I
> > >won't explain here, I really want to do a completely dynamic query:
> > >
> > ><CFLOOP LIST="#form.rowlist#" INDEX="idx">
> > > <CFSET tempVar = "fieldstr = '#form["fieldstr_" & idx]#'">
> > >
> > > <CFQUERY NAME="uddata" DATASOURCE="#myDS#">
> > > UPDATE testtable
> > > SET #preservesinglequotes(tempVar)#
> > > WHERE rowref = #idx#
> > > </CFQUERY>
> > ></CFLOOP>
> > >
> > >How to deal with single quotes and the double quotes
> > truncation -in the
> > >value- with this? Can you?
> > >
> > >
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm?link=i:4:137318
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
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
