Solved

cfqueryparam's question

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Coldfusion - rename files in a folder 4 18
Application.cfc - what is the order of events? 4 29
REGEX HELP 11 40
Coldusion - DATA insert syntax problem 12 22
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 spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

813 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

17 Experts available now in Live!

Get 1:1 Help Now