Dynamic Stored Procedure Execution in SQL Server

STAY IN TOUCH

Get New posts delivered straight to your inbox

  • Madeira Team

Dynamic Stored Procedure Execution in SQL Server

In my previous blog post: Suggestion: “USE” Keyword with Linked Servers, I talked about the suggestion I’ve posted on Microsoft Connect which allows easy execution of dynamic code on linked servers.

I’ve received a surprising reply from Umachandar of the SQL Programmability team, revealing to me an amazing feature that I wasn’t aware of until now.

Here’s the sample that he wrote:

Transact-SQL

DECLARE @ServerName VARCHAR(200), @Statement NVARCHAR(MAX), @Parameters NVARCHAR(MAX) SET @ServerName = 'SQLINS1'; SET @Parameters = N'@Path NVARCHAR(4000)'; SET @Statement = N'BACKUP DATABASE [MyDB] TO DISK = @Path' DECLARE @sp nvarchar(1000) = QUOTENAME(@ServerName) + N'.[MyDB].sys.sp_executesql' EXEC @sp @Statement, @Parameters, @Path

1

2

3

4

5

6

7

8

9

DECLARE

@ServerName VARCHAR(200),

@Statement NVARCHAR(MAX),

@Parameters NVARCHAR(MAX)

SET @ServerName = 'SQLINS1';

SET @Parameters = N'@Path NVARCHAR(4000)';

SET @Statement = N'BACKUP DATABASE [MyDB] TO DISK = @Path'

DECLARE @sp nvarchar(1000) = QUOTENAME(@ServerName) + N'.[MyDB].sys.sp_executesql'

EXEC @sp @Statement, @Parameters, @Path

Apparently there’s a little-known method for executing stored procedures (this is from Books Online):

Transact-SQL

-- Execute a character string { EXEC | EXECUTE } ( { @string_variable | [ N ]'tsql_string' } [ + ...n ] ) [ AS { LOGIN | USER } = ' name ' ] [;]

1

2

3

4

5

-- Execute a character string

{ EXEC | EXECUTE }

( { @string_variable | [ N ]'tsql_string' } [ + ...n ] )

[ AS { LOGIN | USER } = ' name ' ]

[;]

In SQL Server Books Online it’s not clear right away that you can actually use this method to execute stored procedures which you’ve built their name dynamically, and even pass on parameters to them like any other stored procedure.

Though there is a tiny little example that shows you can actually do it:

Transact-SQL

DECLARE @proc_name varchar(30); SET @proc_name = 'sys.sp_who'; EXEC @proc_name;

1

2

3

DECLARE @proc_name varchar(30);

SET @proc_name = 'sys.sp_who';

EXEC @proc_name;

Yeah, not very informative, and very easy to miss.

This feature grants us amazing power and protection from SQL Injection.

We can use this feature for:

Executing dynamic code at linked servers where the linked server name is dynamic as well (my first example).

Executing a stored procedure with a dynamic name provided externally.

Executing stored procedures on databases with a dynamic name.

And all the while we pass the parameters without SQL Injection!

More than that, the dynamic name itself is protected from SQL Injection because SQL explicitly looks for a stored procedure with the provided name, and doesn’t simply execute any command given:

Transact-SQL

-- This will obviously drop the table: CREATE TABLE SQLInjectionTest1 (col1 INT) DECLARE @SQL NVARCHAR(MAX) SET @SQL = N'DROP TABLE SQLInjectionTest1' EXECUTE (@SQL) /* -- result: Command(s) completed successfully. */ GO -- This will NOT drop the table: CREATE TABLE SQLInjectionTest2 (col1 INT) DECLARE @SQL NVARCHAR(MAX) SET @SQL = N'DROP TABLE SQLInjectionTest2' EXECUTE @SQL /* -- result: Msg 2812, Level 16, State 62, Line 6 Could not find stored procedure 'DROP TABLE SQLInjectionTest2'. */ GO DROP TABLE SQLInjectionTest2

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

-- This will obviously drop the table:

CREATE TABLE SQLInjectionTest1

(col1 INT)

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = N'DROP TABLE SQLInjectionTest1'

EXECUTE (@SQL)

/* -- result:

Command(s) completed successfully.

*/

GO

-- This will NOT drop the table:

CREATE TABLE SQLInjectionTest2

(col1 INT)

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = N'DROP TABLE SQLInjectionTest2'

EXECUTE @SQL

/* -- result:

Msg 2812, Level 16, State 62, Line 6

Could not find stored procedure 'DROP TABLE SQLInjectionTest2'.

*/

GO

DROP TABLE SQLInjectionTest2

I’m surprised this feature is not more widely known because it can be extremely useful at times.

I’m really curious, were you familiar with this feature before? Let me know in the comments below!

#storedprocedures #dynamicsql #sqlinjection #spexecutesql

JOIN OUR MAILING LIST

CONTACT US

 3 Rapaport St. Kfar Saba, Israel

 +972-9-7400101

  • Google+ - White Circle
  • Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle