Using SqlParameter to create Order By clause

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

  • Locking the record and unlocking
  • System Triggers for Replication and the NOCOUNT option
  • Stored Procedure is taking time in execution
  • How do I import an excel spreadsheet into SQL Server?
  • Creating an SQL Server Script to check for nulls on any table given in a stored procedure
  • Include carriage returns when calculating substring
  • 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);
    

  • Working with OpenXML
  • UTC Time related issue in Application, (C#,ASP.NET & SQL)
  • Diaplaying SSMS standard report in your web application
  • SQL : JOIN from two different servers using C#
  • Why do “I get Failed to generate a user instance of SQL Server” with .NET 4 and not .NET 2?
  • serialization and deserialization of session data
  • 5 Solutions collect form web for “Using SqlParameter to create Order By clause”

    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 ;
    

    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";
    }
    ...
    

    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.

    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.

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.