SQL Server参数化查询如何防注入且高效？

什么是参数化查询？

参数化查询是一种将 SQL 语句的结构（代码）和数据（参数）分离的查询方法。

就是你先写好 SQL 语句的“骨架”，然后用占位符（参数）来代替那些用户输入或动态变化的数据，在执行时，你再将这些数据作为参数传递给数据库引擎。

一个简单的比喻：

• 非参数化查询：就像直接把完整的句子写死。"SELECT * FROM Users WHERE Name = '张三' AND Age = 30"，如果用户想查“李四”，你就要重新构建整个字符串。
• 参数化查询：就像一个填空题。"SELECT * FROM Users WHERE Name = @UserName AND Age = @UserAge"。@UserName 和 @UserAge 是“空”，无论用户输入“张三”、“李四”还是任何内容，你只需要把新的名字和年龄“填”进去即可，句子骨架保持不变。

为什么参数化查询如此重要？（核心优势）

参数化查询是编写安全、高效数据库代码的基石，它的主要优势体现在以下三个方面：

安全性：防止 SQL 注入攻击

这是参数化查询最核心、最重要的优势。

SQL 注入 是一种恶意攻击，攻击者通过在输入字段中插入恶意的 SQL 代码，来篡改后台执行的 SQL 命令，从而窃取、修改或删除数据。

示例：非参数化查询的危险性

假设你有一个登录页面,用户输入用户名和密码，后端代码是这样写的（伪代码）：

-- 危险！直接拼接字符串
string userName = "admin";
string password = "' OR '1'='1"; -- 恶意输入
string sql = "SELECT * FROM Users WHERE UserName = '" + userName + "' AND Password = '" + password + "'";

最终执行的 SQL 语句会变成：

SELECT * FROM Users WHERE UserName = 'admin' AND Password = '' OR '1'='1'

因为 '1'='1' 永远为真，这个查询会返回 Users 表中的所有用户，攻击者就成功绕过了登录验证。

参数化查询如何防止注入？

使用参数化查询,上面的代码会是这样：

-- 安全！使用参数
string sql = "SELECT * FROM Users WHERE UserName = @UserName AND Password = @Password";

即使攻击者输入 "' OR '1'='1" 作为密码，数据库引擎也会将其视为一个字符串字面值，而不是 SQL 代码的一部分，数据库会去寻找一个密码字段的值恰好是 "' OR '1'='1" 的用户，这几乎不可能成功，从而彻底阻止了注入攻击。

性能：利用预编译和执行计划缓存

SQL Server 在执行 SQL 语句时，会经历两个主要阶段：

1. 编译：将 SQL 文本解析、优化，生成一个执行计划，这是一个非常耗时的过程。
2. 执行：按照执行计划去操作数据。

对于非参数化查询,即使只是参数值不同（如 WHERE Name = '张三' 和 WHERE Name = '李四'），SQL Server 也会认为它们是两个完全不同的 SQL 语句，因此需要分别进行编译。

而对于参数化查询,SQL Server 会识别出它们的结构是相同的，只是参数值不同，在第一次执行后，它会将编译好的执行计划缓存起来，当后续再次执行相同结构的查询时，数据库会直接从缓存中取出执行计划并执行，跳过了昂贵的编译阶段，从而大大提高了性能。

注意：对于参数嗅探问题，有时需要手动干预，但总体而言，参数化查询的性能优势远大于其潜在问题。

可读性与可维护性

参数化查询的 SQL 语句结构清晰，代码和数据分离，使得代码更容易阅读、调试和维护，当你需要修改查询逻辑时，不需要关心参数的具体值，只需关注 SQL 语句本身。

如何在 SQL Server 中使用参数化查询？

参数化查询可以在多种场景下使用,下面介绍最常见的几种。

在 ADO.NET 中使用（C# / .NET 应用程序）

这是最经典和常见的方式,使用 SqlCommand 对象，并通过其 Parameters 属性来添加参数。

using (SqlConnection connection = new SqlConnection("Your_Connection_String"))
{
    connection.Open();
    // 1. 定义 SQL 语句，使用 @param 作为占位符
    string sql = "SELECT * FROM Products WHERE CategoryId = @CategoryId AND Price > @MinPrice";
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        // 2. 添加参数并赋值
        command.Parameters.AddWithValue("@CategoryId", 2); // 假设类别ID是2
        command.Parameters.AddWithValue("@MinPrice", 100.00); // 假设最低价格是100
        // 3. 执行查询
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                // 处理结果...
                Console.WriteLine($"Product: {reader["ProductName"]}, Price: {reader["Price"]}");
            }
        }
    }
}

最佳实践：

