Leider findet man oft SQL, das nicht typsicher und anfällig für Codeinjektion ist. Man stelle sich nur mal vor das @paramCODE mit ‘ValueX’’; DROP TABLE …–‘ gefüllt wäre.
1: DECLARE @paramCODE AS VARCHAR(100) = 'ValueX';
2:
3: DECLARE @sqlToExec AS VARCHAR(MAX) =
4: 'SELECT
5: *
6: FROM ...
7: WHERE CODE = ''' + @paramCODE + ''''
8: EXEC (@sqlToExec)
Oder wie konvertiert man am besten den Datentyp DATETIME nach VARCHAR?
1: DECLARE @paramID AS INT = 20;
2: DECLARE @paramStichtag AS DATETIME = '2014-01-01';
3:
4: DECLARE @sqlToExec AS VARCHAR(MAX) =
5: 'SELECT
6: *
7: FROM ...
8: WHERE ID = ' + STR(@paramID) + '
9: AND Stichtag = ''' + CONVERT(varchar,@paramStichtag,21) + ''''
10: EXEC (@sqlToExec)
Wesentlich verbessert wird der Code durch den Einsatz von sp_executesql:
- Übergabe der Parameter erfolgt ohne Konvertierung (Codeinjektion wird erschwert)
- DROP Table wird nicht ausgeführt, sondern in der Suche (Where) benutzt
1: DECLARE @paramCODE AS VARCHAR(100) = 'ValueX''; DROP TABLE ...--';
2:
3: DECLARE @sqlToExec AS NVARCHAR(MAX) =
4: 'SELECT
5: *
6: FROM ...
7: WHERE CODE = @execParamCODE';
8:
9: EXECUTE sp_executesql
10: @sqlToExec,
11: N'@execParamCODE VARCHAR(100)',
12: @execParamCODE = @paramCODE;
- Übergabe der Parameter erfolgt typsicher
1: DECLARE @paramID AS INT = 20;
2: DECLARE @paramStichtag AS DATETIME = '2014-01-01';
3:
4: DECLARE @sqlToExec AS NVARCHAR(MAX) =
5: 'SELECT
6: *
7: FROM ...
8: WHERE ID = @execParamID
9: AND Stichtag = @execParamStichtag';
10:
11: EXECUTE sp_executesql
12: @sqlToExec,
13: N'@execParamID INT, @execParamStichtag DATETIME',
14: @execParamID = @paramID,
15: @execParamStichtag = @paramStichtag;
weitere Voreile:
- Einsatz von OUTPUT Parametern ist möglich
- Weniger ‘’’’’ und damit eine bessere Lesbarkeit
- Kompilierung und mögliche Wiederverwendbarkeit* von Ausführungsplänen
Auch wenn der Aufruf von sp_executesql im ersten Moment komplexer wirkt, ist der Code am Ende für mich lesbarer, verständlicher und sicherer.
*) nur wenn @sqlToExec gleich ist, meist gibt es auch bei dynamischen SQL eine feste Anzahl von Möglichkeiten.
Sie sehen gerade einen Platzhalterinhalt von Facebook. Um auf den eigentlichen Inhalt zuzugreifen, klicken Sie auf die Schaltfläche unten. Bitte beachten Sie, dass dabei Daten an Drittanbieter weitergegeben werden.
Mehr InformationenSie sehen gerade einen Platzhalterinhalt von Instagram. Um auf den eigentlichen Inhalt zuzugreifen, klicken Sie auf die Schaltfläche unten. Bitte beachten Sie, dass dabei Daten an Drittanbieter weitergegeben werden.
Mehr InformationenSie sehen gerade einen Platzhalterinhalt von X. Um auf den eigentlichen Inhalt zuzugreifen, klicken Sie auf die Schaltfläche unten. Bitte beachten Sie, dass dabei Daten an Drittanbieter weitergegeben werden.
Mehr Informationen