Exec SQL mit Parametern

19. Juni 2015
Dynamisches SQL wird gern verwendet, um komplexe Probleme schnell und einfach zu lösen. Sehr oft wird dieses SQL in Stored Procedures zusammengebaut, um beispielsweise Spalten situativ zu selektieren oder komplexe where-Statements zu erstellen.

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.