• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 999
  • Last Modified:

cfqueryparam's question

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
Bang-O-Matic
Asked:
Bang-O-Matic
  • 5
  • 4
  • 2
1 Solution
 
gdemariaCommented:
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
 
_agx_Commented:
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
 
_agx_Commented:
>>   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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
gdemariaCommented:
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
 
gdemariaCommented:
oh, i missed your first post that its more involved...  ok :)
0
 
_agx_Commented:
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
 
_agx_Commented:
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
 
gdemariaCommented:
0
 
Bang-O-MaticAuthor Commented:
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
 
Bang-O-MaticAuthor Commented:
Thanks again!
0
 
_agx_Commented:
Perfect!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now