Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Build dynamic coldfusion query with cfqueryparam

Posted on 2012-03-29
17
Medium Priority
?
1,314 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
A new era in Cloud training has arrived.

A day that will go down in Cloud history.. But are you ready for it? Will you accept this Cloud challenge?

 

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 1000 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 1000 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

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

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

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

715 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