Building the where clause using CF

[Neil Grimes]

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:3052
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6