@param nvarchar(Max)

DECLARE @Output nvarchar(Max) = 
N’Select ‘ + @param

Select @output

Intended Use

exec sp_test ‘5’

Returns “select 5”

Malicious Use

exec sp_test ‘5; drop database’

Returns(would be safe): “select 5; drop database“


Returns(not safe): “select 5” ...but also actually dropping the database

MS SQL Server

  • select 5 will not be so usefull... is there a part of your query missing here ? Aug 26 '20 at 15:50
  • 1
    Have you tried to run the proc for the malicious use case? If not, try it on a test system (and be sure you have a backup because you'll need it if the proc is run by a privileged account).
    – Dan Guzman
    Aug 26 '20 at 15:52

All your stored procedure is doing is to put “select” in front of whatever is passed in. It doesn’t run anything.

But presumably you’re going to then ask something else to run whatever comes back, and that could be unsafe. If you’re wanting a very dynamic querying environment, then read what Erland Sommarskog has to say on the matter at http://www.sommarskog.se/dynamic_sql.html

  • Thanks for the answer Rob! My account is too new to up-vote it. I figured that a select SP couldn't change it's query plan, but wanted to double-check. You are correct in extrapolating that a 2nd account is used to execute the dynamic SQL returned by this SP. That second account only has GRANT SELECT ON OBJECT:: [ReportDataTable] TO [Account2]
    – Donnie
    Aug 26 '20 at 21:33
  • You should be able accept the answer it though...
    – Rob Farley
    Aug 27 '20 at 13:10

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.