Tutorial :SQL Server - Dynamic PIVOT Table - SQL Injection



Question:

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.

I'm build up some dynamic SQL to produce a PIVOT table in SQL Server 2005.

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.

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  

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.

INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('A', 'FOO])) PVT; DROP TABLE #PivotTest;SELECT ((GETDATE()--', 1)  

When I now run the SQL, low and behold, the EXEC part drops the #PivotTest table thus making the last SELECT fail.

So my question is, does anyone know of a way to perform a dynamic PIVOT without risking SQL Injection attacks?


Solution:1

We've done a lot of work similar to your example. We haven't worried about SQL injenction, in part because we have complete and total control over the data being pivoted--there's just no way malicious code could get through ETL into our data warehouse.

Some thoughts and advice:

  • Are you required to pivot with nvarcahr(500) columns? Ours are varchar(25) or numerics, and it would be pretty hard to sneak damaging code in through there.
  • How about data checking? Seems like if one of those strings contained a "]" character, it's either a hack attempt or data that will blow up on you anyway.
  • How robust is your security? Is the system locked down such that Malorey can't sneak his hacks into your database (either directly or through your application)?

Hah. It took writing all that to remember function QUOTENAME(). A quick test would seem to indicate that adding it to your code like so would work (You'll get an error, not a dropped temp table):

SELECT          @columns =           STUFF          (                  (                          SELECT DISTINCT                                  ', [' + quotename(ColumnB, ']') + ']'                          FROM                                  #PivotTest                          FOR XML PATH('')                  ), 1, 1, ''          )  

This should work for pivot (and unpivot) situations, since you almost always have to [bracket] your values.


Solution:2

A bit of refactoring...

CREATE PROCEDURE ExecutePivot (      @TableName sysname,      @GroupingColumnName sysname,      @AggregateExpression VARCHAR(256),      @SelectExpression VARCHAR(256),      @TotalColumnName VARCHAR(256) = 'Total',      @DefaultNullValue VARCHAR(256) = NULL,      @IsExec BIT = 1)  AS  BEGIN      DECLARE @DistinctGroupedColumnsQuery VARCHAR(MAX);      SELECT @DistinctGroupedColumnsQuery = CONCAT('SELECT DISTINCT ',@GroupingColumnName,' FROM ',@TableName,';');      DECLARE @DistinctGroupedColumnsResult TABLE ( [row] VARCHAR(MAX) );      INSERT INTO @DistinctGroupedColumnsResult EXEC(@DistinctGroupedColumnsQuery);        DECLARE @GroupedColumns VARCHAR(MAX);      SELECT @GroupedColumns = STUFF ( ( SELECT DISTINCT CONCAT(', ',QUOTENAME([row])) FROM @DistinctGroupedColumnsResult FOR XML PATH('') ), 1, 1, '' );        DECLARE @GroupedColumnsNullReplaced VARCHAR(MAX);      IF(@DefaultNullValue IS NOT NULL)          SELECT @GroupedColumnsNullReplaced = STUFF ( ( SELECT DISTINCT CONCAT(', ISNULL(',QUOTENAME([row]),',',@DefaultNullValue,') AS ',QUOTENAME([row])) FROM @DistinctGroupedColumnsResult FOR XML PATH('') ), 1, 1, '' );      ELSE          SELECT @GroupedColumnsNullReplaced=@GroupedColumns;        DECLARE @ResultExpr VARCHAR(MAX) = CONCAT('          ; WITH cte AS          (              SELECT ',@SelectExpression,', ',@GroupedColumns,'              FROM ',@TableName,'              PIVOT ( ',@AggregateExpression,' FOR ',@GroupingColumnName,' IN (',@GroupedColumns,') ) as p          )          , cte2 AS          (              SELECT ',@SelectExpression,', ',@GroupedColumnsNullReplaced,'              FROM cte          )          SELECT ',@SelectExpression,', ',REPLACE(@GroupedColumns,',','+'),' AS ',@TotalColumnName,', ',@GroupedColumns,'          FROM cte2;          ');        IF(@IsExec = 1) EXEC(@ResultExpr);      ELSE SELECT @ResultExpr;  END;  

Usage example:

select schema_id, type_desc, 1 as Item       into PivotTest  from sys.objects;    EXEC ExecutePivot 'PivotTest','type_desc','SUM(Item)','schema_id','[Total Items]','0',1;  


Solution:3

DECLARE @PvtColumns varchar(max)    SET @PvtColumns = STUFF((SELECT ',MAX(CASE WHEN Seq = ' + CAST(Seq AS varchar(10)) + ' THEN gr_hdr_grno END) AS grNo_' +  CAST(Seq AS varchar(10))   +',MAX(CASE WHEN Seq = ' + CAST(Seq AS varchar(10)) + ' THEN gr_hdr_docvalue END) AS gramt_' +  CAST(Seq AS varchar(10))  +',MAX(CASE WHEN Seq = ' + CAST(Seq AS varchar(10)) + ' THEN gr_tcd_amt END) AS grtcd_' +  CAST(Seq AS varchar(10))   +',MAX(CASE WHEN Seq = ' + CAST(Seq AS varchar(10)) + ' THEN document_no END) AS sobi_' +  CAST(Seq AS varchar(10))  +',MAX(CASE WHEN Seq = ' + CAST(Seq AS varchar(10)) + ' THEN sobiamount END) AS samt_' +  CAST(Seq AS varchar(10))  +',MAX(CASE WHEN Seq = ' + CAST(Seq AS varchar(10)) + ' THEN sobivat END) AS svat_' +  CAST(Seq AS varchar(10))  FROM (SELECT DISTINCT Seq FROM (SELECT ROW_NUMBER() OVER (PARTITION BY pomas_pono ORDER BY pomas_pono) AS Seq  FROM po_grn_vat_supp)t)r  ORDER BY Seq  FOR XML PATH('')),1,1,'')      DECLARE @SQL varchar(max) = 'SELECT supp_spmn_supcode,supp_spmn_supname,supp_bu_language,vatregno,pomas_pono,pomas_pobasicvalue,pomas_tcdtotalrate,' + @PvtColumns + '   FROM  (SELECT ROW_NUMBER() OVER (PARTITION BY pomas_pono ORDER BY pomas_pono) AS Seq,*  FROM po_grn_vat_supp)t GROUP BY supp_spmn_supcode,supp_spmn_supname,supp_bu_language,vatregno,pomas_pono,pomas_pobasicvalue,pomas_tcdtotalrate'    EXEC (@SQL)  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »