问题描述
我使用这个查询分页
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是我得到一个异常。如果u使用这样它工作正常，但我希望把它参数化查询。该如何解决？
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).
目前，该订单由是变量，不按行改变的山谷。从本质上讲，排序（书面）被忽略。
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问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！