Solved

cfqueryparam's question

Posted on 2011-02-18
11
992 Views
Last Modified: 2012-05-11
How would I got about adding cfqueryparam's to my queries to prevent sql injection?
<!---Search by City--->
<cfif form.City IS NOT "">
 AND City LIKE '#form.City#'
</cfif>
<!---Search by Subdivision--->
<cfif form.Subdivision IS NOT "">
 AND Subdivision LIKE '#form.Subdivision#'
</cfif>
<!---Search by Age--->
<cfif form.ApproxAge IS NOT "">
 AND ApproxAge BETWEEN #form.ApproxAge#
</cfif>
<!---Search by Acreage--->
<cfif form.ApproxAcreage IS NOT "">
 AND ApproxAcreage BETWEEN #form.ApproxAcreage#
</cfif>
<cfif IsNumeric(form.minimumprice) and IsNumeric(form.maximumprice)>
       AND Price BETWEEN #val(form.minimumprice)# AND #val(form.maximumprice)#
</cfif>

<cfif IsNumeric(form.Bedrooms)>
     AND Bedrooms >= <cfqueryparam value="#form.Bedrooms#" cfsqltype="cf_sql_integer">
</cfif>
<cfif IsNumeric(form.FullBaths)>
     AND FullBaths >= <cfqueryparam value="#form.FullBaths#" cfsqltype="cf_sql_integer">
</cfif>


ORDER BY Price ASC
</cfquery>

Open in new window

0
Comment
Question by:Bang-O-Matic
  • 5
  • 4
  • 2
11 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 34931559
Just add it in like this..

<cfif len(form.City)>
  AND City LIKE  <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.City#">
</cfif>

The only thing is that you have to match your datatype.  

CF_SQL_VARCHAR for strings,   CF_SQL_INTEGER or CF_SQL_NUMERIC for numbers

0
 
LVL 52

Expert Comment

by:_agx_
ID: 34931570
We're continuing another question ... and it's a little more involved :)

I would start by replacing all the simple cases first as gdemaria showed.  But you mentioned your "Price" column is a decimal. What's the exact size and precision.  ie   DECIMAL(10, 2) ... etc...

0
 
LVL 52

Expert Comment

by:_agx_
ID: 34931582
>>   AND City LIKE  <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.City#">

Actually that should be equals since there's no wildcard, and trim the values to ignore white space only.  So for "City" and "Subdivision" you should have something like

<!---Search by City--->
<cfif len(trim(form.City))>
     AND City = <cfqueryparam value="#form.City#" cfsqltype="cf_sql_varchar">
</cfif>
<!---Search by Subdivision--->
<cfif len(trim(form.Subdivision))>
     AND Subdivision = <cfqueryparam value="#form.Subdivision#" cfsqltype="cf_sql_varchar">
</cfif>

Confirm those are working first. Then move on to "Price" and the other fields.
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 39

Expert Comment

by:gdemaria
ID: 34931602
I assumed that the wildcard would be passed in by the user or preceding code

If not, then you don't need LIKE, but it doesn't hurt.  I researched it once, there is no performance difference to use LIKE instead of = if there is no wildcard
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34931606
oh, i missed your first post that its more involved...  ok :)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34931619
I don't recall if I've done any comparisons. But I wouldn't assume every db is smart enough to silently convert it to an equals.  MS SQL probably is, but I wouldn't count on every db doing the same ;-) Besides it's just not intuitive. Whenever I see I keep looking for the missing wildcard! lol.
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 34931663
Since you went to sleep for the night ;-) I'll skip "Price" and move on to the other fields and check back tomorrow.  Like I was saying earlier, it's not safe to store SQL in the #form.ApproxAge# and #form.ApproxAcreage# fields.  Instead, I'd change the values of those fields to store the min/max values delimited by a ":"

ie     <cfselect name="ApproxAge" ...>
            <option value="1:5">....</option>
            ....
        </cfselect>

Then you can use list functions to extract the min/max values and use them in cfqueryparam.  

ie      form.approxAge = "1:5"
         #listFirst(form.approxAge, ":")#  ===>  would equal 1
         #listLast(form.approxAge, ":")#  ===>  would equal 5

So your new form code for those 2 fields would be something like

<!--- default to empty string --->
<cfparam name="form.ApproxAge" default="">
<cfparam name="form.ApproxAcreage" default="">

