SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Simple Introduction to Dynamic SQL


A Simple Introduction to Dynamic SQL

Author
Message
Paulo de Jesus
Paulo de Jesus
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 Visits: 967
Comments posted to this topic are about the item A Simple Introduction to Dynamic SQL
Julian Fletcher
Julian Fletcher
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1490 Visits: 1134
You quite rightly say

When placing your Dynamic SQL code into production (typically in stored procedures), be careful about concatenating alphanumeric parameters directly because of SQL injection.


but a few lines previously, you've done exactly that, ending up with a

EXEC  (@SQL)


Shouldn't you be recommending the use of sp_executesql from the outset?
Paulo de Jesus
Paulo de Jesus
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 Visits: 967
Hi Julian, I was trying to focus more on the basic principals of how sql statements are created dynamically.
But you make a very good point, I should probably have nudged the readers towards using sp_executesql from the start.

For those of you reading this, here is a link to what Julian is referring to:
https://blogs.msdn.microsoft.com/turgays/2013/09/17/exec-vs-sp_executesql/
ggrewe
ggrewe
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 49
Instead of escaping the single tick, I find it more straight forward to use QUOTENAME:

SELECT @Result = 'Total of ' + QUOTENAME(CAST(@NoOfColumns AS VARCHAR), CHAR(39)) + ' column(s)';

PRINT @Result;

or use a quote;

SELECT @Result = 'Total of "' + CAST(@NoOfColumns AS VARCHAR) + '" column(s)';

PRINT @Result;
GilaMonster
GilaMonster
SSC Guru
SSC Guru (588K reputation)SSC Guru (588K reputation)SSC Guru (588K reputation)SSC Guru (588K reputation)SSC Guru (588K reputation)SSC Guru (588K reputation)SSC Guru (588K reputation)SSC Guru (588K reputation)

Group: General Forum Members
Points: 588826 Visits: 48016
julian.fletcher - Monday, February 12, 2018 2:16 AM
You quite rightly say

When placing your Dynamic SQL code into production (typically in stored procedures), be careful about concatenating alphanumeric parameters directly because of SQL injection.


but a few lines previously, you've done exactly that, ending up with a

EXEC  (@SQL)


Shouldn't you be recommending the use of sp_executesql from the outset?


Also, there's nothing about sp_executesql that makes it immune to SQL injection. sp_executesql allows for dynamic SQL to be parameterised, but not everything can be parameterised.

Specifically database name can't.
SELECT @SQL ='SELECT COUNT(1) ' +'FROM [' + @DBName + '].[dbo].[CommonTable] ' +'WHERE [InsertDate] = ''' + CAST(@Date AS VARCHAR) + ''''


Hence, without some whitelisting, that will still be vulnerable, even if run with sp_executesql.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Peter Heller
Peter Heller
Old Hand
Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)

Group: General Forum Members
Points: 398 Visits: 146
I would have used the concat function SQL Server 2012 or better, eliminate the cast by letting concat handle the char(10) cast and use sysdatetime() instead of Getdate().


declare @DBName as varchar(20);
set @DBName = 'Branch1';
declare @Date as date;
set @Date = sysdatetime() --not Getdate();
declare @SQL as varchar(2000);

select @SQL
= 'SELECT COUNT(1) ' + 'FROM [' + @DBName + '].[dbo].[CommonTable] ' + 'WHERE [InsertDate] = '''
+ cast(@Date as char(10)) + '''';
print @SQL;

select @SQL
= concat('SELECT COUNT(1) FROM [', @DBName, '].[dbo].[CommonTable] WHERE [InsertDate] = ', quotename(@Date, ''''));

print @SQL;
EXEC (@SQL)

Stan Kulp-439977
Stan Kulp-439977
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6013 Visits: 1184
Nice article. Dynamic SQL is one of those things you don't use very often, and a simple article like this is useful for refreshing your memory on the basic principle.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (542K reputation)SSC Guru (542K reputation)SSC Guru (542K reputation)SSC Guru (542K reputation)SSC Guru (542K reputation)SSC Guru (542K reputation)SSC Guru (542K reputation)SSC Guru (542K reputation)

Group: General Forum Members
Points: 542214 Visits: 44626
Peter Heller - Monday, February 12, 2018 5:42 AM
I would have used the concat function SQL Server 2012 or better, eliminate the cast by letting concat handle the char(10) cast and use sysdatetime() instead of Getdate().


declare @DBName as varchar(20);
set @DBName = 'Branch1';
declare @Date as date;
set @Date = sysdatetime() --not Getdate();
declare @SQL as varchar(2000);

select @SQL
= 'SELECT COUNT(1) ' + 'FROM [' + @DBName + '].[dbo].[CommonTable] ' + 'WHERE [InsertDate] = '''
+ cast(@Date as char(10)) + '''';
print @SQL;

select @SQL
= concat('SELECT COUNT(1) FROM [', @DBName, '].[dbo].[CommonTable] WHERE [InsertDate] = ', quotename(@Date, ''''));

print @SQL;
EXEC (@SQL)


SysDateTime() returns more bytes, which isn't necessary here because you're dumping the result to a variable with the DATE datatype.

