> -----Original Message----- > From: Philip Arnold - ASP [mailto:pra@;aspmedia.co.uk] > Sent: Monday, November 04, 2002 5:46 AM > To: CF-Talk > Subject: RE: HELP!! sql wierdness ERROR message > > > > Not to argue with Ben, but I don't think that > > PreserveSingleQuotes() is > > the answer... That function prevents CF from automatically esacping > > single quotes (in this case you WANT CF to escape them for you). > > I think you're confused a little with what CF terms as > "escaping" the single quotes > > It converts all single quote types to their real single quote > versions for SQL (used in WHERE and such), normally it won't > always put the apostrophies in for the INSERT and UPDATE, > it's almost a hit-and-miss thing > > This is why my wife suggested using Replace() combines with > PreserveSingleQuotes() as it's the only guaranteed way of > getting it working
Honestly I'm still a little confused here... I still don't think that PerserveSingleQuotes() does what you suggest. As far as I understand it (which may turn out to be not at all) CF will automatically escape single quotes in variables placed inside a cfquery. So something like this is legal: <cfset foo = "How're you doing today"> <cfquery> SELECT * FROM Table WHERE Value = '#foo#' </cfquery> CF will automatically escape the single quote in the variable and statement will pass. However using "PreserveSingleQuotes()" will (I think) cause the statement to fail as the single quote in the variable will be "exposed" to SQL and seen, erroneously, as the end of the content. The point is that in some cases you need CF not to escape automatically as in: <cfset foo="'hi','hello','howdy'"> <cfquery> SELECT * FROM Table WHERE Value IN (#PreserveSingleQuotes(aList)#) </cfquery> In the above statement CF, without the function, would have escaped the quotes and invalidated the list. So you need to explicitly tell it to preserve them. Am I wrong in this? Are we actually saying the same thing? Of course a lot of this is academic anyway. Jochem is correct of course that much (perhaps all of this) of this is now handled (better) with CFQUERYPARAM. The PreserveSingleQuotes() function was one of the original functions (I believe all the way back to version 1.0). It made sense when it was created but it really only makes sense now if you can't, for some reason, use CFQUERYPARAM. Jim Davis ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
