4 further example listed
source | link

EDIT: In response to your follow-up question in the comments, you can further lock this down by wrapping a call to sp_send_dbmail within another stored procedure where the @query parameter is statically defined or more tightly controlled. This new SP can then take advantage of impersonation (ref1, ref2) and you would then only need to grant EXECUTE rights to explicit users that you want to expose this functionality to. This should lock things down pretty well and depending on how you limit the use of the @query parameter being passed to sp_send_dbmail, you may be able to completely eliminate the potential for SQL Injection occurring from normal accounts. Again, any elevated account that can directly call sp_send_dbmail will still have the ability to extort the potential for SQL Injection, but this approach will lock it down about as good as you can.

Because I typed this up hastily, here's also an example in hopes it shows the approach a bit better:

-- CREATE THIS PROCEDURE USING AN ACCOUNT THAT HAS SUFFICIENT ACCESS TO THE DATA AND SP_SEND_DBMAIL
CREATE PROCEDURE [dbo].[limited_sp_send_dbmail]
      @messageBody NVARCHAR(MAX)
    , @recipientList VARCHAR(MAX)
    , @reply_to_address VARCHAR(MAX)
    , @subject_line NVARCHAR(255)
WITH EXECUTE AS SELF
AS
    -- does not allow the usage of @query parameter
    EXEC msdb.dbo.sp_send_dbmail @profile_name='MyDBMailProfile'
        , @recipients = @recipientList
        , @reply_to = @reply_to_address
        , @subject = @subject_line
        , @body = @messageBody
GO

EXECUTE [limited_sp_send_dbmail] @messageBody = N'Hello World!', @recipientList = 'john.eisbrener@Contoso.com', @reply_to_address = 'john.eisbrener@Contoso.com', @subject_line = N'Hello World!'
GO

Mail (Id: 36757) queued.

EDIT: In response to your follow-up question in the comments, you can further lock this down by wrapping a call to sp_send_dbmail within another stored procedure where the @query parameter is statically defined or more tightly controlled. This new SP can then take advantage of impersonation (ref1, ref2) and you would then only need to grant EXECUTE rights to explicit users that you want to expose this functionality to. This should lock things down pretty well and depending on how you limit the use of the @query parameter being passed to sp_send_dbmail, you may be able to completely eliminate the potential for SQL Injection occurring from normal accounts. Again, any elevated account that can directly call sp_send_dbmail will still have the ability to extort the potential for SQL Injection, but this approach will lock it down about as good as you can.

Because I typed this up hastily, here's also an example in hopes it shows the approach a bit better:

-- CREATE THIS PROCEDURE USING AN ACCOUNT THAT HAS SUFFICIENT ACCESS TO THE DATA AND SP_SEND_DBMAIL
CREATE PROCEDURE [dbo].[limited_sp_send_dbmail]
      @messageBody NVARCHAR(MAX)
    , @recipientList VARCHAR(MAX)
    , @reply_to_address VARCHAR(MAX)
    , @subject_line NVARCHAR(255)
WITH EXECUTE AS SELF
AS
    -- does not allow the usage of @query parameter
    EXEC msdb.dbo.sp_send_dbmail @profile_name='MyDBMailProfile'
        , @recipients = @recipientList
        , @reply_to = @reply_to_address
        , @subject = @subject_line
        , @body = @messageBody
GO

EXECUTE [limited_sp_send_dbmail] @messageBody = N'Hello World!', @recipientList = 'john.eisbrener@Contoso.com', @reply_to_address = 'john.eisbrener@Contoso.com', @subject_line = N'Hello World!'
GO

Mail (Id: 36757) queued.
3 added 917 characters in body
source | link

You can SQL Inject it without much issue, sadly. Here's a simple test I just ran with an elevated account andwith the results included:

USE [master]
GO

DECLARE @msgbody NVARCHAR(4000)
SET @msgbody = N'Test to see if this SP can suffer from SQL Injection' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

