CFQUERYPARAM and order by

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

LVL 1
ShawnAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
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
 
_agx_Commented:
> (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
 
ShawnAuthor Commented:
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
 
_agx_Commented:
> 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
 
ShawnAuthor Commented:
I'm already starting to...so many possibilities.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.