Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

CFQUERYPARAM and order by

Posted on 2009-05-18
5
Medium Priority
?
198 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
  • 3
  • 2
5 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 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

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.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month12 days, 3 hours left to enroll

916 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