Articles

Warum QUOTENAME wichtig ist

Irgendwann wird die überwiegende Mehrheit von uns dynamisches SQL in unserer Karriere verwenden müssen. Dynamisches SQL ist keine schlechte Sache, aber es ist wirklich wichtig sicherzustellen, dass es sicher ist. Eines der einfachsten Tools ist die gute Verwendung von QUOTENAME .

Umgang mit dynamischen Objekten

Nehmen wir eine einfache gespeicherte Prozedur wie die folgende, bei der jemand versucht hat, sie „sicher“ zu machen“:

 CREATE PROC CreateTable_sp @TableName sysname AS DECLARE @DSQL nvarchar(MAX); SET @DSQL = N'CREATE TABLE (ID int)'; EXEC (@DSQL); GO

Auf den ersten Blick kann man sehen, dass sie versucht haben, die Injektion zu vermeiden, aber es ist alles andere als sicher. Wie bei der Verkettung einer Reihe von Zeichenfolgen in einfachen Anführungszeichen können hier die gleichen Injektionstechniken verwendet werden.

 EXEC dbo.CreateTable_sp @TableName = 'asdklfhjgasdklf] (ID int); SELECT 1 AS test;--';

Dies würde den gekapselten Parameter leicht umgehen und eine unsinnige benannte Tabelle erstellen sowie ein Dataset mit dem Spaltentest zurückgeben. Wenn wir jedoch die Verwendung von QUOTENAME implementieren, sind wir an einem viel besseren Ort:

 CREATE PROC CreateTable_sp @TableName sysname AS DECLARE @DSQL nvarchar(MAX); SET @DSQL = N'CREATE TABLE ' + QUOTENAME(@TableName) + N' (ID int);'; EXEC sp_executesql @DSQL; GO

Wenn wir dasselbe SQL wie oben ausführen würden, hätten Sie tatsächlich eine Tabelle mit dem Namen „asdklfhjgasdklf] (ID int); SELECT 1 AS test;–“ .

QUOTENAME sollte jedoch nicht nur berücksichtigt werden, wenn Sie ein Objekt wie oben erstellen. Dynamische Pivots sind ziemlich häufig, und die Verwendung von QUOTENAME ist dort genauso wichtig. Möglicherweise haben Sie das Gefühl, dass Sie vor einer Injektion sicherer sind, da die Spaltennamen bekannter sind. Das bedeutet jedoch nicht, dass jemand nicht „dumm“ genug war, ein Sonderzeichen im Namen einer Spalte zu verwenden. Wenn Sie eine Spalte mit einem Namen wie CustomerID haben, funktioniert (wie zuvor) die Syntax ‚ + N‘]‘ nicht. Es wird nichts mit diesem Namen injizieren, aber einen Fehler verursachen.

Wenn das dynamische Element kein Objekt ist

Obwohl ich wiederhole, was die Dokumentation (oben verlinkt) Ihnen sagt, denke ich, dass es wichtig ist zu beachten, dass QUOTENAME tatsächlich 2 Parameter akzeptiert. Der erste ist der „character string“ und der zweite (optionale) Parameter ist das „quote character“. Gemäß der Dokumentation ist die Definition des Anführungszeichens „eine Ein-Zeichen-Zeichenfolge, die als Trennzeichen verwendet werden soll. Kann ein einfaches Anführungszeichen ( ‚ ), eine linke oder rechte Klammer ( ) oder ein doppeltes Anführungszeichen ( “ ) sein. Wenn quote_character nicht angegeben ist, werden Klammern verwendet.“ Ich habe jedoch festgestellt, dass andere Charaktere akzeptiert werden. Zum Beispiel funktionieren beide (und { und ich bin sicher, es gibt mehr. Wenn ein Zeichen nicht akzeptabel ist, wird NULL zurückgegeben; zum Beispiel QUOTENAME(’someString‘,’#‘) gibt NULL zurück.

Wenn Sie mit dynamischem SQL arbeiten, haben Sie es möglicherweise nicht immer mit einem dynamischen Objekt zu tun. Beispielsweise können Sie dynamisches SQL mit OPENROWSET verwenden. Wenn Sie den Dateipfad für Ihre Datei deklarieren, muss er in einem einfachen Anführungszeichen (‚) und nicht in Klammern () angegeben werden. Dies bedeutet, dass QUOTENAME(@filePath) nicht funktioniert, aber ändern Sie das in QUOTENAME(@filePath,““) , und es wird.

Wenn wir eine (nicht so) einfache gespeicherte Prozedur verwenden:

 CREATE PROC ImportData_sp @NewTable sysname, @ImportFile nvarchar(512), @Worksheet nvarchar(128) AS DECLARE @DSQL nvarchar(MAX); SET @DSQL = N'SELECT ''' + @Worksheet + ''' AS Worksheet, *' + NCHAR(10) + N'INTO ' + NCHAR(10) + N'FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',' + NCHAR(10) + N' ''Excel 12.0; Database=' + REPLACE(@ImportFile,N'''',N'') + ',' + NCHAR(10) + N' );'; EXEC (@DSQL); GO

Anders als zuvor haben wir einige dynamische Elemente; das Zielobjekt, die Quelldatei und das Arbeitsblatt in dieser Datei. Dies lässt tatsächlich einige Bereiche offen, was alles andere als ideal ist. Aber wir können immer noch mit einer ähnlichen Methodik von früher beheben:

 CREATE PROC ImportData_sp @NewTable sysname, @ImportFile nvarchar(512), @Worksheet nvarchar(128) AS DECLARE @DSQL nvarchar(MAX); SET @DSQL = N'SELECT @dWorkSheet AS WorkSheet, *' + NCHAR(10) + N'INTO ' + QUOTENAME(@NewTable) + + NCHAR(10) + N'FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',' + NCHAR(10) + N' ' + QUOTENAME('Excel 12.0; Database=' + @ImportFile,'''') + ',' + NCHAR(10) + N' ' + QUOTENAME(QUOTENAME(@Worksheet + '$','''')) + N');'; --PRINT @DSQL; EXEC sp_executesql @DSQL, N'@dWorkSheet nvarchar(128)', @dWorksheet = @Worksheet; GO

Hier gibt es noch ein bisschen mehr zu beachten (beachten Sie beispielsweise den verschachtelten QUOTENAMEN für das Arbeitsblatt). Ein Benutzer kann es einfach wie folgt ausführen, und vorausgesetzt, er hat keine einfachen Anführungszeichen oder andere schlechte Sonderzeichen im Namen des Arbeitsblatts (versuchen Sie, wie ich könnte, ich konnte das nicht zum Laufen bringen, wenn jemand weiß wie, bitte kommentieren), dann wird es erfolgreich importiert:

 EXEC dbo.ImportData_sp @NewTable = 'JanData', @ImportFile = 'C:\My Files\ExcelDoc.xlsx;', @Worksheet = 'Jan data';

Fazit

QUOTENAME scheint manchmal eine „vergessene“ Funktion zu sein. Wenn Sie mit dynamischem SQL arbeiten, ist die Verkettung von Rohzeichenfolgen immer eine schlechte Idee. Wenn Sie sicherstellen, dass Sie Ihre Abfrage korrekt parametrisieren, und die Zeichenfolge zitieren, die Sie nicht können, wird die Exposition, die Sie verwenden müssen, erheblich verringert.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.