EXEC msdb.dbo.sp_send_dbmail @profile_name='MyDBMailProfile'
    , @recipients = 'john.eisbrener@Contoso.com'
    , @reply_to = 'john.eisbrener@Contoso.com'
    , @subject = 'SQL Injection Test'
    , @query = 'CREATE TABLE MyDB.dbo.johnSITest
(
      ID INT IDENTITY(1,1)
    , VAL VARCHAR(50)
)

INSERT INTO MyDB.dbo.johnSITest
    (VAL)
VALUES
    (''yes''), (''you''), (''can'')'
    , @body = @msgbody
GO


Mail (Id: 36756) queued.



SELECT *
FROM MyDB.dbo.johnSITest
GO


ID          VAL
----------- --------------------------------------------------
1           yes
2           you
3           can

(3 row(s) affected)




DROP TABLE MyDB.dbo.johnSITest
GO

Now if the account executing it doesn't have elevated rights, this should limit the exposure of the SP. The minimum permissions needed for an account to execute this SP are as follows:

Permissions

Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message.

The Principle of least privilege should always dictate your security approach, but again, yes it can be SQL Injected much like anything that ingests Dynamic SQL.

You can SQL Inject it without much issue, sadly. Here's a simple test I just ran with an elevated account and the results included:

USE [master]
GO

DECLARE @msgbody NVARCHAR(4000)
SET @msgbody = N'Test to see if this SP can suffer from SQL Injection' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

EXEC msdb.dbo.sp_send_dbmail @profile_name='MyDBMailProfile'
    , @recipients = 'john.eisbrener@Contoso.com'
    , @reply_to = 'john.eisbrener@Contoso.com'
    , @subject = 'SQL Injection Test'
    , @query = 'CREATE TABLE MyDB.dbo.johnSITest
(
      ID INT IDENTITY(1,1)
    , VAL VARCHAR(50)
)

INSERT INTO MyDB.dbo.johnSITest
    (VAL)
VALUES
    (''yes''), (''you''), (''can'')'
    , @body = @msgbody
GO


Mail (Id: 36756) queued.



SELECT *
FROM MyDB.dbo.johnSITest
GO


ID          VAL
----------- --------------------------------------------------
1           yes
2           you
3           can

(3 row(s) affected)




DROP TABLE MyDB.dbo.johnSITest
GO

Now if the account executing it doesn't have elevated rights, this should limit the exposure of the SP. The minimum permissions needed for an account to execute this SP are as follows:

Permissions

Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message.

The Principle of least privilege should always dictate your security approach, but again, yes it can be SQL Injected much like anything that ingests Dynamic SQL.

You can SQL Inject it without much issue, sadly. Here's a simple test I just ran with an elevated account with the results included:

USE [master]
GO

DECLARE @msgbody NVARCHAR(4000)
SET @msgbody = N'Test to see if this SP can suffer from SQL Injection' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

EXEC msdb.dbo.sp_send_dbmail @profile_name='MyDBMailProfile'
    , @recipients = 'john.eisbrener@Contoso.com'
    , @reply_to = 'john.eisbrener@Contoso.com'
    , @subject = 'SQL Injection Test'
    , @query = 'CREATE TABLE MyDB.dbo.johnSITest
(
      ID INT IDENTITY(1,1)
    , VAL VARCHAR(50)
)

INSERT INTO MyDB.dbo.johnSITest
    (VAL)
VALUES
    (''yes''), (''you''), (''can'')'
    , @body = @msgbody
GO


Mail (Id: 36756) queued.



SELECT *
FROM MyDB.dbo.johnSITest
GO


ID          VAL
----------- --------------------------------------------------
1           yes
2           you
3           can

(3 row(s) affected)




DROP TABLE MyDB.dbo.johnSITest
GO

Now if the account executing it doesn't have elevated rights, this should limit the exposure of the SP. The minimum permissions needed for an account to execute this SP are as follows:

Permissions

Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message.

The Principle of least privilege should always dictate your security approach, but again, yes it can be SQL Injected much like anything that ingests Dynamic SQL.

2 added 917 characters in body
source | link

