SQL Server-动态PIVOT表-SQL注入 [英] SQL Server - Dynamic PIVOT Table - SQL Injection

问题描述

很抱歉，这个问题很长，但这包含了我用来测试场景的所有SQL，希望可以清楚地说明我在做什么.

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.

我正在构建一些动态SQL，以在SQL Server 2005中生成PIVOT表.

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

下面是执行此操作的代码.通过各种选择显示原始数据，可以使用GROUP BY的值和PIVOT中的值，如我所愿.

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

每当我生成任何动态SQL时，我总是会意识到SQL Injection攻击.因此，我在其他INSERT语句中添加了以下行.

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)

当我现在运行SQL(低位运行)时，EXEC部分会删除#PivotTest表，从而使最后一个SELECT失败.

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

所以我的问题是，有谁知道一种执行动态PIVOT而不冒SQL注入攻击风险的方法吗?

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

推荐答案

我们已经完成了很多与您的示例相似的工作.我们不必担心SQL注入，部分原因是我们对正在旋转的数据拥有完全而全面的控制-恶意代码绝对不可能通过ETL进入我们的数据仓库.

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.

一些想法和建议:

• 是否需要使用nvarcahr(500)列进行数据透视?我们使用的是varchar(25)或数字，很难在其中潜入破坏性代码.
• 如何检查数据?好像这些字符串之一包含]"字符一样，它要么是黑客企图，要么是无论如何都会炸毁您的数据.
• 您的安全性如何?系统是否已锁定，以致Malorey无法(直接或通过应用程序)将黑客入侵到您的数据库中?

哈.花了所有的时间来记住函数QUOTENAME().快速测试似乎表明将其添加到您的代码中可以正常工作(您将得到一个错误，而不是一个临时表被删除):

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.

这篇关于SQL Server-动态PIVOT表-SQL注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！