Lo siento por la pregunta larga, pero este contiene todos los SQL que yo he utilizado para probar el escenario para lo pondrá en claro qué es lo que estoy haciendo.

Estoy crear un poco de SQL dinámico para producir una tabla dinámica en SQL Server 2005.

A continuación es el código para hacer esto. Con diversos selecciona mostrar los datos en bruto de los valores de GRUPO y los valores en una dinámica como 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

Cualquier momento que me produce ningún SQL dinámico estoy siempre al tanto de los ataques de Inyección SQL. Por lo tanto, he añadido la siguiente línea con el resto de las instrucciones INSERT.

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

Cuando yo ahora ejecutar el SQL, bajo y he aquí, el EXEC parte gotas de la #PivotTest tabla con lo que la última SELECCIONAR error.

Así que mi pregunta es, ¿alguien sabe de una forma de realizar una dinámica de PIVOTE sin correr el riesgo de ataques de Inyección de SQL?

OriginalEl autor Robin Day | 2009-09-17

3 Comentarios

  1. 16

    Hemos hecho un montón de trabajo similares a los de su ejemplo. No hemos preocupado acerca de SQL injenction, en parte debido a que contamos con una completa y total control sobre los datos que se dinamiza–simplemente no hay manera de que el código malicioso puede obtener a través de ETL en nuestro almacén de datos.

    Algunos pensamientos y consejos:

    • Se le requiere para girar con nvarcahr(500) columnas? Los nuestros son de tipo varchar(25) o caracteres numéricos, y sería bastante difícil colarse dañar el código a través de allí.
    • ¿Y comprobación de los datos? Parece como si una de esas cadenas de contenidos «]» carácter», es un intento de hack o datos que va a explotar de todos modos.
    • Cómo robusto es su seguridad? Es el sistema bloqueado abajo, de modo que Malorey no puede colarse sus trucos en la base de datos (ya sea directamente o a través de su aplicación)?

    Ja. Se llevó a escribir todo eso para recordar la función QUOTENAME(). Una prueba rápida parece indicar que añadir a tu código como lo haría el trabajo (obtendrá un error, no una quita de la tabla temporal):

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

    Esto debería funcionar para el eje (y unpivot) situaciones, ya que casi siempre se tiene que [soporte] sus valores.

    1) Mi muestra de prueba es sencilla. El real columnas son de tipo nvarchar(max). No tenemos datos de ese tamaño en la actualidad y de los datos que podrían ser utilizados para el PIVOTE rara vez tanto como 100, de manera que pueda realizar un forzado trunca en esta instancia! Gran Idea. 2) yo estaba pensando en el ‘[‘ y ‘]’. Estoy tentado a la franja de todos los corchetes de los datos y sólo la tienen como una limitación de esta funcionalidad. 3) Las únicas personas que pueden agregar estos datos son los llamados «Super Usuarios», sin embargo, esto no es suficiente para que me diera paz de la mente.
    QUOTENAME! La primera vez que he visto! Perfecto! Esto soluciona totalmente el problema. Yo era la adición de las CITAS de forma manual. Si puedo quitar esto y hacerlo con QUOTENAME va a deshabilitar cualquier SQL dentro de ese campo! GRACIAS!

    OriginalEl autor Philip Kelley

  2. 0

    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[email protected]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;

    OriginalEl autor Abin

  3. -1
    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)

    OriginalEl autor Mohamed Udhuman

Dejar respuesta

Please enter your comment!
Please enter your name here