You need to use CFQUERYPARAM or extensively edit your form fields. I think
the latter is almost impossible. To allow the use of CFQUERYPARAM build the
query dynamically inside the CFQUERY tag.
<!-- set the defaults -->
<cfparam name="form.name" default="">
<cfparam name="form.title" default="">
<cfparam name="form.email" default="">
<cfparam name="form.id" default="">
<!-- do the query -->
<cfquery name="qGetAllForumPosts" datasource="#request.dsn#">
SELECT *
FROM discussionForum
WHERE 1 = 1
<cfif Len(Trim(form.name)) GT 0>
<cfset TName=Trim(Form.Name)>
AND name = <cfqueryparam value="%#PreserveSingleQuotes(TName)#%"
cfsqltype="CF_SQL_VARCHAR">
</cfif>
<cfif Len(Trim(form.email)) GT 0>
<cfset TEMail=Trim(Form.Email)>
AND email = <cfqueryparam value="%#PreserveSingleQuotes(TEMail)#%"
cfsqltype="CF_SQL_VARCHAR">
</cfif>
<cfif Len(Trim(form.title)) GT 0>
<cfset TTitle=Trim(Form.Title)>
AND title = <cfqueryparam value="%#PreserveSingleQuotes(TTitle)#%"
cfsqltype="CF_SQL_VARCHAR">
</cfif>
<cfif Len(Trim(form.id)) GT 0>
AND id = <cfqueryparam value="#Trim(form.id)#"
cfsqltype="CF_SQL_VARCHAR">
</cfif>
ORDER BY submit_date DESC
</cfquery>
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:3063
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
