Build dynamic coldfusion query with cfqueryparam

what is the custom tag doing?  Is it assembling a full where clause from multiple calls to the tag?  Not sure this is the best use or way to do this.

Maybe if you did..

select * from myTable
where 1=1
<CF_BuildCriteria  FieldName="Members.DisplayAd"  FieldType="Char" Operator="equal" Value="yes">
<CF_BuildCriteria  FieldName="Members.AnotherFIeld"  FieldType="Char" Operator="equal" Value="yes">
<CF_BuildCriteria  FieldName="Members.3rdField"  FieldType="Char" Operator="equal" Value="yes">


Then each one simpley output the generated part of the clause rather than concatinating it.

But I don't see the advantage to typing all that rather than just typing the clause itself.

If you wanted to make it more dynamic, you could loop through the fields something like this...

select * from myTable
where 1=1
<cfloop index="aField" list="#form.formFields#>
   <cfif len(trim(form[aField]))>
     and #aField#  = <cfqueryparam cfsqltype='CF_SQL_CHAR' value=" #form[aField]#">
   </cfif>
</cfif>

<CFSET Criteria = Criteria & " = "  & "<cfqueryparam cfsqltype='CF_SQL_CHAR' value=" & '#value#' &">" >

Yeah, cfqueryparam and dynamically built sql strings are incompatible.  The tags must be added within the query itself, not appended to a string.  (If you were using CF9 it might be possible w/cfscripting. )

and #aField#  = <cfqueryparam cfsqltype='CF_SQL_CHAR' value=" #form[aField]#">

Just a note, that would work but since column names can't be protected you're still left w/a sql injection risk.

> Just a note, that would work but since column names can't be protected you're still left w/a sql injection risk

True, you would have to validate the list of column names against the table or just generate the list from the table, not take it from the form.

Surely this is a common real world situation and someone must have a solution?

The form has mulitples of possiblities  with check boxes and - multiples being selected eg
pettypes=dogs,cats,fish,birds,ferrets   etc  some statements are =  others like

Surely this is a common real world situation and someone must have a solution?


Wanting to do it is common, but I'm 99% certain it's just not possible pre-CF9.

The problem is you're trying to combine 2 incompatible things: safe sql and dynamic sql. With safe sql, CF builds the sql statement for you by evaluating the cfqueryparam tags and converting them to bind parameters. But CF simply won't evaluate those tags if they're inside a dynamic string. (If you think about it, it would be very scary if it did.) So the sql can either be completely dynamic -OR- you can use cfqueryparam, but not both. To use cfqueryparam, the tags must be included in the cfquery explicitly - like in gdemaria's last example.  

If you were using CF9, you'd have some leeway with it's cfscript version of the tag. But afaik, you're out of luck with CF8.

Edit: As long as you generate the tags within the cfquery, you could probably store the parameter settings in some sort of array of structures.   Then loop through the array and WHERE clause conditions dynamically.  But doing it the way gd showed above seems much simpler.

<!--- param.column should NOT be user supplied --->
WHERE 
<cfloop array="#parameters#" index="param">
        #param.column# #param.operator# <cfqueryparam value="#param.value#" ...>
</cfloop>

Select allOpen in new window

Thanks for the input. so I have it working - keeping it simple see below

"Just a note, that would work but since column names can't be protected you're still left w/a sql injection risk"  

I dont understand this. How do I protect this in my code below?


<cfquery name="SearchResult"
                        datasource="foo"
                        dbtype="ODBC"
                cachedWithin="#CreateTimeSpan(0,0,30,0)#">
                        
                        
     Select
                  UserName,            
              MemberId,
            Country
          FROM Members  WITH (NOLOCK)
                
        where 1=1

            <cfif IsDefined("form.co")>
                 and (members.Country  = <cfqueryparam cfsqltype='CF_SQL_CHAR' value="#form.co#">
                 OR members.AlternateCountries  LIKE <cfqueryparam cfsqltype='CF_SQL_CHAR' value="%#form.co#%">)
            </cfif>
           
            <cfif IsDefined("form.sta")>
                 and members.state  = <cfqueryparam cfsqltype='CF_SQL_CHAR' value="#form.sta#">
            </cfif>
           
            <cfif IsDefined("form.ci")>
                 and members.city LIKE <cfqueryparam cfsqltype='CF_SQL_CHAR' maxlength='40' value='%#form.ci#%'>  
            </cfif>
           
            <cfif IsDefined("form.su")>
                 and members.suburb LIKE <cfqueryparam cfsqltype='CF_SQL_CHAR' maxlength='40' value='%#form.su#%'>  
            </cfif>

        and Members.MemberType       = <cfqueryparam cfsqltype='CF_SQL_CHAR' value="Sitter">
        and Members.MembershipStatus = <cfqueryparam cfsqltype='CF_SQL_CHAR' value="confirmed">
        and Members.ActiveInactive   = <cfqueryparam cfsqltype='CF_SQL_CHAR' value="active">
        and Members.ArchiveFlag      = <cfqueryparam cfsqltype='CF_SQL_CHAR' value="n">
        and Members.DisplayAd        = <cfqueryparam cfsqltype='CF_SQL_CHAR' value="yes">    
       
        ORDER BY NEWID()
     
           
  </cfquery>

Thanks for all your tips.  I need to use IfDefined  as the routine is used from different places and sometime that form field wont exit, but I will add <cfif len(trim(form.co))>

Thanks for the tips

AveAgo, perhaps you're new to EE, but it is customary to split points between experts who both assist you and answer your question.

Ok - thanks for the advise. I am not sure how I can do this now that it is closed

Just click the Request Attention link in your 1st post and ask a moderator to reopen the question. Then you can reallocate the points.

ok I have sent through a request

Cool, thanks AveAGoP.

thanks to both !