很抱歉这个长问题,但是这包含了我用来测试场景的所有sql,希望能够清楚我在做什么.
我建立了一些动态sql,以在sql Server 2005中生成一个PIVOT表.
以下是执行此操作的代码.通过各种选择显示原始数据,使用GROUP BY的值和PIVOT中的值,因为我想要它们.
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,'Y',2)
INSERT INTO #PivotTest (ColumnA,'Z',3)
INSERT INTO #PivotTest (ColumnA,4)
INSERT INTO #PivotTest (ColumnA,5)
INSERT INTO #PivotTest (ColumnA,ColumnC) VALUES('B',6)
INSERT INTO #PivotTest (ColumnA,7)
INSERT INTO #PivotTest (ColumnA,8)
INSERT INTO #PivotTest (ColumnA,9)
INSERT INTO #PivotTest (ColumnA,ColumnC) VALUES('C',10)
INSERT INTO #PivotTest (ColumnA,11)
INSERT INTO #PivotTest (ColumnA,12)
--The data
SELECT * FROM #PivotTest
--Group BY
SELECT
ColumnA,SUM(ColumnC)
FROM
#PivotTest
GROUP BY
ColumnA,ColumnB
--Manual PIVOT
SELECT
*
FROM
(
SELECT
ColumnA,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,''
)
EXEC
('
SELECT
*
FROM
(
SELECT
ColumnA,ColumnC
FROM
#PivotTest
) DATA
PIVOT
(
SUM(DATA.ColumnC)
FOR
ColumnB
IN
(
' + @columns + '
)
) PVT
')
--The data again
SELECT * FROM #PivotTest
ROLLBACK
任何时候,我产生任何动态sql我总是知道sql注入攻击.因此,我已经添加了以下行与其他INSERT语句.
INSERT INTO #PivotTest (ColumnA,'FOO])) PVT; DROP TABLE #PivotTest;SELECT ((GETDATE()--',1)
当我现在运行sql的时候,看到,EXEC部分会丢弃#PivotTest表,从而使最后一个SELECT失败.
所以我的问题是,有没有人知道一种方式来执行动态PIVOT,而不冒sql Injection攻击的风险？
解决方法
我们做了很多类似于你的例子的工作.我们并不担心sql注入,部分原因在于我们对数据进行完整的全面控制 – 只有恶意代码才能通过ETL获得数据仓库.
一些想法和建议：
您需要使用nvarcahr(500)列进行转换吗？我们是varchar(25)或数字,并且通过那里潜行的破坏性代码很难.
>数据检查怎么样？似乎如果其中一个字符串包含一个“]”字符,这是一个黑客尝试或数据,将会爆炸你反正.
>你的安全性有多强？系统是否锁定,以至于Malorey无法将其黑客攻入数据库(直接或通过您的应用程序)？
哈.它写了所有记住功能QUOTENAME().一个快速的测试似乎表明,将它添加到您的代码如此将工作(你会得到一个错误,而不是一个删除的临时表)：
SELECT
@columns =
STUFF
(
(
SELECT DISTINCT
',[' + quotename(ColumnB,']') + ']'
FROM
#PivotTest
FOR XML PATH('')
),''
)
这应该适用于枢轴(和解除)情况,因为您几乎总是必须[括号]您的值.
