CODEWITHSUNDEEP
sql-injectionsp-executesqlsp-send-dbmail
Vidhi Shah
Vidhi Shah

Reputation: 23

sp_executesql with OUTPUT Parameter on SET statement

I want to use sp_executesql to set @email_body for sp_send_dbmail

Why?

Previously I was concatenating parameter directly to the @email_body which is prone to SQL Injection. like this

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

SQL injection is possible as we don't have control over input.

What have I done?

so above was just an example my query looks closely like this

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

But nothing gets attached in @email_body and mail comes empty. It works fine when I run simple query like this with 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;

Thanks in advance for your help :)

Upvotes: 0

Views: 344

Answers (1)

Vidhi Shah
Vidhi Shah

Reputation: 23

Found The problem, It was caused as one of the parameter was Null, which caused @emailbody to be null. Fixed it by ISNULL function.

Upvotes: 0

Related Questions