• 使用 AddWithValue 方便快捷。
• 对于特定数据类型（如 DateTime, Guid），最好使用 Add 方法并明确指定 SqlDbType，以避免类型转换问题。
• 永远不要用字符串拼接的方式构造 SQL 语句，即使是为了拼接表名或列名（这需要更复杂的安全处理）。

在存储过程中使用

存储过程是数据库中预编译的 SQL 代码块，天然就是参数化的，这是企业级应用中推荐的做法。

创建存储过程：

CREATE PROCEDURE usp_GetProductsByCategoryAndPrice
    -- 定义参数
    @CategoryId INT,
    @MinPrice DECIMAL(10, 2)
AS
BEGIN
    -- 使用参数执行查询
    SELECT 
        ProductID,
        ProductName,
        Price,
        CategoryId
    FROM 
        Products
    WHERE 
        CategoryId = @CategoryId AND Price > @MinPrice;
END
GO

在应用程序中调用存储过程：

using (SqlConnection connection = new SqlConnection("Your_Connection_String"))
{
    connection.Open();
    using (SqlCommand command = new SqlCommand("usp_GetProductsByCategoryAndPrice", connection))
    {
        // 指定命令类型为存储过程
        command.CommandType = CommandType.StoredProcedure;
        // 添加参数
        command.Parameters.AddWithValue("@CategoryId", 2);
        command.Parameters.AddWithValue("@MinPrice", 100.00);
        // 执行并获取结果
        using (SqlDataReader reader = command.ExecuteReader())
        {
            // ... 处理结果
        }
    }
}

使用 Entity Framework (EF Core)

ORM（对象关系映射）框架如 EF Core，在底层自动将你的 LINQ 查询转换为参数化 SQL 查询，你几乎不需要手动编写 SQL。

using (var context = new MyDbContext())
{
    // LINQ 查询会被 EF Core 自动翻译成参数化 SQL
    var products = context.Products
                          .Where(p => p.CategoryId == 2 && p.Price > 100.00m)
                          .ToList();
    // ...
}

EF Core 会生成类似这样的 SQL：

SELECT [p].[ProductId], [p].[CategoryId], [p].[Price], [p].[ProductName]
FROM [Products] AS [p]
WHERE ([p].[CategoryId] = @__CategoryId_0) AND ([p].[Price] > @__Price_1)

使用 SQL Server Management Studio (SSMS) 测试

你也可以直接在 SSMS 中测试参数化查询，这对于调试存储过程或编写复杂查询非常有用。

1. 打开 SSMS，连接到你的数据库。
2. 在查询窗口中,使用 DECLARE 关键字声明变量，并使用 EXEC 或 EXECUTE 来执行参数化查询。

-- 声明变量并赋值
DECLARE @CategoryIdParam INT = 2;
DECLARE @MinPriceParam DECIMAL(10, 2) = 100.00;
-- 执行参数化查询
EXEC usp_GetProductsByCategoryAndPrice 
    @CategoryId = @CategoryIdParam, 
    @MinPrice = @MinPriceParam;

参数嗅探 及其应对

虽然参数化查询性能很高,但有时也会遇到一个问题：参数嗅探。

什么是参数嗅探？ 当 SQL Server 第一次编译一个参数化查询时，它会使用传入的参数值来生成一个“最优”的执行计划，如果这个第一次传入的值是“特殊”的（比如返回了极多或极少的行），那么生成的执行计划可能只对这个特定值有效，当后续传入“常规”值时，数据库可能会重用这个“坏”的计划，导致性能很差。

如何解决？

1. 使用 OPTION (RECOMPILE)：强制查询在每次执行时都重新编译，为当前参数值生成最优计划，适用于参数值变化极大，且查询执行成本不高的场景。

   SELECT * FROM Products WHERE CategoryId = @CategoryId OPTION (RECOMPILE);

2. 使用 LOCAL VARIABLE：将参数值赋给一个局部变量，然后查询中使用这个变量，SQL Server 在编译时无法知道局部变量的值，因此会使用统计信息来生成一个“平均”水平的执行计划，通常更稳定。

   -- 在存储过程中
   DECLARE @LocalCategoryId INT = @CategoryId;
   SELECT * FROM Products WHERE CategoryId = @LocalCategoryId;

3. 使用 OPTIMIZE FOR：强制查询为某个特定值（可以是 UNKNOWN）生成执行计划。

   -- 强制使用第一次传入的参数值生成计划
   SELECT * FROM Products WHERE CategoryId = @CategoryId OPTION (OPTIMIZE FOR (@CategoryId = 2));
   -- 强制使用统计信息中的平均值生成计划
   SELECT * FROM Products WHERE CategoryId = @CategoryId OPTION (OPTIMIZE FOR UNKNOWN);

黄金法则： 永远不要信任来自客户端的输入，始终使用参数化查询来构建你的动态 SQL 语句。 这是每一位数据库开发者必须遵守的安全准则。