Dynamic SQL, now what?

Many times as DBA or Developers inherit a database that is using a lot of Dynamic SQL, that nifty tool that developers love and most DBA hate. Dynamic SQL scripts are a way of composing a query by concatenating values and logic into a dynamically generated query. During my career every time I have found dynamic SQL in a procedure, it has been non-parametized, which can represent a security risk and can lead to SQL injection vulnerabilities.

Let’s take a look at the typical non-parametized queries that you will find often in many database systems.

DECLARE @Query      NVARCHAR (MAX)
      , @PersonType VARCHAR (10) = 'EM';
SET @Query = N'SELECT FirstName, LastName 
		    FROM Person.Person 
		   WHERE PersonType = ''' + @PersonType + N'''';
EXEC sp_executesql @Query;

The query above can be subject to SQL injection attacks because there is no parameter validation. By concatenating values, end users my pass SQL attacks as parameter values and cause harm in your database. Let’s be clear, concatenation is part of using dynamic SQL, but it should not be used to attach parameters to be used as predicates. Let’s take a look at a little more complicated dynamic sql statement without parameters.

DECLARE @Query          NVARCHAR (MAX)
      , @PersonType     VARCHAR (10) = 'EM'
      , @EmailPromotion BIT          = 0
      , @ModifiedDate   INT          = 2009;
SET @Query = N'SELECT FirstName
					, LastName
					, EmailPromotion
					, ModifiedDate
			   FROM Person.Person 
			   WHERE PersonType = ''' + @PersonType + N'''
				   AND EmailPromotion =  ' + CAST(@EmailPromotion AS VARCHAR (10)) + N' 
				   AND Year(ModifiedDate) = ' + CAST(@ModifiedDate AS VARCHAR (10));

EXEC sp_executesql @Query;

The query above requires casting INT and BIT columns as strings in order to concatenate their values into the query string, this makes queries hard to read and very difficult to maintain and validate for accuracy. Now let’s convert this same last query into a properly parametized query.

DECLARE @QueryString NVARCHAR (MAX)
      , @Parameters  NVARCHAR (1000) = N'@PersonType Varchar(10), @EmailPromotion BIT, @ModifiedDateYear INT';

SET @QueryString = N'SELECT   FirstName
							, LastName
							, EmailPromotion
							, ModifiedDate
					FROM Person.Person 
					WHERE PersonType = @PersonType
						AND EmailPromotion =  @EmailPromotion 
						AND Year(ModifiedDate) = @ModifiedDateYear';

EXECUTE sp_executesql @QueryString
                    , @Parameters
                    , @PersonType = 'EM'
                    , @EmailPromotion = 0
                    , @ModifiedDateYear = 2009;

The query above now contains parameters that will enforce data type validations and contains less concatenating actions. The sp_executesql stored procedure tokenizes the query string and replaces the value of variables declared in the query string with variables specified on the parameters variable.

Wrap Up

Dynamic SQL and the sp_executesql stored procedure is a great way of generating queries based on run-time values or a pre-defined logic within you application. Commonly misused this tool has grown a bad reputation, but if used well, it can help you accomplish very complicated tasks dynamically. Using a parameter based syntax on dynamic sql will help you accomplish complicated queries easier and safer minimizing the risk foot print for SQL injection and making your sql code much readable. Use wisely!