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? > > > >-- >Regards; > >Richard Meredith-Hardy >------------------------------------------------------------- >[EMAIL PROTECTED] >Tel: + 44 (0)1462 834776 FAX: + 44 (0)1462 732668 > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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
