A Simple Introduction to Dynamic SQL

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  February 13, 2018 at 12:00 pm

  #1979636

  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

  SSCertifiable

  Points: 6613

  More actions

  February 13, 2018 at 1:02 pm

  #1979642

  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