>-----Original Message-----
>From: Richard Meredith-Hardy [mailto:[EMAIL PROTECTED]
>Sent: Wednesday, September 17, 2003 11:55 AM
>To: CF-Talk
>Subject: Re: Quotes in dynamic queries [solved]
>
>
>Thanks for your answers
>
><CFQUERYPARAM only works after the WHERE, this is not the problem I
>have....
>
ugh no. You can use it pretty much anywhere I suppose. Here is some sample code
similar to what you are doing that I used. This has no issues with single ticks or
quotes.
<cfquery name="update" datasource="#request.datasource#">
UPDATE PRODUCTS
SET
NAME = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#form['name_#i#']#">,
POSCATEGORY = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#form['poscategory_#i#']#">,
ACTIONUSER = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#request.logemplid#">
WHERE PRODUCT = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#form['product_#i#']#">
</cfquery>
Doug
>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:137323
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
