• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1363
  • Last Modified:

Build dynamic coldfusion query with cfqueryparam

Coldfusion 8 SQL Server 2005

I have forms that submit multiple combinations of parameters. In the past I have
called a custom tag to keep appending critieria to build the criteria. This works fine but no cfqueryparam protection. Also The form is now going to pass parameters in url rather than form fields.

<CF_AppendCriteria
          FieldName="Members.DisplayAd"
          FieldType="Char"
          Operator="equal"
          Value="yes">
 
The custom tag used to work fine (without cfquerparm as below)
....
<CFIF Attributes.operator is 'EQUAL'>       <CFSET Criteria = Criteria & " = '#Value#' ">

Now I have tried many different things to include cfqueryparam and cant get it to work

eg

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


When I do a test outpui it looks like this:

 (Members.MembershipStatus = '' AND Members.ActiveInactive = '' AND Members.ArchiveFlag = ''
etc etc

It used to be fine like this
 (Members.MembershipStatus = 'paid'  AND Members.ActiveInactive = 'active' AND Members.ArchiveFlag = 'No'  ..... etc


I dont want to do massive if Isdefined statements as there are so many combinations of params that can be passed in. Thanks for your help
0
Ian White
Asked:
Ian White
  • 6
  • 5
  • 5
2 Solutions
 
gdemariaCommented:
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>
0
 
_agx_Commented:
<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.
0
 
gdemariaCommented:
> 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.
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
Ian WhiteOwner and FounderAuthor Commented:
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
0
 
gdemariaCommented:
The problem is assembling your where clause into a string and then expecting Coldfusion code within that string to dynamically execute.  

If you dropped the cfqueryparam it would work or if you moved it out of the custom tag and put it into your query it would work like this:  

This is the typical method used, right in the query.  I dont' really see the benefit of putting it into a custom tag

<cfquery name="getIt".....>
select *
  from myTable
where 1=1
<cfif listLen(form.thisField) gt 1>
  and thisField in (<cfqueryparam cfsqltype='CF_SQL_CHAR' value="#form.thisField#" list="Yes">)
<cfelse>
  and thisField = <cfqueryparam cfsqltype='CF_SQL_CHAR' value="#form.thisField#">
</cfif>
0
 
_agx_Commented:
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>

Open in new window

0
 
Ian WhiteOwner and FounderAuthor Commented:
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>
0
 
_agx_Commented:
I dont understand this. How do I protect this in my code below?

Your code above is fine. We were talking about an earlier example from comment 37788627. Your code is safe because the column names are hard coded (and values protected with cfqueryparam).


    and members.suburb LIKE <cfqueryparam ...>  

The earlier example is unsafe because it uses FORM data (which could contain malicious sql) to construct the sql column names.  To make it safe you'd have to validate #aField# against a list of valid db column names first.

<cfloop index="aField" list="#form.formFields#>
      ...
     and #aField# = <cfqueryparam ...>
    ...
</cfif>



1)  and Members.MemberType       = <cfqueryparam cfsqltype='CF_SQL_CHAR' value="Sitter">
....  
2) <cfif IsDefined("form.su")>


1) You don't need to use cfqueryparam for static values, only dynamic ones (form, url etc.. variables).

2) If the FORM values are text fields, they'll always be defined. Instead you should check the length and only add a WHERE condition if the text field isn't empty. So the final code would be more like this:

SELECT  UserName, ....
FROM   Members  WITH (NOLOCK)
<!--- list static conditions 1st ---->
WHERE  MemberType   = 'Sitter'
AND      MembershipStatus  = 'confirmed'
AND      ActiveInactive   = 'active'
AND      ArchiveFlag       = 'n'
AND      DisplayAd         = 'yes'

<!--- if these are all text fields ... --->
<cfif len(trim(form.co))>
        AND (
                   Country  = <cfqueryparam ....> OR
                   AlternateCountries  LIKE <cfqueryparam .....>
         )
</cfif>
           
<cfif len(trim(form.sta))>
        AND state  = <cfqueryparam ....>
</cfif>
etc ....
0
 
Ian WhiteOwner and FounderAuthor Commented:
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))>
0
 
Ian WhiteOwner and FounderAuthor Commented:
Thanks for the tips
0
 
gdemariaCommented:
AveAgo, perhaps you're new to EE, but it is customary to split points between experts who both assist you and answer your question.
0
 
Ian WhiteOwner and FounderAuthor Commented:
Ok - thanks for the advise. I am not sure how I can do this now that it is closed
0
 
_agx_Commented:
Just click the Request Attention link in your 1st post and ask a moderator to reopen the question. Then you can reallocate the points.
0
 
Ian WhiteOwner and FounderAuthor Commented:
ok I have sent through a request
0
 
_agx_Commented:
Cool, thanks AveAGoP.
0
 
gdemariaCommented:
thanks to both !
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 6
  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now