Important security article related to dynamic SQL: How To: Protect From SQL Injection in ASP.NET

------------

-- Dynamic SQL QUICK SYNTAX

------------

USE AdventureWorks2008;

EXEC ('SELECT * FROM Sales.SalesOrderHeader')

DECLARE @DynamicSQL varchar(256); SET @DynamicSQL='SELECT * FROM Sales.SalesOrderHeader'

EXEC (@DynamicSQL)

GO

DECLARE @DynamicSQL varchar(256), @Table sysname;

SET @DynamicSQL='SELECT * FROM'; SET @Table = 'Sales.SalesOrderHeader'

SET @DynamicSQL = @DynamicSQL+' '+@Table

PRINT @DynamicSQL  -- for testing & debugging

EXEC (@DynamicSQL)

GO

-- Dynamic SQL for rowcount in all tables

DECLARE @DynamicSQL nvarchar(max), @Schema sysname, @Table sysname;

SET @DynamicSQL = ''

SELECT @DynamicSQL = @DynamicSQL + 'SELECT '''+QUOTENAME(TABLE_SCHEMA)+'.'+

  QUOTENAME(TABLE_NAME)+''''+

  '= COUNT(*) FROM '+ QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME) +';'

FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

PRINT @DynamicSQL -- test & debug

EXEC sp_executesql @DynamicSQL

-- Equivalent code using the undocumented sp_MSforeachtable

EXEC sp_MSforeachtable 'select ''?'', count(*) from ?'

------------

  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following dynamic SQL scripts demonstrate:

   1. dynamic SQL stored procedure

   2. dynamic SQL with OUTPUT parameter

   3. stored procedure with dynamic SQL WHILE loop

   4. dynamic SQL with using parent's #temptable

   5. dynamic SQL for dynamic PIVOT query

   6. dynamic stored procedure with output parameter

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- FIRST EXAMPLE - dynamic stored procedure for customer list

USE Northwind;

GO

-- DROP stored procedure if exists to make CREATE work

IF EXISTS ( SELECT *

                  FROM sys.objects

                  WHERE object_id = OBJECT_ID(N'[dbo].[CustomerListByState]')

                              AND TYPE IN (N'P',N'PC'))

DROP PROCEDURE [dbo].[CustomerListByState]

GO

-- Sproc (stored procedure) with dynamic SQL

CREATE PROCEDURE CustomerListByState

@States VARCHAR(128)

AS

BEGIN

      SET NOCOUNT ON

      DECLARE @SQL NVARCHAR(MAX) -- alternate nvarchar(1024)

      -- Dynamic query assembly with string concatenation

      SET @SQL = 'select Region, CustomerID, CompanyName, ContactName, Phone

      from Customers where Region IN (' + @States + ')' +

      ' order by Region, CompanyName'

      PRINT @SQL -- for testing & debugging

/* Assembled code

select Region, CustomerID, CompanyName, ContactName, Phone

from Customers where Region IN ('WA', 'OR', 'ID', 'CA')

order by Region, CompanyName

*/

      EXEC sp_executeSQL @SQL

END

GO

-- Execute dynamic stored procedure script

DECLARE @States VARCHAR(100)

SET @States = '''WA'', ''OR'', ''ID'', ''CA'''

EXEC CustomerListByState @States

GO

/* Results

*/

-- SECOND EXAMPLE - search names in Person.Person table

-- Dynamic SQL with input and output parameters

USE AdventureWorks2008;

DECLARE  @ParmDefinition NVARCHAR(1024) = N'@FirstLetterOfLastName char(1),

      @LastFirstNameOUT nvarchar(50) OUTPUT'

DECLARE @FirstLetter CHAR(1) = 'P', @LastFirstName NVARCHAR(50)

DECLARE @SQL NVARCHAR(MAX) = N'SELECT @LastFirstNameOUT = max(FirstName)

      FROM Person.Person'+CHAR(13)+

      'WHERE left(LastName,1) = @FirstLetterOfLastName'

PRINT @SQL+CHAR(13)     -- test & debug

PRINT @ParmDefinition    -- test & debug

EXECUTE sp_executeSQL

      @SQL,

      @ParmDefinition,

      @FirstLetterOfLastName = @FirstLetter,

      @LastFirstNameOUT=@LastFirstName OUTPUT

SELECT

  [Last First Name] = @LastFirstName,

  Legend='of last names starting with',

  Letter=@FirstLetter

GO

/* Results

Last First Name   Legend                        Letter

Zoe               of last names starting with   P

-- THIRD EXAMPLE - SPROC to enumerate all objects in databases

-- Return objects count in all databases on the server

-- Dynamic SQL stored procedure with cursor loop

-- QUOTENAME function is used to build valid identifiers

USE AdventureWorks;

GO

IF EXISTS (SELECT *

           FROM   sys.objects

           WHERE  object_id = OBJECT_ID(N'[dbo].[sprocObjectCountsInAllDBs]')

                  AND TYPE IN (N'P',N'PC'))

  DROP PROCEDURE [dbo].[sprocObjectCountsInAllDBs]

GO

CREATE PROC sprocObjectCountsInAllDBs

AS

  BEGIN

    DECLARE  @dbName      SYSNAME,

             @ObjectCount INT

    DECLARE  @SQL NVARCHAR(MAX)

    DECLARE  @DBObjectStats  TABLE(

                                   DBName    SYSNAME,

                                   DBObjects INT

                                   )

    DECLARE curAllDBs CURSOR  FOR

    SELECT   name

    FROM     MASTER.dbo.sysdatabases

    WHERE    name NOT IN ('master','tempdb','model','msdb')

    ORDER BY name

    OPEN curAllDBs

    FETCH  curAllDBs

    INTO @dbName

    WHILE (@@FETCH_STATUS = 0) -- Loop through all db-s

      BEGIN

        -- Build valid yet hard-wired SQL statement

        SET @SQL = 'select @dbObjects = count(*)' + char(13) + 'from ' +

                    QuoteName(@dbName) + '.dbo.sysobjects'

        PRINT @SQL -- Use it for debugging

        /*

                  select @dbObjects = count(*)

                  from [AdventureWorks].dbo.sysobjects

        */

        -- Dynamic call for query execution with output parameter(s)

        EXEC sp_executesql @SQL, N'@dbObjects int output', 

             @dbObjects = @ObjectCount output

        INSERT @DBObjectStats SELECT @dbName, @ObjectCount

        FETCH  curAllDBs

        INTO @dbName

      END -- while

    CLOSE curAllDBs

    DEALLOCATE curAllDBs

  -- Return results 

  SELECT * FROM @DBObjectStats ORDER BY DBName

  END

GO

-- Execute stored procedure

EXEC sprocObjectCountsInAllDBs

GO

/* Partial results

DBName                  DBObjects

AdventureWorks          604

AdventureWorks2008      646

AdventureWorksDW        151

AdventureWorksDW2008    164

AdventureWorksLT        158

AdventureWorksLT2008    158

*/

/* FOURTH EXAMPLE - automatic T-SQL code generation
        for datetime conversion from style 0 to 14 */

USE AdventureWorks2008;

DECLARE  @I INT = -1

DECLARE  @SQLDynamic NVARCHAR(1024)

-- Temporary table is used for data sharing between parent & child processes

-- This is the parent process; the child process is the dynamic SQL execution

CREATE TABLE #SQL (

  STYLE  INT,

  [SQL]  VARCHAR(256),

  Result VARCHAR(32))

-- Loop on @I from 0 to 13

WHILE (@I < 14)

  BEGIN

    SET @I += 1

    -- Store query and dynamic results in temporary table

    INSERT #SQL (STYLE, [SQL])

    SELECT @I,

           'SELECT ' + 'CONVERT(VARCHAR, GETDATE(), ' +

                        CONVERT(VARCHAR,@I) + ')'

    -- Build dynamic sql statement

    SET @SQLDynamic = 'UPDATE #SQL SET Result=(SELECT  CONVERT(VARCHAR,

        GETDATE(), ' + CONVERT(VARCHAR,@I) + ')) WHERE STYLE=' +

                       CONVERT(VARCHAR,@I)

    PRINT @SQLDynamic

    /*

    UPDATE #SQL SET Result=(SELECT  CONVERT(VARCHAR,

        GETDATE(), 0)) WHERE STYLE=0

    */

    EXEC sp_executeSQL  @SQLDynamic

  END

-- Return results from temporary table

SELECT * FROM   #SQL

DROP TABLE #SQL

GO

/* Partial results

STYLE SQL                                       Result

0     SELECT CONVERT(VARCHAR, GETDATE(), 0)     Mar 14 2009  6:10AM

1     SELECT CONVERT(VARCHAR, GETDATE(), 1)     03/14/09

2     SELECT CONVERT(VARCHAR, GETDATE(), 2)     09.03.14

*/

-- FIFTH EXAMPLE - dynamic pivot crosstab query

------------

-- T-SQL Dynamic Pivot Crosstab Report - Column header YYYY is dynamically assembled

------------

USE AdventureWorks

GO

DECLARE  @YearList  AS  TABLE(

                               YYYY INT    NOT NULL    PRIMARY KEY

                               )

DECLARE  @DynamicSQL  AS NVARCHAR(MAX)

INSERT INTO @YearList

SELECT DISTINCT YEAR(OrderDate)

FROM   Sales.SalesOrderHeader

DECLARE  @ReportColumnNames  AS NVARCHAR(MAX),

         @IterationYear      AS INT

SET @IterationYear = (SELECT MIN(YYYY)

                      FROM   @YearList)

SET @ReportColumnNames = N''

-- Assemble pivot list dynamically

WHILE (@IterationYear IS NOT NULL)

  BEGIN

    SET @ReportColumnNames = @ReportColumnNames + N',' + QUOTENAME(CAST(@IterationYear AS NVARCHAR(10)))

    SET @IterationYear = (SELECT MIN(YYYY)

                          FROM   @YearList

                          WHERE  YYYY > @IterationYear)

  END

SET @ReportColumnNames = SUBSTRING(@ReportColumnNames,2,LEN(@ReportColumnNames))

PRINT @ReportColumnNames

-- [2001],[2002],[2003],[2004]

SET @DynamicSQL = N'SELECT * FROM (SELECT [Store (Freight Summary)]=s.Name,    

YEAR(OrderDate) AS OrderYear, 

Freight = convert(money,convert(varchar, Freight))    

FROM Sales.SalesOrderHeader soh    

INNER JOIN Sales.Store s    

ON soh.CustomerID = s.CustomerID) as Header    

PIVOT (SUM(Freight)    FOR OrderYear

IN(' + @ReportColumnNames + N'))  AS Pvt

ORDER BY 1'

PRINT @DynamicSQL -- Testing & debugging 

/* 

SELECT * FROM (SELECT [Store (Freight Summary)]=s.Name,    

YEAR(OrderDate) AS OrderYear, 

Freight = convert(money,convert(varchar, Freight))    

FROM Sales.SalesOrderHeader soh    

INNER JOIN Sales.Store s    

ON soh.CustomerID = s.CustomerID) as Header    

PIVOT (SUM(Freight)    FOR OrderYear

IN([2001],[2002],[2003],[2004]))  AS Pvt

ORDER BY 1

*/

-- Execute dynamic sql

EXEC sp_executesql   @DynamicSQL

GO
-- Partial results

------------

-- SIXTH EXAMPLE - dynamic stored procedure with output

-- SQL Server dynamic SQL stored procedure to find size for all databases

CREATE PROC sprocSizeForAllDBs

AS

BEGIN

    DECLARE  @dbName      SYSNAME,

             @ObjectSize INT

    DECLARE  @SQL NVARCHAR(MAX)

    DECLARE  @DBSizes  TABLE(

                                   DBName             SYSNAME,

                                   DBSizeinMB       MONEY

                                   )

    DECLARE curAllDBs CURSOR  FOR

    SELECT   name

    FROM     MASTER.dbo.sysdatabases

    WHERE    name NOT IN ('master','tempdb','model','msdb')

    ORDER BY name

    OPEN curAllDBs

    FETCH  curAllDBs

    INTO @dbName

    WHILE (@@FETCH_STATUS = 0) -- Loop through all db-s 

      BEGIN

        -- Build valid yet hard-wired SQL statement

        SET @SQL = 'select @DBSize = 0.0078125 * sum(size) ' + char(13) +

                   'from ' + QuoteName(@dbName) + '.dbo.sysfiles'

        PRINT @SQL -- test & debug 

/* 

select @DBSize = 0.0078125 * sum(size)

from [AdventureWorks].dbo.sysfiles

*/

-- Dynamic call for query execution with output parameter(s)

        EXEC sp_executesql  @SQL ,

                                          N'@DBSize Money output' ,

                                          @DBSize = @ObjectSize OUTPUT

        INSERT @DBSizes

        SELECT @dbName,  @ObjectSize

        FETCH  curAllDBs

        INTO @dbName

      END -- while 

    CLOSE curAllDBs

    DEALLOCATE curAllDBs

    INSERT @DBSizes  -- total size

    SELECT 'Total Space Used', SUM(DBSizeinMB) FROM @DBSizes   

    -- Return results

    SELECT *

    FROM     @DBSizes

    ORDER BY DBSizeinMB DESC

END -- sproc

GO

EXEC sprocSizeForAllDBs

/*

DBName                    DBSizeinMB

....

AdventureWorks            172.00

AdventureWorks2008        182.00

AdventureWorksDW          69.00

AdventureWorksDW2008      87.00

.....

*/

------------