5

I use this system stored procedure dbo.sp_send_dbmail to send email from my database, I want to know if this procedure is protect against SQL Injection?

And if you have some reference this will be util for me.

Thanks in advance.

5

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.

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.
  • may I limit the permissions of that procedure to avoid the creations of tables, updates, creates and deletes? – csuazo Jun 21 '17 at 16:10
  • 1
    @csuazo I've updated my answer to include a response to this question. Hope that helps. – John Eisbrener Jun 21 '17 at 17:16
  • I'm not sure what the injection is supposed to be here. Is it the fact that you executed DDL and DML in the query? The query is supposed to be arbitrary SQL, so it's no surprise that you can use it to run arbitrary SQL. It'd be an injection if you managed to run arbitrary SQL through the reply_to or something. – user2357112 Jun 21 '17 at 22:48
  • 1
    @user2357112 Effectively yes, the execution of DDL within what conceptually should only be a DML driven field shows that it's not restricted on any level and could be prone to SQL Injection attacks in the right scenarios. As for more exhaustive testing, I'd be interested in that myself, though I don't know how much more value that will provide for the OP. – John Eisbrener Jun 21 '17 at 23:19
2

Even if it does, you should always ASSUME that it doesn't.

That is to say: if you have an application that will be using it, you should create a procedure (wrapper) that calls sp_send_dbmail, and have the application call THAT procedure, not do a direct call to sp_send_dbmail.

This way you can eliminate any chance of injection in your wrapper, and also make sure that the right call is being made every time (null parameter checks, data type checks, etc.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.