SQL Server - 动态 PIVOT 表 - SQL 注入
抱歉问了这么长的问题，但这包含了我用来测试场景的所有 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.
相关文章