使用列名C＃SQL参数化 [英] Using C# SQL Parameterization on Column Names

问题描述

我有一个问题。我想这个工作，但它不会：

I'm having a problem. I want this to work, but it doesn't:

SqlDataSource.SelectCommand = "SELECT blah1, blah2 FROM myTable WHERE @ColumnName = @Value";

SqlDataSource.SelectParameters.Add("ColumnName", System.Data.DbType.String, "one");
SqlDataSource.SelectParameters.Add("Value", System.Data.DbType.String, "two");

这不会顶替第一个放慢参数的ColumnName。如果我删除了参数，并将列名像这样，它会工作：

It won't substitue the first paramter "ColumnName." If I remove that parameter and place the column name in it like this, it will work:

SqlDataSource.SelectCommand = "SELECT blah1, blah2 FROM myTable WHERE one = @Value";

SqlDataSource.SelectParameters.Add("Value", System.Data.DbType.String, "two");

我有一个用户界面，用户可以选择要搜索的数据库列名。我要保护自己不受任何形式的注入攻击。任何想法我怎样才能使这项工作？

I have a UI where the user can select the DB column name to search on. I want to protect myself from any sort of injection attacks. Any ideas how I can make this work?

我读到一个想法是使用查表从的DropDownList 走索引和拉列名的方式。我可以做的工作，但我宁愿得到的参数，因为这似乎更自然的我，如果可能的工作。

One idea I read about was to use a look-up table to take the index from the DropDownList and pull column names that way. I could make that work, but I'd rather get parameterization working if possible since that seems more natural to me.

感谢您预先任何帮助，您可以提供。

Thank you in advance for any help you can provide.

推荐答案

由于查询参数都解决了的之后的SQL的解析，并生成一个执行计划，你不能真正动态构建SQL与参数。我会建议建立SQL字符串本身，当然在一个安全的方式。也许首先创建一个枚举有效的列名：

Since query parameters are resolved after the SQL is parsed and an execution plan is generated, you can't actually dynamically build SQL with parameters. I would recommend building the SQL string itself, in a safe way of course. Perhaps first create an enum of valid column names:

enum DbColumns { One, Two, Three };

，然后生成SQL字符串，像这样：

And then build the SQL string like so:

DbColumns colName = (DbColumns)Enum.Parse(typeof(DbColumns), "One");
SqlDataSource.SelectCommand = String.Format("SELECT blah1, blah1 FROM myTable WHERE {0} = @Value", colName);

另一个想法是使用常规的前pression验证列名，也许只有让 [A-Z] 。

这篇关于使用列名C＃SQL参数化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！