使用SqlParameter创建Order By子句

Question

我正在尝试将所有对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:由ORDER by数字1标识的选择项包含一个变量，该变量是标识列位置的表达式的一部分。仅当按引用列名的表达式排序时，才允许使用变量。

它在这一行上失败。

dataCommand.Fill(ds);

Answer 1

你真的有三个选择。

1)使用数据视图对结果集进行排序

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)最后一个选项是在C#代码中执行与#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 ;

Answer 2

使用SqlCommand是防止sql注入的一种方法。您更改order by的方式与在此上下文中使用sql注入的方式相同，因此不应允许这样做-参数用作常量，不能用作列或表名。

您不必连接sortBy的内容，只需将其用作枚举，并根据其值连接您确信安全的内容。如下所示：

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

Answer 3

我遇到了和你一样的问题，但是列出的解决方案不能使用，因为我可能的排序列是模型的属性之一，这意味着如果模型很大，就会有太多的if语句。我对这个相关问题的解决方案是使用反射。类似于：

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

此解决方案的好处是，无论我的模型如何更改，此代码都将支持按其任何属性进行排序，因此也不需要更改。