Actually, there was a slight change in behavior with CFMX. In CF5 (and prior), automatic SQL string quoting would not happen if a function was used against the data (in this case, the Left() function). In CFMX, the strings are properly escaped, even if a function is used first.
So, you should instead have: WHERE NAME LIKE '%#preserveSingleQuotes(left(table1query.name,5))%#' ...as Ryan suggested. --Daryl ----- Original Message ----- From: "Doug Teetzen" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, August 12, 2002 10:28 AM Subject: [KCFusion] CFMX Possible Bug HELP! > I am having terrible luck trying to resolve an issue that came up this > weekend. > It appears that using certain expressions within CFMX are not properly > escaping quotes in strings. > > Objective= Find possible dupe records before inserting a record from Table1 > into Table2 > > Sample Data In Database #1 and #2: > > Name= Dick's Sporting Goods ZIPCode=00101 > > I have a loop over the data in table1 that queries against table2 in the > following way > Sample Query1: > SELECT ID,NAME > FROM TABLE1 > > Loop Start > > Sample Query2: > SELECT ID > FROM TABLE2 > WHERE NAME LIKE '%#left(table1query.name,5)%#' (checking the first 5 chars > only in comparison) > > **So the resulting query is as follows from debug output** > Problem Query Sample: > SELECT ID > FROM TABLE2 > WHERE NAME LIKE '%Dick'%#' > ** > End Loop > > What happens is that it blows up when there is a single quote in the name > result from table1 and it is used as a parm to query table2 (in an > expression). > > >From the debug output, it appears that it may be an error in how it is > passed from CFMX to the JDBC:SqlServer driver. > > Any help or contacts anyone can offer would be GREATLY appreciated..... > > Doug Teetzen > Bushnell Performance Optics > > > > ______________________________________________________________________ > The KCFusion.org list and website is hosted by Humankind Systems, Inc. > List Archives........ http://www.mail-archive.com/cf-list@kcfusion.org > Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] > To Subscribe.................... mailto:[EMAIL PROTECTED] > To Unsubscribe................ mailto:[EMAIL PROTECTED] > > ______________________________________________________________________ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives........ http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe.................... mailto:[EMAIL PROTECTED] To Unsubscribe................ mailto:[EMAIL PROTECTED]
