Mittendrin.

Zurück

Flurfunk der eXperts.

Hier erfahren Sie mehr über eXperts, Technologien und das wahre Leben in der SDX.

Exec SQL mit Parametern

19.06.201508:00 Uhr , Alexander Kabisch

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.

Tags: SQL Server

0 Kommentare
Dein Kommentar wartet auf Freischaltung.

Artikel kommentieren

Zurück

Tag Cloud


Kontakt aufnehmen


Anrufen

Gerne beantworten wir Ihre Fragen in einem persönlichen Gespräch!


Kontakt aufnehmen

Schreiben Sie uns eine E-Mail mit Ihren Fragen und Kommentaren!