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.
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:
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.
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.)