This is all to common even among experienced programmers. I recently
caught a major case of this where the entire order by was passed via URL
including exposing the table name as well as the column name in the
URL. (domain.com?sortby=table.columnname desc)
I like to use a cfswitch if there are more than two sort columns.
I pass something like sort=name or sort=date etc in the URL. My table
columns are never this simply named so I don't have any conflicts.
Then in the cfswitch statement I have something like this
<cfswitch expression"#url.sort#">
<cfcase value="name">
order by table.user_name
<cfcase>
<cfcase value="date">
order by table.lastlogin_date
<cfcase>
..
..
..
</cfswitch>
I NEVER user form or URL variables directly in the sql statement. I
have a large number of validation functions that I use that will allow
me to safely store any data into my db and to protect my applications
from sql injection. cfqueryparam may do some of the work, but I do not
rely on it completely. I also use my custom functions for data
validation to help ensure I get the desired data input.
--
Wil Genovese
One man with courage makes a majority.
-Andrew Jackson
A fine is a tax for doing wrong. A tax is a fine for doing well.
Barney Boisvert wrote:
> Yeah, I didn't mean to imply that you shouldn't be concerned about
> injection. Rather, you only have to worry about injection if
> 'columnname' is a tainted field. If it's untainted, then injection
> isn't a concern, so you can safely do the direct substitution.
>
> For example, if 'columnname' is coming from the URL, you definitely
> have to isolate it. But if it's coming from application code, you
> probably don't have to worry.
>
> cheers,
> barneyb
>
> On 9/17/07, Vince Collins <[EMAIL PROTECTED]> wrote:
>
>> Thanks Barney,
>>
>> I have to say though that I am willing to bet there are programmers that
>> think their SQL is safe from injection just by using the cfqueryparam
>> and forgetting about the order by clause if they allow that to be passed...
>>
>> select * from tablename order by #columnname#
>>
>> index.cfm?columnname=blah;delete from tablename
>>
>> OUCH
>>
>>
>>
>>
>>
>>
>>
>>
>> Barney Boisvert wrote:
>>
>>> You can't use params to control the structure of the statement, only
>>> the values passed into the statement.
>>>
>>> If you're concerned about injection, you can attempt to clean the
>>> value before inlining it, or use a CF conditional to emit static
>>> stuff:
>>>
>>> <cfif sortcolumn EQ "name">name<cfelse>age</cfif>
>>>
>>> cheers,
>>> barneyb
>>>
>>> On 9/17/07, Vince Collins <[EMAIL PROTECTED]> wrote:
>>>
>>>
>>>> Am I not able to use cfqueryparam in the order by column.
>>>>
>>>> Select * from tablename order by <cfqueryparam
>>>> cfsqltype="CF_SQL_VARCHAR" value="#SORTCOLUMN#">
>>>>
>>>> The error I'm getting on a windows 2003 server running CF7 is:
>>>> /[Macromedia][SQLServer JDBC Driver][SQLServer]The SELECT item
>>>> identified by the ORDER BY number 1 contains a variable as part of the
>>>> expression identifying a column position. Variables are only allowed
>>>> when ordering by an expression referencing a column name./
>>>>
>>>> If I just have to do the following...
>>>>
>>>> Select * from tablename order by #sortcolumn#
>>>>
>>>> ....then my SQL call is not secure from SQL injection. Does this mean I
>>>> need to write my own tests for the "sortcolumn" variable being passed or
>>>> create a case statement which then inserts the correct column name?
>>>>
>>>> OR...the scenario that is MUCH more likely, am I missing something?
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Enterprise web applications, build robust, secure
scalable apps today - Try it now ColdFusion Today
ColdFusion 8 beta - Build next generation apps
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:288606
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
