SQL Server – Tabla Dynamic PIVOT – Inyección SQL

Perdón por la larga pregunta, pero contiene todos los SQL que he usado para probar el escenario y espero que aclare lo que estoy haciendo.

Estoy desarrollando algunos SQL dynamics para producir una tabla PIVOT en SQL Server 2005.

A continuación está el código para hacer esto. Con varias selecciones que muestran los datos sin procesar los valores usando GROUP BY y los valores en un PIVOT como los quiero.

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 

Cada vez que produzco un SQL dynamic, siempre estoy al tanto de los ataques de inyección SQL. Por lo tanto, he agregado la siguiente línea con las otras instrucciones INSERT.

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

Cuando ahora ejecuto el SQL, bajo y he aquí, la parte EXEC deja caer la tabla #PivotTest, lo que hace que falle el último SELECT.

Entonces mi pregunta es, ¿alguien sabe de una manera de realizar un PIVOT dynamic sin arriesgar los ataques de inyección SQL?

Hemos hecho mucho trabajo similar a tu ejemplo. No nos hemos preocupado por la injuricación de SQL, en parte porque tenemos un control completo y total sobre los datos que se pivotan, simplemente no hay forma de que un código malicioso pueda pasar a través de ETL en nuestro almacén de datos.

Algunos pensamientos y consejos:

  • ¿Estás obligado a pivotar con columnas nvarcahr (500)? Los nuestros son varchar (25) o numéricos, y sería muy difícil introducir códigos dañinos allí.
  • ¿Qué hay de la comprobación de datos? Parece que si una de esas cadenas contiene un caracter “]”, es un bash de pirateo o datos que explotarán de todos modos.
  • ¿Cuán robusta es tu seguridad? ¿El sistema está bloqueado de forma tal que Malorey no puede introducir sus hacks en su base de datos (ya sea directamente oa través de su aplicación)?

Hah. Tomó escribir todo eso para recordar la función QUOTENAME (). Una prueba rápida parecería indicar que agregarlo a tu código de esa manera funcionaría (obtendrás un error, no una tabla temporal caída):

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

Esto debería funcionar para situaciones pivote (y unpivot), ya que casi siempre tiene que [corchear] sus valores.

Un poco de refactorización …

 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; 

Ejemplo de uso:

 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;