SQL Server - 动态数据透视表 - SQL 注入

2022-01-22 00:00:00 pivot sql-server-2005 sql-injection sql-server dynamic-pivot

抱歉这个问题太长了，但这包含了我用来测试场景的所有 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 注入攻击.因此，我在其他 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.

所以我的问题是，有没有人知道在不冒 SQL 注入攻击风险的情况下执行动态 PIVOT 的方法?

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.

相关文章