A Simple Introduction to Dynamic SQL

Question

A Simple Introduction to Dynamic SQL

Paulo de Jesus

Default port

Points: 1403
More actions
February 10, 2018 at 6:28 pm

#410232

Comments posted to this topic are about the item A Simple Introduction to Dynamic SQL

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

julian.fletcher SSCrazy Points: 2959 More actions · Answer 1

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 Default port Points: 1403 More actions · Answer 2

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 SSC Journeyman Points: 87 More actions · Answer 3

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;

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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 SSC Eights! Points: 999 More actions · Answer 5

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 SSChampion Points: 10062 More actions · Answer 6

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 SSC Guru Points: 1004312 More actions · Answer 7

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rchantler SSCrazy Points: 2128 More actions · Answer 8

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 SSCertifiable Points: 6612 More actions · Answer 9

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 Default port Points: 1403 More actions · Answer 10

Thanks everyone for the feedback and constructive comments.

Chris Hurlbut SSCarpal Tunnel Points: 4162 More actions · Answer 11

   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 SSC-Dedicated Points: 31858 More actions · Answer 12

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 asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Xedni SSCertifiable Points: 6951 More actions · Answer 13

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.[/url]

gbritton1 SSCertifiable Points: 6522 More actions · Answer 14

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';' )