参数化查询 ado.net 问题 [英] Parameterized query ado.net issue

问题描述

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

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 用法所做的).

You can't parameterise things like table-names, columns, order-by, etc. They are the query. You will need to white-list the expected values (to avoid SQL injection) and concatenate it into the query directly (which is what your string.Format usage does).

目前，order-by 位于变量的值上，每行都不会改变.本质上，排序(如所写)被忽略了.

At the moment, the order-by is on the vale of the variable, which doesn't change per-row. Essentially, the sort (as written) is ignored.

这篇关于参数化查询 ado.net 问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！