> 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.
It's not really hacking the code, so much as working around a
deficiency in SQL. Eitherway, it is definitely a better way than what
you're trying to do.
The code you've currently got, even with the bug was fixed, is slow
and insecure.
Try this instead:
<cfquery name="qGetAllForumPosts" datasource="#request.dsn#">
SELECT *
FROM discussionForum
WHERE 1=1
<cfif StructKeyExists(form,'name') AND Len(Trim(form.name))>
AND name LIKE <cfqueryparam value="%#form.name#%"
cfsqltype="CF_SQL_VARCHAR"/>
</cfif>
<cfif StructKeyExists(form,'email') AND Len(Trim(form.email))>
AND email LIKE <cfqueryparam value="%#form.email#%"
cfsqltype="CF_SQL_VARCHAR"/>
</cfif>
<cfif StructKeyExists(form,'title') AND Len(Trim(form.title))>
AND title LIKE <cfqueryparam value="%#form.title#%"
cfsqltype="CF_SQL_VARCHAR"/>
</cfif>
<cfif StructKeyExists(form,'id') AND Len(Trim(form.id))>
AND id = <cfqueryparam value="%#form.id#%"
cfsqltype="CF_SQL_INTEGER"/>
</cfif>
ORDER BY submit_date DESC
</cfquery>
(And don't forget to replace * with the exact column names you need)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:3053
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
