是否可以在不求助于动态 SQL 的情况下查询用户指定的列名? [英] Is it possible to query a user-specified column name without resorting to dynamic SQL?
问题描述
我正在尝试实现具有以下签名的 API:
I am trying to implement an API with the following signature:
public static List<string> SearchDatabase(
string column,
string value);
API 的实现需要构造一个带有 WHERE
子句的 SQL SELECT
查询,该子句使用在 column
参数中指定的列名.
The implementation of the API needs to construct an SQL SELECT
query with a WHERE
clause that uses a column name specified in the column
parameter.
查询:
string query =
string.Format(
"SELECT Name, @Column " +
"FROM Db1 " +
"INNER JOIN Db2 ON Db1.Id = Db2.Id " +
"WHERE @Column = @Value");
SQL 命令和参数:
SqlCommand selectCmd =
new SqlCommand(
query,
connection);
selectCmd.CommandTimeout = SqlCommandTimeout;
selectCmd.Parameters.AddRange(
new SqlParameter[]
{
new SqlParameter("@Column", column),
new SqlParameter("@Value", value)
});
我是这样执行的:
SqlDataReader sqlDataReader = selectCmd.ExecuteReader();
while (sqlDataReader.Read())
{
// ...
}
问题是 sqlDataReader
不返回任何行,所以我没有进入上面的 while
循环.
The problem is that sqlDataReader
doesn't return any rows, so I don't go into the while
loop above.
但是,如果我将上面查询的最后一行从:
However, if I change the last line of the query above from:
"WHERE @Column = @Value");
到
"WHERE Vendor = @Value");
(即,将列名硬编码为供应商")然后它就可以工作了.
(i.e. hardcode the column name to 'Vendor') then it works.
我从我所做的研究中了解到,不能将列名作为参数传递,而只能传递我们正在查询的值.但是,它似乎让我在 SELECT
子句中使用 @Column
参数,而不是 WHERE
子句.
My understanding from research I have done is that it's not possible to pass column names as parameters, but only values we are querying on. But, it does appear to let me use the @Column
parameter in the SELECT
clause, just not the WHERE
clause.
由于 SQL 注入的问题,我不想求助于动态 SQL.有没有其他办法解决这个问题?
I don't want to resort to dynamic SQL because of the issues with SQL injection. Is there another way around this?
推荐答案
遗憾的是表名、列名不能参数化.因为你知道表的结构,所以你可以在这个参数中有一个可能的列名的白名单,然后使用字符串连接来避免 SQL 注入:
Unfortunately table names, column names cannot be parametrized. Because you know the structure of the table you could have a whitelist of possible column names in this parameter and then use string concatenation for it to avoid SQL injection:
"WHERE " + Sanitize(column) + " = @Value");
这篇关于是否可以在不求助于动态 SQL 的情况下查询用户指定的列名?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!