#preserveSingleQuotes(sql_statement)# On 8/7/06, David Carter <[EMAIL PROTECTED]> wrote: > 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 > > Thanks, > David > >
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:249042 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
