Use CFQUERYPARAM rather than the data from the forms. You are open SQL injection attacks and errors such as you are experiencing.
<cfset emailClause = "AND email = <cfqueryparam value='%#Trim(form.email)#%' cfsqltype='CF_SQL_VARCHAR'>"> Ray Thompson Tau Beta Pi (www.tbp.org) The Engineering Honor Society 865-546-4578 -----Original Message----- From: Neil Grimes [mailto:[EMAIL PROTECTED] Sent: Monday, April 28, 2008 5:52 AM To: SQL Subject: Building the where clause using CF I am getting the following error when I try to build the WHERE clause using cold fusion and searching for neil in the email clause. [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'neil'. Here is the sql from that page also. SELECT * FROM discussionForum WHERE email LIKE ''%neil%'' ORDER BY submit_date DESC Now I know it is simple to see the double quotes around the search term are the problem (''%neil%'') but there is no place in the code that this would happen. The actual whereClause has the value WHERE email LIKE '%neil%' So my guess is that this is happening inside the cfquery - but why and is there a fix? I know I could hack the code and just build a normal WHERE clause using WHERE 1=1 AND name = '%#form.name#%' AND email = '%form.email%' ..... but surely there must be a better way. Here is my code to build the query in a step by step way for each search clause from the form. <!-- set the defaults --> <cfparam name="nameClause" default=""> <cfparam name="titleClause" default=""> <cfparam name="emailClause" default=""> <cfparam name="idClause" default=""> <cfparam name="whereClause" default=""> <!-- set the whered var --> <cfset whered = "false"> <cfif isdefined("form.name") AND form.name NEQ ""> <cfset nameClause = "WHERE name = '%#form.name#%' "> <cfset whered = "true"> </cfif> <cfif isdefined("form.email") AND form.email NEQ ""> <cfif whered EQ "false"> <cfset emailClause = "WHERE email = '%#form.email#%'"> <cfset whered = "true"> <cfelse> <cfset emailClause = "AND email = '%#form.email#%' "> </cfif> </cfif> <cfif isdefined("form.title") AND form.title NEQ ""> <cfif whered EQ "false"> <cfset titleClause = "WHERE title = '%#form.title#%'"> <cfset whered = "true"> <cfelse> <cfset titleClause = "AND title = '%#form.title#%' "> </cfif> </cfif> <cfif isdefined("form.id") AND form.id NEQ ""> <cfif whered EQ "false"> <cfset idClause = "WHERE id = #form.id# "> <cfset whered = "true"> <cfelse> <cfset idClause = "AND id = #form.id# "> </cfif> </cfif> <cfset whereClause = "#nameClause# #emailClause# #titleClause# #idClause#"> <!-- do the query --> <cfquery name="qGetAllForumPosts" datasource="#request.dsn#"> SELECT * FROM discussionForum <cfif whereClause NEQ ""> #whereClause# </cfif> ORDER BY submit_date DESC </cfquery> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3054 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
