使用的SqlParameter创建Order By子句 [英] Using SqlParameter to create Order By clause

问题描述

我试图然而，我所有的引用移动到变量SQL语句来的SqlParameter类由于某种原因查询失败。

 字符串的OrderBy =的Request.QueryString [排序依据];
//解决了GET瓦尔
如果（排序依据== NULL）
    ORDERBY =名ASC;字符串的SelectCommand =SELECT CAT_ID为ID，cat_name AS的名字从table_name的ORDER BY @OrderBy
CMD的SqlCommand =新的SqlCommand（SelectCommand中，dataConnection）;
cmd.Parameters.Add（新的SqlParameter（@排序依据排序依据））;//创建SqlDataAdapter的实例
SqlDataAdapter的dataCommand =新SqlDataAdapter的（CMD）;//创建DataSet实例
DataSet的DS =新的DataSet（）;
//从服务器获取数据并填充DataSet
dataCommand.Fill（DS）;
 

下面是误差

System.Data.SqlClient.SqlException：由ORDER BY鉴定1号Select项目包含一个变量作为前pression标识列位置的一部分。由前pression订货引用列名的变量时，只允许

它未能在这条线。

  dataCommand.Fill（DS）;
 

解决方案

您真的有三种选择。

1）使用数据视图命令的结果集

2）如果你知道可以命令你可以测试字符串，然后使用该列，然后选择顺序。例如

例如这将工作

 定义@OrderBy VARCHAR（255）
SET @OrderBy ='名称ASC选择[在这里你的专栏] FROM SYS.TABLES
ORDER BY
   情况下，当@OrderBy ='名称ASC'然后命名ELSE空END ASC，
   情况下，当@OrderBy ='名称DESC'然后命名ELSE空END DESC，
   CASE WHEN @OrderBy ='OBJECT_ID ASC'，那么ELSE OBJECT_ID空END ASC，
   CASE WHEN @OrderBy ='OBJECT_ID DESC'，那么ELSE OBJECT_ID空END DESC
 

3）最后一个选项是做同样的＃2，但在C＃code。只要确保你不只是在ORDER BY粘性用户输入子句，因为这将是vunerable SQL注入攻击。

这是安全的，因为排序依据URL参数名称说明; DROP TABLE用户将完全忽略

 字符串SafeOrderBy =;
字符串的OrderBy =的Request.QueryString [排序依据];
//解决了GET瓦尔
如果（排序依据== NULL）
    ORDERBY =名ASC;如果（排序依据==名称。说明）
{
     SafeOrderBy ==名称。说明
}
字符串的SelectCommand =SELECT CAT_ID为ID，cat_name AS的名字从table_name的ORDER BY
的SelectCommand + = SafeOrderBy;
 

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

解决方案

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 ;

这篇关于使用的SqlParameter创建Order By子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！