如何在C#中参数化SQL表名且避免SQL注入安全漏洞

SQL参数仅支持传递查询值，无法用于替换表名、列名等SQL标识符，所以直接用@tableName作为表名占位符会抛出找不到表变量的异常。以下是两种支持任意合法表名、同时完全避免SQL注入的实现方案：

方案1：表名合法性校验 + QUOTENAME转义动态查询 #

核心逻辑是先校验传入的表名确实是当前数据库的合法表，再通过SQL Server内置的QUOTENAME函数将表名转义为安全标识符，再执行动态查询：

// 校验表名是否为当前数据库的合法表
private bool IsValidTableName(string tableName)
{
    const string checkSql = "SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName";
    using var command = new SqlCommand(checkSql, _connection);
    command.Parameters.AddWithValue("@TableName", tableName);
    return (int)command.ExecuteScalar() > 0;
}

private DataTable RetrieveEmptyDataTable(string tableName)
{
    if (!IsValidTableName(tableName))
    {
        throw new ArgumentException($"指定的表 {tableName} 不存在", nameof(tableName));
    }

    var table = new DataTable() { TableName = tableName };
    // 通过QUOTENAME转义表名构造安全的动态SQL
    const string querySql = @"
        DECLARE @Sql NVARCHAR(MAX) = 'SELECT TOP 0 * FROM ' + QUOTENAME(@TableName)
        EXEC sp_executesql @Sql
    ";
    using var command = new SqlCommand(querySql, _connection);
    command.Parameters.AddWithValue("@TableName", tableName);
    using SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
    dataAdapter.Fill(table);

    return table;
}

如果需要支持带Schema的表名（如dbo.User），可以拆分Schema和表名分别校验、分别用QUOTENAME包裹即可。

方案2：查询系统表手动构造DataTable #

该方案完全避免拼接表名到业务查询语句，所有表名仅作为查询系统表的参数传递，彻底消除SQL注入风险，性能也更优：

private DataTable RetrieveEmptyDataTable(string tableName)
{
    const string columnQuery = @"
        SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = @TableName
        ORDER BY ORDINAL_POSITION
    ";
    
    var table = new DataTable() { TableName = tableName };
    using var command = new SqlCommand(columnQuery, _connection);
    command.Parameters.AddWithValue("@TableName", tableName);
    using var reader = command.ExecuteReader();
    
    if (!reader.HasRows)
    {
        throw new ArgumentException($"指定的表 {tableName} 不存在", nameof(tableName));
    }

    while (reader.Read())
    {
        string columnName = reader.GetString(0);
        string sqlDataType = reader.GetString(1);
        bool allowDbNull = reader.GetString(2) == "YES";
        Type csharpType = MapSqlTypeToCSharpType(sqlDataType);
        
        var column = new DataColumn(columnName, csharpType)
        {
            AllowDBNull = allowDbNull
        };
        // 可按需补充列长度、主键、默认值等其他属性配置
        table.Columns.Add(column);
    }

    return table;
}

// SQL数据类型与C#类型映射，可根据业务需要补充全所有用到的类型
private Type MapSqlTypeToCSharpType(string sqlType)
{
    return sqlType.ToLower() switch
    {
        "int" => typeof(int),
        "bigint" => typeof(long),
        "varchar" or "nvarchar" or "text" or "ntext" => typeof(string),
        "datetime" or "datetime2" or "smalldatetime" => typeof(DateTime),
        "bit" => typeof(bool),
        "decimal" or "numeric" or "money" or "smallmoney" => typeof(decimal),
        "float" => typeof(double),
        "real" => typeof(float),
        "uniqueidentifier" => typeof(Guid),
        _ => throw new NotSupportedException($"暂不支持的SQL数据类型：{sqlType}")
    };
}

内容的提问来源于stack exchange，提问作者Nigel