<cfselect name="ApproxAge">
    <option value="" <cfif form.ApproxAge eq "">selected</cfif>>Any</option>
    <option value="0:0" <cfif form.ApproxAge eq "0:0">selected</cfif>>New Construction</option>
    <option value="1:5" <cfif form.ApproxAge eq "1:5">selected</cfif>>1 to 5 yrs</option>
    <option value="6:10" <cfif form.ApproxAge eq "6:10">selected</cfif>>6 to 10 yrs</option>
    <option value="11:20" <cfif form.ApproxAge eq "11:20">selected</cfif>>11 to 20 yrs</option>
    <option value="21:49" <cfif form.ApproxAge eq "21:49">selected</cfif>>21 to 49 yrs</option>
    <option value="50:998" <cfif form.ApproxAge eq "50:998">selected</cfif>>50 + yrs</option>
</cfselect>

<cfselect name="ApproxAcreage">
    <option value="" <cfif form.ApproxAcreage eq "">selected</cfif>>Any</option>
    <option value="0:1" <cfif form.ApproxAcreage eq "0:1">selected</cfif>>under 1 acre</option>
    <option value="1:5" <cfif form.ApproxAcreage eq "1:5">selected</cfif>>1 to 5 acres</option>
    <option value="6:10" <cfif form.ApproxAcreage eq "6:10">selected</cfif>>6 to 10 acres</option>
    <option value="11:20" <cfif form.ApproxAcreage eq "11:20">selected</cfif>>11 to 20 acres</option>
    <option value="20:10000" <cfif form.ApproxAcreage eq "20:10000">selected</cfif>>20 + acres</option>
</cfselect>

Open in new window


... and the new query filter would be

<cfset delim = ":">
.... 
<cfif listLen(form.approxAge, delim) gte 2>
AND  ApproxAge BETWEEN 
     <cfqueryparam value="#val(listFirst(form.approxAge, delim))#" cfsqltype="cf_sql_integer"> AND
     <cfqueryparam value="#val(listLast(form.approxAge, delim))#" cfsqltype="cf_sql_integer"> 
</cfif>

<cfif listLen(form.ApproxAcreage, delim) gte 2>
AND  ApproxAcreage BETWEEN 
     <cfqueryparam value="#val(listFirst(form.ApproxAcreage, delim))#" cfsqltype="cf_sql_integer"> AND
     <cfqueryparam value="#val(listLast(form.ApproxAcreage, delim))#" cfsqltype="cf_sql_integer"> 
</cfif>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34931676
0
 

Author Comment

by:Bang-O-Matic
ID: 34933792
agx I think it's all updated and working, I changed the Price field from decimal to integer, and updated the form filelds as you suggested...here's my query filter:

<!---Query the database--->
<cfquery name = "idx" datasource="CGMLS">
SELECT * FROM listings,seo WHERE Listings.ListID = Seo.ListID


<!---Search by City--->
<cfif len(trim(form.City))>
     AND City = <cfqueryparam value="#form.City#" cfsqltype="cf_sql_varchar">
</cfif>

<!---Search by Subdivision--->
<cfif len(trim(form.Subdivision))>
     AND Subdivision = <cfqueryparam value="#form.Subdivision#" cfsqltype="cf_sql_varchar">
</cfif>

<!---Search by Age--->
<cfif listLen(form.approxAge, delim) gte 2>
AND  ApproxAge BETWEEN
     <cfqueryparam value="#val(listFirst(form.approxAge, delim))#" cfsqltype="cf_sql_integer"> AND
     <cfqueryparam value="#val(listLast(form.approxAge, delim))#" cfsqltype="cf_sql_integer">
</cfif>

<!---Search by Acreage--->
<cfif listLen(form.ApproxAcreage, delim) gte 2>
AND  ApproxAcreage BETWEEN
     <cfqueryparam value="#val(listFirst(form.ApproxAcreage, delim))#" cfsqltype="cf_sql_integer"> AND
     <cfqueryparam value="#val(listLast(form.ApproxAcreage, delim))#" cfsqltype="cf_sql_integer">
</cfif>

<!---Search by Price--->
<cfif IsNumeric(form.minimumprice) and IsNumeric(form.maximumprice)>
       AND Price BETWEEN <cfqueryparam value="#form.minimumprice#" cfsqltype="cf_sql_integer"> AND <cfqueryparam value="#form.maximumprice#" cfsqltype="cf_sql_integer">
</cfif>

<!---Search by Bedrooms--->
<cfif IsNumeric(form.Bedrooms)>
     AND Bedrooms >= <cfqueryparam value="#form.Bedrooms#" cfsqltype="cf_sql_integer">
</cfif>

<!---Search by FullBaths--->
<cfif IsNumeric(form.FullBaths)>
     AND FullBaths >= <cfqueryparam value="#form.FullBaths#" cfsqltype="cf_sql_integer">
</cfif>


ORDER BY Price ASC
</cfquery>
0
 

Author Closing Comment

by:Bang-O-Matic
ID: 34933916
Thanks again!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34934173
Perfect!
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
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 …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

730 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