参数化查询 ado.net 问题

Parameterized query ado.net issue(参数化查询 ado.net 问题)

问题描述

我正在使用此查询进行分页

I am using this query for pagination

string selectStatement = "SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY @sortMember @sortDirection ) AS RowNum, * FROM School) AS Rows WHERE RowNum > @pageFrom AND RowNum < @pageTo ";

command.Parameters.Add("@sortDirection", System.Data.SqlDbType.NVarChar, 50);
command.Parameters["@sortDirection"].Value = cmd.SortDescriptors.Count == 0 ? "" : cmd.SortDescriptors[0].SortDirection == System.ComponentModel.ListSortDirection.Ascending ? "" : "DESC";

如果 sortDirection 是 "" 我得到一个异常.如果你像这样使用它，它工作正常，但我想让它参数化查询.解决办法是什么?

if sortDirection is "" i get an exception. if u use it like this it works fine but i want to make it parameterized query. what is the solution?

 string selectStatement = string.Format("SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY @sortMember {0} ) AS RowNum, * FROM School) AS Rows WHERE RowNum > @pageFrom AND RowNum < @pageTo ",System.ComponentModel.ListSortDirection.Ascending ? "" : "DESC); 

我得到的例外是:'@sortDirection' 附近的语法不正确.

The exception i get is :Incorrect syntax near '@sortDirection'.

推荐答案

您不能参数化诸如表名、列、排序依据等内容.它们是查询.您需要将预期值列入白名单(以避免 SQL 注入)并将其直接连接到查询中(这是您的 string.Format 用法所做的).