MSSQL传递参数查询如何正确实现？

核心原则：使用参数化查询

永远不要使用字符串拼接来构建 SQL 查询。

这种写法是极其危险的：

-- 危险！不要这样做！
DECLARE @username NVARCHAR(50) = 'admin';
DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM Users WHERE Username = ''' + @username + '''';
EXEC sp_executesql @sql;

@username 的值是 ' OR '1'='1，那么查询就变成了 SELECT * FROM Users WHERE Username = '' OR '1'='1'，这会返回所有用户，这就是典型的 SQL 注入攻击。

正确的方法是使用参数化查询，将 SQL 语句和数据（参数）分开处理。

使用 sp_executesql (最灵活、最推荐)

sp_executesql 是一个系统存储过程，它允许你执行动态 SQL 语句，并且支持参数，这是处理动态 SQL 的首选方法。

语法结构

EXEC sp_executesql
    @stmt,              -- NVARCHAR 类型的 SQL 语句字符串
    @params,            -- NVARCHAR 类型的参数定义字符串 (类似 CREATE PROCEDURE 的参数定义)
    <@param1 = value1,  -- 赋值给参数的变量
     @param2 = value2,
     ...>;

示例 1：简单的字符串参数查询

假设我们想根据用户名查询用户信息。

-- 1. 声明变量，用于存储查询参数
DECLARE @username_to_find NVARCHAR(50) = 'john.doe';
DECLARE @user_id INT;
DECLARE @user_email NVARCHAR(100);
-- 2. 定义 SQL 语句和参数
--    注意：SQL 语句中的参数占位符是 @param_name，和下面的参数定义保持一致
DECLARE @sql NVARCHAR(MAX);
DECLARE @params NVARCHAR(255);
SET @sql = N'SELECT @id_out = Id, @email_out = Email FROM Users WHERE Username = @username_in';
SET @params = N'@username_in NVARCHAR(50), @id_out INT OUTPUT, @email_out NVARCHAR(100) OUTPUT';
-- 3. 执行存储过程，传递参数
EXEC sp_executesql
    @sql,
    @params,
    @username_in = @username_to_find, -- 输入参数
    @id_out = @user_id OUTPUT,       -- 输出参数
    @email_out = @user_email OUTPUT;  -- 输出参数
-- 4. 查看结果
SELECT @user_id AS UserID, @user_email AS Email;

优点：

• 防止 SQL 注入：SQL 语句和数据完全分离。
• 性能高：SQL Server 可以缓存执行计划，对于重复执行的 SQL（仅参数不同），能直接重用缓存的计划，避免重新解析和编译。
• 灵活：可以处理动态的表名、列名（虽然列名参数化有限制）。
• 支持输入/输出参数：可以像存储过程一样返回结果。

使用存储过程 (Stored Procedure)

对于固定的业务逻辑,创建存储过程是最佳实践，它将 SQL 逻辑封装在数据库中，应用程序只需调用存储过程并传递参数。

创建存储过程

CREATE PROCEDURE sp_GetUserByUsername
    @username NVARCHAR(50)
AS
BEGIN
    -- SET NOCOUNT ON 减少网络流量
    SET NOCOUNT ON;
    SELECT Id, Username, Email, CreatedDate
    FROM Users
    WHERE Username = @username;
END
GO

执行存储过程

从应用程序（如 C#、Java、Python）或另一个批处理中调用它。

-- 使用变量传参
DECLARE @my_user NVARCHAR(50) = 'jane.smith';
EXEC sp_GetUserByUsername @username = @my_user;
-- 直接传值
EXEC sp_GetUserByUsername 'test.user';

优点：

• 安全：默认防止 SQL 注入。
• 性能：执行计划被永久缓存，性能极高。
• 封装性：将业务逻辑与数据访问层分离。
• 可维护性：集中管理 SQL 逻辑，修改存储过程无需改动应用程序代码。
• 权限控制：可以授予用户执行存储过程的权限，而不授予其直接访问表的权限。

在应用程序代码中传递参数 (以 C# 为例)

在实际开发中,参数通常由应用程序（如 C#、Python、Java）传递给 SQL Server，这里以 C# 的 ADO.NET 为例。

C# 示例

using System;
using System.Data;
using System.Data.SqlClient;
public class UserRepository
{
    private readonly string _connectionString = "Your_Connection_String_Here";
    public User GetUserByUsername(string username)
    {
        User user = null;
        // 使用 using 语句确保连接和命令对象被正确释放
        using (SqlConnection connection = new SqlConnection(_connectionString))
        {
            // 1. 定义 SQL 语句和参数占位符 (@p_username)
            string sql = "SELECT Id, Username, Email FROM Users WHERE Username = @p_username";
            // 2. 创建 SqlCommand 对象
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                // 3. 添加参数
                //    AddWithValue 会自动推断参数类型，但显式指定类型更安全
                command.Parameters.AddWithValue("@p_username", username);
                try
                {
                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            user = new User
                            {
                                Id = reader.GetInt32(0),
                                Username = reader.GetString(1),
                                Email = reader.GetString(2)
                            };
                        }
                    }
                }
                catch (Exception ex)
                {
                    // 处理异常
                    Console.WriteLine("An error occurred: " + ex.Message);
                }
            }
        }
        return user;
    }
}
public class User
{
    public int Id { get; set; }
    public string Username { get; set; }
    public string Email { get; set; }
}

关键点：

• @p_username：这是参数占位符，SQL Server 会识别它并将其作为参数处理，而不是 SQL 字符串的一部分。
• command.Parameters.AddWithValue()：这是将应用程序变量安全地添加到 SQL 命令中的方法，它会处理所有必要的转义和类型转换，从根本上杜绝 SQL 注入。
• using 语句：确保数据库资源（连接、命令、读取器）被及时释放，避免内存泄漏和连接泄露。

总结与对比

最终建议：

• 优先使用存储过程：对于绝大多数业务场景，将 SQL 逻辑封装在存储过程中是最佳选择。
• 其次使用 sp_executesql：当必须使用动态 SQL 时，sp_executesql 是唯一安全且高效的选择。
• 坚决避免字符串拼接：在任何情况下都不要使用 号或 CONCAT 来拼接 SQL 语句和参数。