SQL Server-Dynamic PIVOT Table-SQL Injection
désolé pour la longue question, mais ceci contient tout le SQL que j'ai utilisé pour tester le scénario pour, espérons, le rendre clair quant à ce que je fais.
je suis construire un certain SQL dynamique pour produire une table de PIVOT dans SQL Server 2005.
ci-dessous est le code pour le faire. Avec divers selects montrant les données brutes les valeurs en utilisant GROUP BY et les valeurs dans un PIVOT comme je les veux.
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
chaque fois que je produis un SQL dynamique Je suis toujours au courant des attaques par Injection SQL. Par conséquent, j'ai ajouté la ligne suivante avec les autres INSERT statements.
INSERT INTO #PivotTest (ColumnA, ColumnB, ColumnC) VALUES('A', 'FOO])) PVT; DROP TABLE #PivotTest;SELECT ((GETDATE()--', 1)
quand j'exécute maintenant le SQL, bas et voici, la partie EXEC laisse tomber la table #PivotTest faisant ainsi le dernier SELECT fail.
donc ma question Est, est-ce que quelqu'un connaît une façon d'effectuer un PIVOT dynamique sans risquer des attaques par Injection SQL?
3 réponses
Nous avons fait beaucoup de travail similaires à votre exemple. Nous ne nous sommes pas inquiétés de L'injec tion SQL, en partie parce que nous avons le contrôle complet et total sur les données étant pivotées--il n'y a juste aucun moyen que le code malveillant pourrait passer par ETL dans notre entrepôt de données.
Quelques réflexions et conseils:
- devez-vous pivoter avec les colonnes nvarcahr(500)? Les nôtres sont varchar (25) ou numerics, et il serait assez difficile de se faufiler le code dommageable en par là.
- et la vérification des données? Il semble que si l'une de ces chaînes contenait un caractère"]", c'est soit une tentative de piratage, soit des données qui vont exploser sur vous de toute façon.
- Quelle est la solidité de votre sécurité? Le système est-il verrouillé de sorte que Malorey ne puisse pas introduire ses piratages dans votre base de données (directement ou par le biais de votre application)?
Hah. Il a fallu écrire tout cela pour se souvenir de la fonction QUOTENAME(). Rapide test semble indiquer que l'ajouter à votre code comme si cela fonctionnerait (vous obtiendrez une erreur, pas une table de température abandonnée):
SELECT
@columns =
STUFF
(
(
SELECT DISTINCT
', [' + quotename(ColumnB, ']') + ']'
FROM
#PivotTest
FOR XML PATH('')
), 1, 1, ''
)
cela devrait fonctionner pour les situations de pivot (et unpivot), puisque vous avez presque toujours à [bracket] vos valeurs.
un peu de remaniement...
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;
exemple d'utilisation:
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;
DECLARE @PvtColumns varchar(max)
SET @PvtColumns = STUFF((SELECT ',MAX(CASE WHEN Seq = ' + CAST(Seq AS varchar(10)) + ' THEN gr_hdr_grno END) AS grNo_' + CAST(Seq AS varchar(10))
+',MAX(CASE WHEN Seq = ' + CAST(Seq AS varchar(10)) + ' THEN gr_hdr_docvalue END) AS gramt_' + CAST(Seq AS varchar(10))
+',MAX(CASE WHEN Seq = ' + CAST(Seq AS varchar(10)) + ' THEN gr_tcd_amt END) AS grtcd_' + CAST(Seq AS varchar(10))
+',MAX(CASE WHEN Seq = ' + CAST(Seq AS varchar(10)) + ' THEN document_no END) AS sobi_' + CAST(Seq AS varchar(10))
+',MAX(CASE WHEN Seq = ' + CAST(Seq AS varchar(10)) + ' THEN sobiamount END) AS samt_' + CAST(Seq AS varchar(10))
+',MAX(CASE WHEN Seq = ' + CAST(Seq AS varchar(10)) + ' THEN sobivat END) AS svat_' + CAST(Seq AS varchar(10))
FROM (SELECT DISTINCT Seq FROM (SELECT ROW_NUMBER() OVER (PARTITION BY pomas_pono ORDER BY pomas_pono) AS Seq
FROM po_grn_vat_supp)t)r
ORDER BY Seq
FOR XML PATH('')),1,1,'')
DECLARE @SQL varchar(max) = 'SELECT supp_spmn_supcode,supp_spmn_supname,supp_bu_language,vatregno,pomas_pono,pomas_pobasicvalue,pomas_tcdtotalrate,' + @PvtColumns + '
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY pomas_pono ORDER BY pomas_pono) AS Seq,*
FROM po_grn_vat_supp)t GROUP BY supp_spmn_supcode,supp_spmn_supname,supp_bu_language,vatregno,pomas_pono,pomas_pobasicvalue,pomas_tcdtotalrate'
EXEC (@SQL)