Last post Oct 29, 2014 04:48 AM by smirnov
Oct 28, 2014 08:48 AM|leecotton|LINK
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?
Oct 28, 2014 09:02 AM|smirnov|LINK
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
Oct 28, 2014 09:09 AM|PatriceSc|LINK
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.
Oct 28, 2014 09:19 AM|oned_gk|LINK
Oct 28, 2014 08:16 PM|Lannie|LINK
You can pass bind variables derived from your Oracle Parameter collection, test a condition, to dynamically pick different ORDER BY columns
WHEN :bindvar = 1 THEN COLA END,
WHEN :bindvar = 2 THEN COLB END
Oct 29, 2014 04:04 AM|leecotton|LINK
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!
Oct 29, 2014 04:48 AM|smirnov|LINK
Parameters in Oracle select command do nothing but supply values for your query.
If your query has something like @SortColumn and @SortDirection, i.e.
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.