You can SQL Inject it without much issue, sadly. Here's a simple test I just ran with an elevated account and the results included:

USE [master]
GO

DECLARE @msgbody NVARCHAR(4000)
SET @msgbody = N'Test to see if this SP can suffer from SQL Injection' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

EXEC msdb.dbo.sp_send_dbmail @profile_name='MyDBMailProfile'
    , @recipients = 'john.eisbrener@Contoso.com'
    , @reply_to = 'john.eisbrener@Contoso.com'
    , @subject = 'SQL Injection Test'
    , @query = 'CREATE TABLE MyDB.dbo.johnSITest
(
      ID INT IDENTITY(1,1)
    , VAL VARCHAR(50)
)

INSERT INTO MyDB.dbo.johnSITest
    (VAL)
VALUES
    (''yes''), (''you''), (''can'')'
    , @body = @msgbody
GO


Mail (Id: 36756) queued.



SELECT *
FROM MyDB.dbo.johnSITest
GO


ID          VAL
----------- --------------------------------------------------
1           yes
2           you
3           can

(3 row(s) affected)




DROP TABLE MyDB.dbo.johnSITest
GO

Now if the account executing it doesn't have elevated rights, this should limit the exposure of the SP. The minimum permissions needed for an account to execute this SP are as follows:

Permissions

Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message.

The Principle of least privilege should always dictate your security approach, but again, yes it can be SQL Injected much like anything that ingests Dynamic SQL.

You can SQL Inject it without much issue, sadly. Here's a simple test I just ran with the results included:

USE [master]
GO

DECLARE @msgbody NVARCHAR(4000)
SET @msgbody = N'Test to see if this SP can suffer from SQL Injection' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

EXEC msdb.dbo.sp_send_dbmail @profile_name='MyDBMailProfile'
    , @recipients = 'john.eisbrener@Contoso.com'
    , @reply_to = 'john.eisbrener@Contoso.com'
    , @subject = 'SQL Injection Test'
    , @query = 'CREATE TABLE MyDB.dbo.johnSITest
(
      ID INT IDENTITY(1,1)
    , VAL VARCHAR(50)
)

INSERT INTO MyDB.dbo.johnSITest
    (VAL)
VALUES
    (''yes''), (''you''), (''can'')'
    , @body = @msgbody
GO


Mail (Id: 36756) queued.



SELECT *
FROM MyDB.dbo.johnSITest
GO


ID          VAL
----------- --------------------------------------------------
1           yes
2           you
3           can

(3 row(s) affected)




DROP TABLE MyDB.dbo.johnSITest
GO

You can SQL Inject it without much issue, sadly. Here's a simple test I just ran with an elevated account and the results included:

USE [master]
GO

DECLARE @msgbody NVARCHAR(4000)
SET @msgbody = N'Test to see if this SP can suffer from SQL Injection' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

EXEC msdb.dbo.sp_send_dbmail @profile_name='MyDBMailProfile'
    , @recipients = 'john.eisbrener@Contoso.com'
    , @reply_to = 'john.eisbrener@Contoso.com'
    , @subject = 'SQL Injection Test'
    , @query = 'CREATE TABLE MyDB.dbo.johnSITest
(
      ID INT IDENTITY(1,1)
    , VAL VARCHAR(50)
)

INSERT INTO MyDB.dbo.johnSITest
    (VAL)
VALUES
    (''yes''), (''you''), (''can'')'
    , @body = @msgbody
GO


Mail (Id: 36756) queued.



SELECT *
FROM MyDB.dbo.johnSITest
GO


ID          VAL
----------- --------------------------------------------------
1           yes
2           you
3           can

(3 row(s) affected)




DROP TABLE MyDB.dbo.johnSITest
GO

Now if the account executing it doesn't have elevated rights, this should limit the exposure of the SP. The minimum permissions needed for an account to execute this SP are as follows:

Permissions

Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message.

The Principle of least privilege should always dictate your security approach, but again, yes it can be SQL Injected much like anything that ingests Dynamic SQL.

1
source | link