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
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 p them
back out -- this just needlessly complicates things.
<!--- IMPORTANT: In this example we use the form variables directly (for
In actual practice, you would not do this but instead: (A) <cfparam> the
(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=...>
AAA, BBB, ... <!--- Fixed list of columns --->
1=1 <!--- Legal, harmless and very useful. --->
<CFIF FORM.NameVar IS NOT "">
AND SQL_NameCol = <CFQUERYPARAM value=#FORM.NameVar#
<CFIF FORM.AgeVar NEQ 0>
AND SQL_AgeCol = <CFQUERYPARAM value=#FORM.AgeVar#
... etc., etc. ...