SQL Server - Dynamic PIVOT Table - SQL Injection
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;
Related videos on Youtube
Comments
-
Robin Day almost 2 years
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?
-
Robin Day over 14 years1) My test sample is a simple one. The actual columns are nvarchar(max). We have no data that size at present and the data that would be used for the PIVOT would rarely be as much as 100 so I may perform a forced truncate in this instance! Great Idea. 2) I was thinking about the '[' and ']'. I'm tempted to strip all square brackets from the data and just have that as a limitation of this functionality. 3) The only people that can add this data are so called "Super Users", however, this is not enough to give me peace of mind.
-
Robin Day over 14 yearsQUOTENAME! First time I've seen it! Perfect! This completely solves the problem. I was adding the QUOTES manually. If I remove this and do it using QUOTENAME it will disable any SQL within that field! THANKYOU!