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

  • How to execute .sql file using powershell?
  • SQL Server: advanced update query
  • MDX How to retrieve data based on the latest date
  • What is ISO_year in sql-server
  • “Simple” SQL Query
  • How should i work in this scenario . Should I use Trigger or Leave on User to manage
  • 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);
    

  • file extensions lost between browsers in asp.net c# application
  • How can I delete the selected rows (CheckBox used) from the GridView on click of a Button?
  • Must declare the scalar variable “@query” dynamic tsql to stored procedure
  • Model connected to other models cannot load foreign keys to code
  • Error creating an SQL Server Database in Visual Studio
  • SQL query won't show all records if bot critera is null or ''
  • 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.