Building Query criteria dynamically based on Form inputs

[Beards247]

I need to dynamically build a db query based on form inputs; this such a
typical thing I am hoping someone can shortcut me to a more elegant solution.
The user does not have to enter any of the 5 criteria and the Query only pulls
from one table, I just need to dynamically build the query criteria...

Where I am now - Once the user has submitted their criteria I put the form
variables into a list and use listlen(variable). to tell if there is 0, 1, gt
1 criteria

<cfifListLen(q_crit) gt 0>
SQL Statement
<cfif ListLen(q_crit) eq 1>
WHERE (cfif's to figure out which variable was entered)
<cfelseif ListLen(q_crit) gt 1>
WHERE
Dynamically format query criteria :confused;
</cfif>
</cfif>
I have the first two options ready, but dynamically creating the where clause
is giving me problems. There must be a better solution than writing out all the
possible permutaions... how do I dynamically write the clause so "AND" is
inserted after the first and subsequent variables but not after the last one?

I tried continually adding to a variable, but so far I haven't figured it out.

Any insights is greatly appreciated. If I managed to come up with a elegant or
fugly solution, I'll post the results

[MikerRoo]

There may be tutorials on [url]http://easycfm.com/[/url] and similar sites...

Generally, here are some guidelines:
1) Keep the Select clause the same -- not switched by If statements.
2) Look up and study SQL Injection attacks -- Someone WILL attempt this on
your site.
3) Use <CFQUERYPARAM> and/or stored procedure(s) (If using SP's be aware that
you may need the "with recompile" option).
3) Use a universal stub where clause, like the attached code:

Finally, don't put your form variables in a list and then try to parse them
back out -- this just needlessly complicates things.

Regards,
-- MikeR


<!--- IMPORTANT: In this example we use the form variables directly (for
simplicity).
In actual practice, you would not do this but instead: (A) <cfparam> the
vars,
(B) guard against cross-site scripting, (C) guard against SQL Injection,
(D) do any
business-rule checking, and (E) check field length if applicable.
--->

<CFQUERY name="qSomeQry" datasource=...>
SELECT
AAA, BBB, ... <!--- Fixed list of columns --->
FROM
Tables, etc..
WHERE
1=1 <!--- Legal, harmless and very useful. --->

<CFIF FORM.NameVar IS NOT "">
AND SQL_NameCol = <CFQUERYPARAM value=#FORM.NameVar#
CFSQLType="CF_SQL_VARCHAR">
</CFIF>
<CFIF FORM.AgeVar NEQ 0>
AND SQL_AgeCol = <CFQUERYPARAM value=#FORM.AgeVar#
CFSQLType="CF_SQL_INTEGER">
</CFIF>

... etc., etc. ...

ORDER BY
ThisColumn, ThatColumn
</CFQUERY>