Solved

Build dynamic coldfusion query with cfqueryparam

Posted on 2012-03-29
17
1,295 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

 

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
 

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

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

632 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