Использование SqlParameter для создания предложения Order By

Я пытаюсь переместить все мои ссылки на переменные в операторах SQL в класс SqlParameter, но по какой-то причине этот запрос не работает.

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);

Вот ошибка

System.Data.SqlClient.SqlException: элемент SELECT, идентифицированный ORDER BY номером 1, содержит переменную как часть выражения, идентифицирующего позицию столбца. Переменные допускаются только при заказе с помощью выражения, ссылающегося на имя столбца.

В этой строке это не работает.

dataCommand.Fill(ds);

Ответы

Ответ 1

У вас действительно есть три варианта.

1) Используйте dataview для упорядочения набора результатов

2) Если вы знаете столбцы, которые можно заказать, вы можете проверить строку, а затем использовать, а затем выбрать порядок. например

Например, это будет работать

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) Окончательный вариант - сделать то же самое, что и # 2, но в коде С#. Просто убедитесь, что вы не просто ссылаетесь на предложение ORDER BY с пользовательского ввода, потому что это будет отвлекаться на SQL-инъекцию.

Это безопасно, потому что параметр OrderBy Url "Name Desc; DROP table Users" будет просто проигнорирован

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 ;

Ответ 2

Использование SqlCommand - это способ предотвратить внедрение sql. Ваш способ изменения порядка совпадает с использованием SQL-инъекции в этом контексте, поэтому он не должен быть разрешен - параметры используются как константы, не могут использоваться как имена столбцов или таблиц.

u не нужно конкатенировать содержимое sortBy, просто используйте его как enum и в зависимости от его значения соедините что-то, что вы уверены, что это безопасно. Вот так:

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

Ответ 3

Я нашел пример, как это сделать здесь

вы можете определить разные порядки сортировки в CASE-структуре и соответствующим образом выполнить их значение переменной:

  SELECT CompanyName,
         ContactName,
         ContactTitle

    FROM Customers

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

Я не тестировал его сам, но он мог работать. Вы можете попробовать. Очевидным недостатком является то, что вам нужно закодировать все инструкции порядка.

Ответ 4

Я столкнулся с той же проблемой, что и вы, однако перечисленные решения не могли использоваться, так как мой возможный столбец сортировки был одним из свойств модели, и это означало бы слишком много if-операторов, если модель большая. Мое решение этой проблемы связано с использованием Reflection. Что-то вроде:

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

Преимущество этого решения заключается в том, что независимо от того, как изменится моя модель, этот код будет поддерживать сортировку по любому из его свойств и, следовательно, его также не нужно менять.

Ответ 5

Вы просто конкатенируете строки. Более простой подход:

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

Я предполагаю, что вы делаете это вообще, потому что вы разрешаете вызывающему абоненту определять поле/направление сортировки, следовательно, порядокBy разделен.

Параметры, поскольку тип сообщения об ошибке наклонной подсказки будет использоваться в предложении WHERE, например. WHERE someColumn = @someValue и т.д.