As a bit of a sidebar and , SysDateTime() is relatively crippled because it returns a DATETIME2() datatype compared to GETDATE() which uses the powerful DATETIME datatype. DATETIME supports incredibly easy to use direct date/time math for period calculations (which is in the ISO standards) where DATETIME2() does not (at least not in SQL Server because they screwed it up). If you use it to support supposed "portable code", true portability is a myth and can't actually be accomplished to any great degree.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
rchantler
rchantler
SSC Eights!
SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)

Group: General Forum Members
Points: 836 Visits: 700
In our shop we have both professional SQL programmers and non professional SQL users. The rampant use of dynamic SQL by the non-professionals is one of the most common sources of poor performance. We handle this (but not always well) by isolating the non-professionals to their own server.
jonathan.crawford
jonathan.crawford
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1689 Visits: 552
I second Julian's recommendation, as someone who wants to learn, I don't just want to know that something is possible if it's a bad idea, I want to know that "some people recommend this <code></code> but that is a bad idea because of XYZ, and the right way to do it is ZYX"

The point of learning from sites like this is standing on the shoulders of those who have come before, so I can get farther faster.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
Paulo de Jesus
Paulo de Jesus
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 Visits: 967
Thanks everyone for the feedback and constructive comments.
Chris Hurlbut
Chris Hurlbut
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2760 Visits: 592
DECLARE @crlf CHAR(4)
DECLARE @v_SQL varchar(MAX) = ''
SET @crlf = CHAR(10) + CHAR(13)

SELECT @v_SQL = @v_SQL + 'DELETE FROM dbo.Some_Table' + ' ' + @crlf --can add carriage return line feed for real complex sql, makes it easier to debug
SELECT @v_SQL = @v_SQL + 'WHERE Person = ' + CHAR(39) + 'JONES' + CHAR(39) --use char(39) instead of ''', easier to read especially if you are doint this alot

PRINT @v_SQL
webrunner
webrunner
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17852 Visits: 4178
Thanks for the article. My only suggestion would be to move the SQL injection warning into a prominent box at the top of the article. Many readers might jump right into using dynamic SQL without taking into account the security implications.

And maybe also add a link to "The Curse and Blessings of Dynamic SQL," by Erland Sommarskog:
http://www.sommarskog.se/dynamic_sql.html

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Xedni
Xedni
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4233 Visits: 770
webrunner - Monday, February 12, 2018 12:40 PM
Thanks for the article. My only suggestion would be to move the SQL injection warning into a prominent box at the top of the article. Many readers might jump right into using dynamic SQL without taking into account the security implications.

I agree with this sentiment. This is a good introduction to the existence of dynamic SQL, and there's not really a great, gentle way to properly and succinctly introduce Dynamic SQL. DSQL is like a dangerous weapon; one you only want the most skilled people handling. But if you don't have those skilled people, where do you start them? Certainly you've got to start somewhere. But I'm of the opinion that it's use should come with a big sign saying "BEWARE OF THE LEOPARD".


Executive Junior Cowboy Developer, Esq.
gbritton1
gbritton1
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3613 Visits: 976
Chris Hurlbut - Monday, February 12, 2018 12:10 PM
DECLARE @crlf CHAR(4)
DECLARE @v_SQL varchar(MAX) = ''
SET @crlf = CHAR(10) + CHAR(13)

SELECT @v_SQL = @v_SQL + 'DELETE FROM dbo.Some_Table' + ' ' + @crlf --can add carriage return line feed for real complex sql, makes it easier to debug
SELECT @v_SQL = @v_SQL + 'WHERE Person = ' + CHAR(39) + 'JONES' + CHAR(39) --use char(39) instead of ''', easier to read especially if you are doint this alot

PRINT @v_SQL

DECLARE @v_SQL Nvarchar(MAX) -- Should be Nvarchar
DECLARE @qt Nchar(1) = NCHAR(39) -- declare the quote character as a variable rather than using the function later.
SET @v_SQL = CONCAT( -- Using CONCAT can make it easier to write and read, especially for casts
N'DELETE FROM dbo.Some_Table', @crlf,
N'WHERE Person = ' , @qt, N'JONES', @qt,
N';'
)

Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)

Group: General Forum Members
Points: 102071 Visits: 21409
One simply does not introduces Dynamic SQL
without a section on how to prevent SQL Injection.



The basic changes are simple enough to include them in the article and promote good code from the beginning. Parametrize your dynamic strings and validate securable (objects, schemas, databases) names.

DECLARE @DBName AS VARCHAR(20);
SET @DBName = 'Test';
DECLARE @Date AS DATE;
SET @Date = GETDATE();
DECLARE @SQL AS NVARCHAR(2000);

SELECT @SQL =
N'SELECT COUNT(1) ' + CHAR(13)
+ N'FROM ' + QUOTENAME(d.name) + N'.[dbo].[CommonTable] ' + CHAR(13)
+ N'WHERE [InsertDate] = @iDate;'
FROM sys.databases AS d
WHERE d.name = @DBName;

PRINT @SQL;
EXEC sp_executesql @SQL, N'@iDate date', @iDate = @Date;



Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
jonathan.crawford
jonathan.crawford
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1689 Visits: 552
You Shall Not Pass..............un-parameterized user input directly into strings without using sp_executesql properly!

Little known fact, if Sauron had only used a backup for his One Ring, it would have been safe from Hobbit attack.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search