title: SQL Server - Dynamic PIVOT Table - SQL Injection
body: <p>Sorry for the long question but this contains all the SQL I've used to test the scenario to hopefully make it clear as to what I'm doing.</p>
  
  <p>I'm build up some dynamic SQL to produce a PIVOT table in SQL Server 2005.</p>
  
  <p>Below is code to do this. With various selects showing the raw data the values using GROUP BY and the values in a PIVOT as I want them.</p>
  
  <pre><code>BEGIN TRAN
  --Create the table
  CREATE TABLE #PivotTest
  (
      ColumnA nvarchar(500),
      ColumnB nvarchar(500),
      ColumnC int
  )
  
  --Populate the data
  INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('A', 'X', 1)
  INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('A', 'Y', 2)
  INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('A', 'Z', 3)
  INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('A', 'X', 4)
  INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('A', 'Y', 5)
  INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('B', 'Z', 6)
  INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('B', 'X', 7)
  INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('B', 'Y', 8)
  INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('B', 'Z', 9)
  INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('C', 'X', 10)
  INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('C', 'Y', 11)
  INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('C', 'Z', 12)
  
  --The data
  SELECT * FROM #PivotTest
  
  --Group BY
  SELECT
      ColumnA,
      ColumnB,
      SUM(ColumnC)
  FROM
      #PivotTest
  GROUP BY
      ColumnA,
      ColumnB
  
  --Manual PIVOT
  SELECT
      *
  FROM
      (
          SELECT
              ColumnA,
              ColumnB,
              ColumnC
          FROM
              #PivotTest
      ) DATA
      PIVOT
      (
          SUM(DATA.ColumnC)
      FOR
          ColumnB
          IN
          (
              [X],[Y],[Z]
          )
      ) PVT
  
  --Dynamic PIVOT
  DECLARE @columns nvarchar(max)
  
  SELECT
      @columns = 
      STUFF
      (
          (
              SELECT DISTINCT
                  ', [' + ColumnB + ']'
              FROM
                  #PivotTest
              FOR XML PATH('')
          ), 1, 1, ''
      )
  
  EXEC
  ('
      SELECT
          *
      FROM
          (
              SELECT
                  ColumnA,
                  ColumnB,
                  ColumnC
              FROM
                  #PivotTest
          ) DATA
          PIVOT
          (
              SUM(DATA.ColumnC)
          FOR
              ColumnB
              IN
              (
                  ' + @columns + '
              )
          ) PVT
  ')
  
  --The data again
  SELECT * FROM #PivotTest
  
  ROLLBACK
  </code></pre>
  
  <p>Anytime that I produce any dynamic SQL I'm always aware of SQL Injection attacks. Therefore I've added the following line with the other INSERT statements.</p>
  
  <pre><code>INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('A', 'FOO])) PVT; DROP TABLE #PivotTest;SELECT ((GETDATE()--', 1)
  </code></pre>
  
  <p>When I now run the SQL, low and behold, the EXEC part drops the #PivotTest table thus making the last SELECT fail.</p>
  
  <p>So my question is, does anyone know of a way to perform a dynamic PIVOT without risking SQL Injection attacks?</p>