Solved

Build dynamic coldfusion query with cfqueryparam

Posted on 2012-03-29
17
1,201 Views
Last Modified: 2012-04-02
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
Comment
Question by:Ian White
  • 6
  • 5
  • 5
17 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 37786883
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
 
LVL 52

Expert Comment

by:_agx_
ID: 37787632
<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
 
LVL 39

Expert Comment

by:gdemaria
ID: 37787755
> 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
 

Author Comment

by:Ian White
ID: 37788596
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
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 250 total points
ID: 37788627
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
 
LVL 52

Expert Comment

by:_agx_
ID: 37788839
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
 

Author Comment

by:Ian White
ID: 37790143
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
 
LVL 52

Accepted Solution

by:
_agx_ earned 250 total points
ID: 37791591
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Ian White
ID: 37794103
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
 

Author Comment

by:Ian White
ID: 37794110
Thanks for the tips
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 37794220
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
 

Author Comment

by:Ian White
ID: 37794267
Ok - thanks for the advise. I am not sure how I can do this now that it is closed
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37794472
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
 

Author Comment

by:Ian White
ID: 37794521
ok I have sent through a request
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37796740
Cool, thanks AveAGoP.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 37796863
thanks to both !
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now