Solved

CFQUERYPARAM and order by

Posted on 2009-05-18
5
183 Views
Last Modified: 2013-12-24
Concerning protection against injection I'm a little confused when I should protect the ORDER by clause. Does the query below need to be changed to protect the order by part? If so what to?
...
SELECT     container, language, active, lastedit, title, contentID
FROM       contentItems
WHERE     (active= <CFQUERYPARAM Value=1>)
ORDER BY container, language
</cfquery>

Open in new window

0
Comment
Question by:Shawn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 24417308
No.  

(Though it is not the only reason to use it) cfqueryparam is used when working with user supplied values (FORM or URL variables).  Since there are no user supplied values in your ORDER BY clause, you do not need to modify it.  

If you were using a user supplied value in the ORDER BY, like for dynamic sorting, you _would_ need to protect it.  BUT ... cfqueryparam is used for "values", not table or column names.  So you would have to find some other way to protect the statement below:

--- this statement is NOT safe
SELECT     container, language, active, lastedit, title, contentID
FROM       contentItems
WHERE     (active= <CFQUERYPARAM Value=1>)
ORDER BY #url.sortByColumnName1#, #url.sortByColumnName2#

0
 
LVL 52

Expert Comment

by:_agx_
ID: 24417320
> (active= <CFQUERYPARAM Value=1>)

    I am assuming that is just psuedo-code.  Otherwise, the value should be quoted and the
    correct  "cfsqltype" included  (integer, bit, etc..)

    WHERE  active =  <CFQUERYPARAM Value="1" cfsqltype="cf_sql_integer">
0
 
LVL 1

Author Comment

by:Shawn
ID: 24417366
thanks for the clarification. It's taken a little while to wrap it all around my head. Moving up from Access to SQL server is great but my head is still spinninig a little :-)

thanks for the pointer. I updated all vulnerable queries with a tool that didn't specify type. The type is boolean so I suppose this should be it.
WHERE     (active= <CFQUERYPARAM cfsqltype="cf_sql_bit" Value="1">)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 24417622
> Moving up from Access to SQL server is great but my head is still spinninig a little :-)

Yes, but you will love it as soon as you get your bearings ;-)
0
 
LVL 1

Author Comment

by:Shawn
ID: 24417632
I'm already starting to...so many possibilities.
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

751 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