A Simple Introduction to Dynamic SQL

  • Paulo de Jesus

    Default port

    Points: 1402

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

  • julian.fletcher

    SSCrazy

    Points: 2623

    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: 1402

    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

    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: 1004446

    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

    Say Hey Kid

    Points: 712

    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

    SSCrazy Eights

    Points: 9947

    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: 994843

    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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • rchantler

    SSCrazy

    Points: 2092

    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: 6357

    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: 1402

    Thanks everyone for the feedback and constructive comments.

  • Chris Hurlbut

    SSCarpal Tunnel

    Points: 4052

       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

    One Orange Chip

    Points: 29945

    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

    SSCertifiable

    Points: 6932

    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: 6520

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

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

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