That's by design. ColdFusion is escaping your single quotes. You need to either wrap your string in the PreserveSingleQuotes() function or, better, use <cfqueryparam>
-----Original Message----- From: David Carter Sent: Monday, August 07, 2006 3:14 PM To: CF-Talk Subject: cfquery - single quotes become doubled Hello all, I am having a strange problem with a cfquery through SQL Server where I am building the SQL statement dynamically from form input. I wonder if anyone here has encountered and over come a similar error in the past, and could lead me toward a solution. When the SQL statement is placed into a <cfquery> tag, the single quotes which denote the string literal in the WHERE clause become doubled-up and cause a syntax error. Dumping the Variables scope gives the value of sql_stmt as: SELECT user_id, user_name, name_first, name_last, ssn_encrypted FROM tbl_user WHERE c_encrypted = 'KSNRUy1VUDcvIzI4PAo=' (the ending = is a part of the string literal) Cutting and pasting this value directly into SQL Server's Query Analyzer generates the single row which I am expecting. In ColdFusion however, I get this: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Line 1: Incorrect syntax near 'KSNRUy1VUDcvIzI4PAo'. The error occurred in C:\Webs\user\find\search_results.cfm: line 37 35 : <cfinclude template="#application.root#pop_up_variables.cfm"> 36 : <cfquery name="q_users_found" datasource="#application.db#"> 37 : #sql_stmt# 38 : </cfquery> 39 : <cfelse> ------------------------------------------------------------------------ -------- SQL SELECT user_id, user_name, name_first, name_last, c_encrypted FROM tbl_user WHERE c_encrypted = ''KSNRUy1VUDcvIzI4PAo='' (notice the single quotes are doubled) DATASOURCE base_db VENDORERRORCODE 170 SQLSTATE HY000 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:249044 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
