Solved

cfqueryparam's question

Posted on 2011-02-18
11
989 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
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!

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
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 …
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…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

757 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

19 Experts available now in Live!

Get 1:1 Help Now