Thanks everyone for your responses,
In the end, it seems that combating the SQL Injection vulnerability when
sorting is a matter of style.
It's just important to note that cfqueryparam is your friend but not the
only answer to the injection problem.
Vince Collins
http://www.vincentcollins.com
On 9/17/07, Dave Watts <[EMAIL PROTECTED]> wrote:
>
>
> >
> >
> >
> >
> >
> >
> >select *
> > from
> > Order By #Argument.qOrderField#
> >
> >
> >
>
> How does that prevent SQL injection?
>
> Dave Watts, CTO, Fig Leaf Software
> http://www.figleaf.com/
Good
Most RDBMSs support something like this. But you'd want to cache the results
so you only have to do it once.
On 9/17/07, Greg Morphis <[EMAIL PROTECTED]> wrote:
>
> Not sure if your DB supports this but with Oracle I can select the
> columns from the table..
> select column_name
> from user_tab_co
>
>
> Then in the cfswitch statement I have something like this
>
>
>
> order by table.user_name
>
>
> order by table.lastlogin_date
>
> ..
> ..
I think this is how I would go about the new order by stuff . Instead of
switch or if statements. run the query as on
Not sure if your DB supports this but with Oracle I can select the
columns from the table..
select column_name
from user_tab_columns
where table_name = 'MY_TABLE'
Granted this would require 2 trips to the database, 1 to get the
column names, then check to see if your sortby variable is in that
list
> Actually, I was thinking I could just test for the existence
> of a semicolon in the passed url variable, and if one exists,
> ignore it and default the sort but I'm not a SQL expert. My
> guess is that you can still do some ugly things after order
> by that doesn't require a semicolon
>
>
> I think this is how I would go about the new order by stuff .
> Instead of switch or if statements. run the query as one of
> the methods in a component, then all you need to do is pass
> an argumentcollection item over to handle the Order by portion.
>
>
>
>
>
>
>select *
>
On 9/17/07, Vince Collins <[EMAIL PROTECTED]> wrote:
>
>
> That suggestion works. However, in an example of a query with 10
> columns and assuming you want to allow desc and asc, does anyone have a
> more conscience way other than 20 cfcase statements?
You could specify a list of table names an
I figured :)
Dave Watts wrote:
>> Actually, I was thinking I could just test for the existence
>> of a semicolon in the passed url variable, and if one exists,
>> ignore it and default the sort but I'm not a SQL expert. My
>> guess is that you can still do some ugly things after order
>> by t
Actually, I was thinking I could just test for the existence of a
semicolon in the passed url variable, and if one exists, ignore it and
default the sort but I'm not a SQL expert. My guess is that you can
still do some ugly things after order by that doesn't require a semicolon
Is this true?
I agree with you on this.
Greg Morphis wrote:
> I dont like using the order by 2 stuff simply because if the query has
> 42 columns, and it's 27 I'm looking for, it's a bitch to find.. it's
> easier to read "MONTHDATE" or whatever.
> Plus if the query changes, whoops.. gotta recount the items (un
I dont like using the order by 2 stuff simply because if the query has
42 columns, and it's 27 I'm looking for, it's a bitch to find.. it's
easier to read "MONTHDATE" or whatever.
Plus if the query changes, whoops.. gotta recount the items (unless
its a small change of course)
On 9/17/07, Vince Co
Yes, I did this in each case statement.
DESC
Also the expression for the cfswitch need to be modified to this
then I add that to the url sort variable like this.
sortby=categoryname,DESC
It's that simple
--
Wil Genovese
One man with courage makes a majority.
-Andrew Jackson
A fine is a
Jochem, good suggestion, then I can test for a numeric value.
MS SQL Server does support this but then the application ends up being
database specific and more cryptic to read. I could however live with that.
Maybe I'm making this a bigger deal than it is. Just seems there should
be a more e
You could also pass the order by via URL, and that would cut the
cfcases by half.
On 9/17/07, Jochem van Dieten <[EMAIL PROTECTED]> wrote:
> Vince Collins wrote:
> >
> > That suggestion works. However, in an example of a query with 10
> > columns and assuming you want to allow desc and asc, do
Vince Collins wrote:
>
> That suggestion works. However, in an example of a query with 10
> columns and assuming you want to allow desc and asc, does anyone have a
> more conscience way other than 20 cfcase statements?
Check if your database support ordering by the ordinal number of the
colu
Thanks Wil,
That suggestion works. However, in an example of a query with 10
columns and assuming you want to allow desc and asc, does anyone have a
more conscience way other than 20 cfcase statements?
I thought about just checking for the existence of the semicolon in the
sort variable bein
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
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 ar
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?colu
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:
nameage
cheers,
barneyb
On 9/17/07, Vince Collins
Am I not able to use cfqueryparam in the order by column.
Select * from tablename order by
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
expres
22 matches
Mail list logo
