Solved

cfqueryparam's question

Posted on 2011-02-18
11
990 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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
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 …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

864 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

21 Experts available now in Live!

Get 1:1 Help Now