locked
Can I use an Oracle select command with the ORDER column/direction as a parameter? RRS feed

  • Question

  • User816415303 posted

    Hello all.  I hope someone can help me with a problem.  I'm using an Oraclecommand to select a bunch of data from a table, but I'd like to be able to pass the ORDER BY column and also the order direction as parameters, so the results will be sorted for me when they're returned.  I can see how to pass other variables such as WHERE values as parameters, but this doesn't appear to work with ORDER BY xxx.  Does anyone know if this can be acheived?  

    The only way I can see of doing it at the moment is constructing the SQL Select string, but that's a bad practice as it could be exploited by injections attacks.  Any thoughts anyone?

    Tuesday, October 28, 2014 8:48 AM

All replies

  • User1508394307 posted

    What is your current code? Not clear where you can add WHERE but not ORDER BY.

    If you do not want to use dynamic sql you could consider to use stored procedures where you could specify required sorting order. Examples: http://msdn.microsoft.com/en-us/library/ms971506.aspx 

    Tuesday, October 28, 2014 9:02 AM
  • User753101303 posted

    Hi,

    As it is an ORDER BY clause it should be quite easy to check that the order is a valid column name (or a list of column names) and that a valid ASC or DESC  indicator is used. The key point is to be as restrictive as possible about what you are using to construct a dynamic SQL string (here just the ORDEY BY clause). I assume that the ORDER BY clause would be constructed on the C# side?

    It can't be handled using parameters because they are not used by just replacing something anywhere in the SQL statement but are specifically to pass literal values (and not columns names). Or you could use a CASE statement but it is likely more complex.

    Tuesday, October 28, 2014 9:09 AM
  • User-1716253493 posted
    Maybe something like this : execute 'select.... Order by ' + @colname. See http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12022.htm
    Tuesday, October 28, 2014 9:19 AM
  • User269602965 posted

    You can pass bind variables derived from your Oracle Parameter collection, test a condition, to dynamically pick different ORDER BY columns

    ORDER BY
      CASE
        WHEN :bindvar = 1 THEN COLA END,
        WHEN :bindvar = 2 THEN COLB END
    /
    

    Tuesday, October 28, 2014 8:16 PM
  • User816415303 posted

    Thanks for the suggestions everyone.  I'll see if I can implement some of them.  I'm kinda surprised there's no standard method for doing this, as I'd imagine it's something that people would need quite often!

    Lee

    Wednesday, October 29, 2014 4:04 AM
  • User1508394307 posted

    Parameters in Oracle select command do nothing but supply values for your query

    If your query has something like @SortColumn and @SortDirection, i.e.

    order by
    case when @SortDirection = 'ASC' and @SortColumn = 'col1' then col1 end,
    case when @SortDirection = 'DSC' and @SortColumn = 'col1' then col1 desc,
    case when @SortDirection = 'ASC' and @SortColumn= 'col2' then col2 end,
    case when @SortDirection = 'DSC' and @SortColumn = 'col2' then col2 end desc

    then you can supply both parameters to Oraclecommand, no problem here.

    Wednesday, October 29, 2014 4:48 AM