如何将对称密钥作为参数传入SQL查询

由于SQL Server中OPEN SYMMETRIC KEY语句不支持直接使用参数指定密钥名，你需要借助动态SQL来实现参数化传入，同时要注意防范SQL注入风险。以下是两种可行的实现方式：

方法一：直接使用动态SQL拼接参数 #

DECLARE @SymKeyName NVARCHAR(128) = N'SymKey'; -- 可替换为外部传入的参数
DECLARE @CertName NVARCHAR(128) = N'CertiFi';

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'OPEN SYMMETRIC KEY ' + QUOTENAME(@SymKeyName) + N'
DECRYPTION BY CERTIFICATE ' + QUOTENAME(@CertName) + N';

SELECT * FROM MasterBlaster;

CLOSE SYMMETRIC KEY ' + QUOTENAME(@SymKeyName) + N';';

EXEC sp_executesql @SQL;

• 使用QUOTENAME()函数对密钥名和证书名进行转义，能有效避免SQL注入攻击。
• 你可以将@SymKeyName和@CertName替换为外部传入的变量或程序参数。

方法二：封装为存储过程接收参数 #

如果需要重复调用，建议封装成存储过程：

CREATE PROCEDURE dbo.GetMasterBlasterData
    @SymKeyName NVARCHAR(128),
    @CertName NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX);
    
    SET @SQL = N'OPEN SYMMETRIC KEY ' + QUOTENAME(@SymKeyName) + N'
    DECRYPTION BY CERTIFICATE ' + QUOTENAME(@CertName) + N';

    SELECT * FROM MasterBlaster;

    CLOSE SYMMETRIC KEY ' + QUOTENAME(@SymKeyName) + N';';

    EXEC sp_executesql @SQL;
END;

调用存储过程时直接传入参数即可：

EXEC dbo.GetMasterBlasterData @SymKeyName = N'SymKey', @CertName = N'CertiFi';

额外说明：密码解密场景的参数化 #

如果你的对称密钥是通过密码解密而非证书，建议对密码进行参数化传递（避免明文拼接）：

DECLARE @SymKeyName NVARCHAR(128) = N'SymKey';
DECLARE @DecryptPassword NVARCHAR(128) = N'YourSecurePassword';

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'OPEN SYMMETRIC KEY ' + QUOTENAME(@SymKeyName) + N'
DECRYPTION BY PASSWORD = @Password;

SELECT * FROM MasterBlaster;

CLOSE SYMMETRIC KEY ' + QUOTENAME(@SymKeyName) + N';';

EXEC sp_executesql @SQL, N'@Password NVARCHAR(128)', @Password = @DecryptPassword;

注意事项 #

• 确保执行查询的账号拥有OPEN SYMMETRIC KEY权限，以及访问目标证书、密钥和表的权限。
• 始终使用QUOTENAME()处理对象名称，防止注入风险；敏感参数（如密码）通过sp_executesql的参数列表传递，不要直接拼接进SQL字符串。

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