0
CREATE PROCEDURE [sp_Test] (
     @param nvarchar(Max)
) AS BEGIN

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

Select @output
Return

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“

—-OR—-

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 ? – Dominique Boucher Aug 26 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 at 15:52
1

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

| improve this answer | |
  • 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 at 21:33
  • You should be able accept the answer it though... – Rob Farley Aug 27 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.