• Dynamic SQL- Intro

    November 5, 2017

    In the world of SQL Server database development, there is an incredibly powerful tool that is usually
    over relied on, or not relied on at all. This tool is called Dynamic SQL.

    For the purposes of clarity, I will call the process of application code dynamically generating SQL strings as ‘ad-hoc SQL’ while referring to essentially the same process happening within a stored procedure as ‘dynamic SQL’. Yes, they are similar, and in my opinion there are ideal places for both. However, it’s been my experience that applications are often written entirely under one approach or the other, while the best approach may be somewhere in the middle.

    What is Dynamic SQL? 
    A simplified explanation would be that it is building a string and then executing that string. Keep in mind that I’m referring to building and executing these strings within a stored procedure.

    A normal SELECT statement might look something like this:

    SELECT * FROM Person.Person

    While a Dynamic SELECT statement would look something like this (Using EXEC)

    declare @SQLStatement nvarchar(max)

set @SQLStatement = 'SELECT * FROM Person.Person' 

EXEC (@SQLStatement)

    Or more like this (Using sp_executeSQL)

    declare @SQLStatement nvarchar(max)

set @SQLStatement = 'SELECT * FROM Person.Person' 

EXECUTE sp_executeSQL @SQLStatement

    Minimizing the Threat
    People are often afraid of Dynamic SQL and this fear is usually tied to security. Being able to execute a string could lead to a malicious string being executed, and this string could contain instructions to DROP a table or to display important internal information that should not be getting exposed.

    There are several things that can be done to minimize the threat potential when using Dynamic SQL.

    These things include

    1) using sp_executeSQL instead of EXEC. 
    sp_executeSQL is  a stored procedure with built in SQL injection protections. Parameters are where more of the threat lies, so taking care to make sure that your parameters don’t contain things that could harm your system is something that needs to be done.

    Here is an example where a parameter is in play. The parameter @LastName gets passed in through the query string, but also as a parameter to the sp_executeSQL call.

    declare @SQLStatement nvarchar(max),
        @LastName nvarchar(100),
 @Parameters nvarchar(max)

set @LastName = 'Walters'
set @Parameters = '@LastName nvarchar(100)'

set @SQLStatement = 'SELECT * FROM Person.Person WHERE LastName = @LastName'

EXECUTE sp_executeSQL 
  @SQLStatement,
  @Parameters,
  @LastName

    2) Another thing you can do to minimize risk would include setting up a login-less user account to execute sp_executeSQL with.

    3) Make sure the application code does not allow unsanitized user inputs to make it through to the database. Security is about layering after all, and the application is one of the layers!

    Read More