使用 C# SqlCommand 的动态 SQL [英] Dynamic SQL with C# SqlCommand

问题描述

我想使用 c# 的 SqlCommand 创建一个动态 SQL 查询，其中甚至表也是一个参数，以避免注入尝试.如下图:

I'd like to create a dynamic SQL query with c#'s SqlCommand where even the table is a parameter, so as to avoid injection attempts. Like below:

comm.CommandText = "SELECT * FROM @tbl WHERE cond=@cond";
comm.Parameters.AddWithValue("tbl","TABLENAME");
comm.Parameters.AddWithValue("cond","CONDITION");

但是，我发现这是不允许的.我研究过将动态 SQL 与执行一起使用，但这似乎仅适用于存储过程.我可以使用带有 SqlCommand 的表名参数执行动态 SQL 吗?如果没有，如何避免 SQL 注入问题?

However, I have found that this is not allowed. I've looked into using Dynamic SQL with an execute, but that seems to be only for stored procedures. Can I use Dynamic SQL with an Execute using parameters for the table name with an SqlCommand? If not, how can this be done to avoid SQL injection problems?

谢谢！

推荐答案

使用 SqlCommandBuilder.QuoteIdentifier 转义表名的方法.

Use SqlCommandBuilder.QuoteIdentifier method to escape table names.

SqlCommandBuilder builder = new SqlCommandBuilder();
string tableName ="SomeTable";
string escapedTableName = builder.QuoteIdentifier(tableName);

稍后您可以在字符串中使用转义的表名，例如:

Later you can use the escaped table name in your string like:

comm.CommandText = "SELECT * FROM "+ escapedTableName +"  WHERE cond=@cond";

这篇关于使用 C# SqlCommand 的动态 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！