Using SqlParameter to create Order By clause

Question

I am trying to move all of my references to variables in SQL statements to the SqlParameter class however for some reason this query fails.

string orderBy = Request.QueryString["OrderBy"];
//Fix up the get vars
if (orderBy == null)
    orderBy = "name ASC";

string selectCommand = "SELECT cat_id AS id, cat_name AS name FROM table_name ORDER BY @OrderBy";
SqlCommand cmd = new SqlCommand(selectCommand, dataConnection);
cmd.Parameters.Add(new SqlParameter("@OrderBy", orderBy));

//Create the SQLDataAdapter instance
SqlDataAdapter dataCommand = new SqlDataAdapter(cmd);

//Create the DataSet instance
DataSet ds = new DataSet();
//Get data from a server and fill the DataSet  
dataCommand.Fill(ds);

Here is the error

System.Data.SqlClient.SqlException: 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.

It fails on this line.

dataCommand.Fill(ds);

Answer 1

You're just concatenating strings. A simpler approach would be:

string orderBy = "name ASC";
string selectCommand = "SELECT cat_id AS id, cat_name AS name FROM table_name ORDER BY " + orderBy;

I'm assuming you're doing this at all because you're letting the caller decide sort field/direction, hence orderBy separated.

Parameters, as the error message sort of obliquely hints at, would be used in a WHERE clause, e.g. WHERE someColumn = @someValue etc.

Answer 2

I ran in to the same problem as you, however the listed solutions could not be used since my possible sort column was one of the properties of a model and that would mean way too many if-statements if the model is big. My solution to this related problem is to use Reflection. Something like:

class MyModel {
    public string MyField1 { get; set; }
    public string MyField2 { get; set; }
    // ...
}

//...
using System.Reflection;
// sortBy = "MyField1"
// sortDirection = "Asc";
var sql = "SELECT FROM foo WHERE bar=baz ORDER BY ";
foreach (var prop in typeof(MyModel).GetProperties())
{
    if (sortBy.Equals(prop.Name))
    {
        sql += (prop.Name + (sortDirection.Value.Equals("Asc") ? " ASC" : " DESC"));
        break;
    }
}

The benefit to this solution is that no matter how my model changes, this code will support sorting by any of its properties and thus doesn't need to be changed as well.

Answer 3

Using SqlCommand is the way to prevent from sql injection. Your way of changing of the order by is the same as using sql injection in this context so it shouldnt be allowed - params are used as the constants, can't be used as column or table names.

u dont have to concatenate content of sortBy just use it as enum and depending on its value concatenate something you're sure that is safe. Like this:

If(orderBy == "some_column")
{
   selectColumn += "someColumn";
}
...

Answer 4

I found an example how to do this here

you can define different sort orders in a CASE-structure and execute them appropriately to your variable value:

  SELECT CompanyName,
         ContactName,
         ContactTitle

    FROM Customers

ORDER BY CASE WHEN @SortOrder = 1 THEN CompanyName
              WHEN @SortOrder = 2 THEN ContactName
         ELSE ContactTitle

I didn't test it myself but it could work. You can give it a try. An obvious disadvantage is that you have to code all the order-by statements.

Answer 5

You really have three options.

1) Use a dataview to order the result set

2) If you know the columns that can be ordered you can test for the string and then use then select the order. e.g.

For example this will work

DECLARE @orderby varchar(255)
SET @orderby = 'Name ASC'

SELECT [Your Column here ]FROM sys.tables 
ORDER BY    
   case WHEN @orderby = 'Name ASC' Then name ELSE null END ASC,
   case WHEN @orderby = 'Name DESC' Then name ELSE null END DESC,
   CASE WHEN @orderby = 'Object_id ASC' then object_id ELSE null END ASC,
   CASE WHEN @orderby = 'Object_id DESC' then object_id ELSE null END DESC

3) The final option is to do the same as #2 but in your C# code. Just be sure you don't just tack on the ORDER BY clause from user input because that will be vunerable to SQL injection.

This is safe because the OrderBy Url parameter "Name Desc; DROP table Users"will simply be ignored

string SafeOrderBy = "";
string orderBy = Request.QueryString["OrderBy"];
//Fix up the get vars
if (orderBy == null)
    orderBy = "name ASC";

if (orderby == "name Desc")
{
     SafeOrderBy == "name Desc"
}


string selectCommand = "SELECT cat_id AS id, cat_name AS name FROM table_name ORDER BY "
selectCommand  += SafeOrderBy ;