Solved

CFQUERYPARAM and order by

Posted on 2009-05-18
5
186 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

Three Considerations for Containers

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read our article on Experts Exchange.

Question has a verified solution.

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

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…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

632 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