-2
DECLARE @TSQL NVARCHAR(MAX) = N'SELECT 1, ''One''';

CREATE TABLE #ThisIsATable (Id INT, VAL NVARCHAR(10));

INSERT INTO #ThisIsATable
EXEC [dbo].[sp_executesql] @TSQL ;

Is it safe to have input param like @TSQL to a stored procedure and use it in this manner?

3

This is not a safe way to do it.

You can run this quick demo to see what could go wrong

create database demo
Go

use demo
Go

Create table SomeVeryImportantData (id int, txt varchar(30));
insert into SomeVeryImportantData values (1,'some txt');

DECLARE @TSQL NVARCHAR(MAX) = N'truncate table SomeVeryImportantData;'

CREATE TABLE #ThisIsATable (Id INT, VAL NVARCHAR(10));

INSERT INTO #ThisIsATable
EXEC [dbo].[sp_executesql] @TSQL ;

select * from SomeVeryImportantData;

drop table #ThisIsATable

You may want to do something like this:

declare @param1 int;
declare @param2 varchar(30);

set @param1=1;
set @param2='one';

DECLARE @TSQL NVARCHAR(MAX) = N'SELECT @param1, @param2';

CREATE TABLE #ThisIsATable (Id INT, VAL NVARCHAR(10));

INSERT INTO #ThisIsATable
EXEC [dbo].[sp_executesql] @TSQL, N'@param1 int, @param2 varchar(30)', @param1=@param1, @param2=@param2

select * from #ThisIsATable;

drop table #ThisIsATable

| improve this answer | |
  • Thank you for a clear example and a suggestion. It's strange that sql is not throwing a syntax error in the first example and proceeds to run unrelated/syntactically-incorrect query for INSERT statement. Cheers mate! – user1569220 Sep 9 at 15:25
  • SQL doesn't know before the execution what will be the output of the "Exec" so it cannot throw a syntax error. In this specific example, the sp_executeSQL does not return anything but this is not something SQL consider as "invalid". If you do a "insert into tableX select 1 from sys.databases where name = 'does not exist' ". It will execute and will insert "nothing" into the table. Same thing happen in the previous example. The "exec" is executed, it returned nothing so nothing got inserted... but it still executed ;) – Dominique Boucher Sep 9 at 17:08
2

I think I've seen this question elsewhere, and I probably asked for more info. And I'm doing it again. What is your purpose with this? Why are you using dynamic SQL? What part of your SQL is dynamic, and what part of the input can be modified by the evil criminal?

Without such details, all we can do is propose something, and then somebody else can counter that proposal by injecting something, just like I'll do below with Dominique's example (with no critique intended, again difficult to be precise with so little info.

So please explain why use dynamic SQL and why not just have two parameters to the stored procedure (one for each value to be used in the VALUES clause for the INSERT statement).

Anyhow, here is the proposed answer modified with an injected TRUNCATE TABLE:

DROP TABLE IF EXISTS SomeVeryImportantData
DROP TABLE IF EXISTS #ThisIsATable

CREATE TABLE SomeVeryImportantData (id int, txt varchar(30));
INSERT INTO SomeVeryImportantData VALUES (1,'some important txt');

DECLARE @param1 int;
DECLARE @param2 varchar(30);

SET @param1=1;
set @param2='one';

DECLARE @TSQL nvarchar(MAX) = N'SELECT @param1, @param2 truncate table SomeVeryImportantData';

CREATE TABLE #ThisIsATable (Id INT, VAL NVARCHAR(10));

INSERT INTO #ThisIsATable
EXEC [dbo].[sp_executesql] @TSQL, N'@param1 int, @param2 varchar(30)', @param1=@param1, @param2=@param2

SELECT * FROM SomeVeryImportantData;
--Rows removed

SELECT * FROM #ThisIsATable;
| improve this answer | |
  • In my exemple, @TSQL was not used as a variable/input and should not be changeable ;) But I got your point, more info is always usefull. – Dominique Boucher Sep 9 at 17:46
  • 1
    Understood Dominique. I just wanted to post something to emphasize that we do need a more realistic case, or we risk ending leaving holes because lack of details. :) – Tibor Karaszi Sep 9 at 19:15

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.