Dynamic PIVOT Table

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?

---

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.

---

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;

链接地址: http://www.djcxy.com/p/16766.html

上一篇: 准备好的语句如何防止SQL注入攻击？

下一篇: 动态枢轴表