如何在不易受 SQL 注入影响的情况下参数化 SQL 表

Question

我正在编写一个 C# 类库，其中的功能之一是能够创建与任何现有表的架构匹配的空数据表。

例如，这个：

private DataTable RetrieveEmptyDataTable(string tableName)
{
var table = new DataTable() { TableName = tableName };
using var command = new SqlCommand($"SELECT TOP 0 * FROM {tableName}", _connection);
using SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(table);
return table;
}

上面的代码有效，但它有一个明显的安全漏洞：SQL注入。

我的第一直觉是像这样参数化查询：

using var command = new SqlCommand("SELECT TOP 0 * FROM @tableName", _connection);
command.Parameters.AddWithValue("@tableName", tableName);

但这会导致以下异常：

必须声明表变量"@tableName">

在快速搜索堆栈溢出后，我发现了这个问题，建议使用我的第一种方法(具有sqli漏洞的方法)。这根本没有帮助，所以我继续搜索并找到了这个问题，它说唯一安全的解决方案是对可能的表进行硬编码。同样，这不适用于我的类库，该类库需要适用于任意表名。

我的问题是：如何在不易受SQL注入影响的情况下参数化表名？

Answer 1

任意表名仍然必须存在，因此您可以先检查它是否存在：

IF EXISTS (SELECT 1 FROM sys.objects WHERE name = @TableName)
BEGIN
... do your thing ...
END

此外，如果要允许用户从中选择的表列表是已知且有限的，或者与特定的命名约定(如dbo.Sales%)匹配，或者属于特定的架构(如Reporting)，则可以添加其他谓词来检查这些谓词。

这要求您将表名作为正确的参数传入，而不是串联或标记替换。(请不要将AddWithValue()用于任何事情。

一旦检查对象是否真实且有效，您仍然必须动态构建 SQL 查询，因为您仍然无法参数化表名。不过，您仍然应该应用QUOTENAME()，正如我在这些帖子中所解释的那样：

• 保护自己免受 SQL Server 中的 SQL 注入 - 第 1 部分
• 保护自己免受 SQL Server 中的 SQL 注入 - 第 2 部分

所以最终的代码是这样的：

CREATE PROCEDURE dbo.SelectFromAnywhere
@TableName sysname 
AS
BEGIN
IF EXISTS (SELECT 1 FROM sys.objects
WHERE name = @TableName)
BEGIN
DECLARE @sql nvarchar(max) = N'SELECT * 
FROM ' + QUOTENAME(@TableName) + N';';
EXEC sys.sp_executesql @sql;
END
ELSE
BEGIN
PRINT 'Nice try, robot.';
END
END
GO

如果您还希望它位于某个定义的列表中，您可以添加

AND @TableName IN (N't1', N't2', …)

或者LIKE <some pattern>或加入sys.schemas或你有什么。

如果没有人有权修改过程以更改检查，则无法传递给允许您执行任何恶意操作的@TableName值，除了可能从另一个您意想不到的表中进行选择，因为具有太多访问权限的人能够在调用代码之前创建。替换像--或;这样的字符并不能使它更安全。

Answer 2

您可以将表名传递给 SQL Server，以对其应用quotename()以正确引用它，然后仅使用带引号的名称。

大致如下：

...
string quotedTableName = null;
using (SqlCommand command = new SqlCommand("SELECT quotename(@tablename);", connection))
{
SqlParameter parameter = command.Parameters.Add("@tablename", System.Data.SqlDbType.NVarChar, 128 /* nvarchar(128) is (currently) equivalent to sysname which doesn't seem to exist in SqlDbType */);
parameter.Value = tableName;
object buff = command.ExecuteScalar();
if (buff != DBNull.Value
&& buff != null /* theoretically not possible since a FROM-less SELECT always returns a row */)
{
quotedTableName = buff.ToString();
}
}
if (quotedTableName != null)
{
using (SqlCommand command = new SqlCommand($"SELECT TOP 0 FROM { quotedTableName };", connection))
{
...
}
}
...

(或者直接在SQL Server上执行动态部分，也使用quotename()。但这似乎过于乏味和不必要的乏味，特别是如果你在不同的地方在桌子上做不止一个操作。

Answer 3

Aaron Bertrand 的回答解决了这个问题，但存储过程对于可能与任何数据库交互的类库没有用。这是使用他的编写RetrieveEmptyDataTable的方法(我问题的方法) 答：

private DataTable RetrieveEmptyDataTable(string tableName)
{
const string tableNameParameter = "@TableName";
var query =
"  IF EXISTS (SELECT 1 FROM sys.objectsn" +
$"      WHERE name = {tableNameParameter})n" +
"  BEGINn" +
"    DECLARE @sql nvarchar(max) = N'SELECT TOP 0 * n" +
$"      FROM ' + QUOTENAME({tableNameParameter}) + N';';n" +
"    EXEC sys.sp_executesql @sql;n" +
"END";

using var command = new SqlCommand(query, _connection);
command.Parameters.Add(tableNameParameter, SqlDbType.NVarChar).Value = tableName;
using SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
var table = new DataTable() { TableName = tableName };
Connect();
dataAdapter.Fill(table);
Disconnect();
return table;
}