RE: [KCFusion] CFMX Possible Bug HELP!
Doug, I believe the 'preservesinglequotes' function in CF is intended to handle quoting problems like this. Ryan __ 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]
Re: [KCFusion] CFMX Possible Bug HELP!
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]
RE: [KCFusion] CFMX Possible Bug HELP!
YepTried that as well. That has not worked either.. It just does not seem that it is escaping them correctly at some point in the MX or JDBC driver process. Running the same code on CF5 does not have this problem. Doug __ 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]
Re: [KCFusion] CFMX Possible Bug HELP!
Actually, this is the exact opposite problem I thought it was at first glance. It's not that single quotes are being quoted that shouldn't, the problem is that single quotes are getting overlooked. Let me try to replicate that and get back to the list. --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]