sp_executesql对SET语句使用OUTPUT参数

Question

我想使用sp_executesql为sp_send_dbmail设置@email_body

为什么

以前，我将参数直接连接到@email_body，这很容易导致SQL注入。像这个

SET @email_body = 'some html' + @id + 'some html' + @name + 'some html';

SQL注入是可能的，因为我们无法控制输入。

我做了什么

所以上面只是一个例子我的查询看起来很像这个

DECLARE @sql NVARCHAR(MAX);
DECLARE @email_body VARCHAR(max);
DECLARE @ParamDef NVARCHAR(MAX);
DECLARE @id INT;
DECLARE @name NVARCHAR(MAX);
DECLARE @status NVARCHAR(MAX);
-- select statement to input have input in @id, @name and @status from sometable;
SET @sql = N'SET @email_bodyOUT = CASE @status  
WHEN ''A'' THEN ''SOME HTML'' + @id + ''SOME HTML'' + @name + ''SOME HTML'' 
WHEN ''B'' THEN ''SOME HTML'' + @id + ''SOME HTML'' + @name + ''SOME HTML'' 
WHEN ''C'' THEN ''SOME HTML'' + @id + ''SOME HTML'' + @name + ''SOME HTML'' END';
SET @ParamDef = N'@email_bodyOUT VARCHAR(MAX) OUTPUT
, @status NVARCHAR(MAX)
, @id INT
, @name NVARCAHR(MAX)';
EXECUTE sp_executesql @sql 
, @ParamDef
, @email_bodyOUT = @email_body OUTPUT
, @status = @status
, @id = @id 
, @name = @name;
SELECT @email_bodyOUT;
--executing sp_send_dbmail and send email

但@email_body中没有附加任何内容，邮件也为空。当我用sp_executesql运行类似这样的简单查询时，它工作得很好

DECLARE @First_Name NVARCHAR(200) = 'abc';
EXEC sp_executesql N'
declare @HTML nvarchar(max) =  ''Hi'' + @FN + '' , Rest of HTML email :) :)  ''
SELECT @HTML',
N'@FN VARCHAR(8000)',
@First_Name;

提前感谢您的帮助：(

Answer 1

发现问题，原因是其中一个参数为Null，导致@emailbody为空。通过ISNULL函数修复。