Während der Besprechung zu einer Projekterweiterung wurde ein Problem besprochen, dass sehr häufig anzutreffen ist – Konkatenation eines SQL-Strings “am Client” und Versand und Ausführung am SQL Server, um die Daten zu ermitteln. Ich habe vorgeschlagen, die komplette Suchroutine in eine Stored Procedure auszulagern. Dieser Stored Procedure werden die erforderlichen Parameter übergeben und die Konkatenation findet innerhalb der Prozedur statt. Das komplette SQL-Statement wird innerhalb der Stored Procedure ausgeführt und die Daten an den Client zurück geliefert. Die Bedenken von SQL-Injection habe ich widerlegt, indem ich argumentiert habe, dass trotz Variabilität in der Parameterübergabe ausschließlich mit sys.sp_executesql und expliziter Parameterübergabe gearbeitet wird.

Problemstellung

Durch die Clientapplikation wird mit Hilfe von Konkatenation ein dynamischer SQL-String aufgebaut, der gegen den SQL Server ausgeführt wird. Neben der Gefahr von SQL Injection wurde das Problem von Plan Cache Bloating von mir angemerkt. Von “Plan Cache Bloating” spricht man, wenn bei jedem konkatenierten String ein anderer Wert für das/die Prädikat(e) übergeben wird. Durch das Ändern des SQL Textes wird Microsoft SQL Server gezwungen, jedes Mal einen neuen Ausführungsplan zu generieren. Dadurch wird der Prozedurcache des SQL Servers nur unnötig belastet. Die nachfolgende Lösung basiert darauf, dass ein SQL-Statement nicht mehr auf Seiten des Clients “zusammengebaut” wird sondern in einer Prozedur hinterlegt ist. Die Variablen / Parameter für die Daten werden der Stored Procedure übergeben und in der Prozedur zu einem Ausführungstext konkateniert. Anschließend wird dieser String mittels sp_executesql bei Übergabe von Parametern ausgeführt und die Daten an den Client zurück geliefert. Durch diese Technik werden gleich drei Probleme beseitigt:

• SQL Injection wird unterbunden, da nur noch die Werte selbst übertragen werden und keine SQL-Strings mehr vom Client konkateniert werden.
• Abfragepläne können wiederverwendet werden, da die Abfrage parametrisiert wird und somit wiederverwendet werden kann
• Änderungen in der Ergebnismenge können schnell implementiert werden und die Clients benötigen keine Updates

Datenstruktur

Um die nachfolgende Technik zu demonstrieren, verwende ich eine Kopie der Systemtabelle [sys].[messages].

-- Erstellen der Demo-Tabelle
SELECT *
INTO dbo.messages
FROM sys.messages;
GO

-- Erstellung eines gruppierten Index...
CREATE UNIQUE CLUSTERED INDEX cuix_messages
ON dbo.messages
(
    message_id,
    language_id
);
GO

-- und eines nicht gruppierten Index
CREATE NONCLUSTERED INDEX nix_messages_severity
ON dbo.messages
(severity);
GO

Um die Daten mittels dynamischem SQL in Verbindung mit Parametern abzufragen, wird eine Prozedur verwendet, die für drei abzufragende Attribute in der Relation Parameter bereitstellt, in denen der zu suchende Wert gespeichert wird. Per Definition haben diese Variablen einen Standardwert von NULL.

-- Prozedur zum Suchen von Meldungstexten
CREATE OR ALTER PROC dbo.FindMessage
    @message_id INT = NULL,
    @language_id INT = NULL,
    @severity TINYINT = NULL
AS
BEGIN
    SET NOCOUNT ON;

    -- Der String $where wird durch den Inhalt von @where ersetzt!
    DECLARE @stmt NVARCHAR(1024) = N'SELECT * FROM dbo.messages $(where)';
    DECLARE @parms NVARCHAR(128) = N'@message_id INT, @language_id INT, @severity TINYINT';
    DECLARE @where NVARCHAR(1024) = N'';

    -- Konkatenieren des WHERE-Statements
    IF @message_id IS NOT NULL
        SET @where = @where + N'message_id = @message_id AND ';

    IF @language_id IS NOT NULL
        SET @where = @where + N'language_id = @language_id AND ';

    IF @severity IS NOT NULL
        SET @where = @where + N'severity = @severity';

    -- Wenn @where länger ist als 0 Zeichen, wurde mindestens 1 Parameter übergeben
    IF LEN(@where) > 0
        SET @where = N'WHERE ' + @where;

    -- Wenn am Ende der WHERE-Klausel ein 'AND' steht, wird es eliminiert
    IF RIGHT(@where, 5) = N' AND '
        SET @where = LEFT(@where, LEN(@where) - 4);

    SET @stmt = REPLACE(@stmt, N'$(where)', @where);

    -- Ausführung des Statements!
    EXEC sp_executesql @stmt, @parms, @message_id, @language_id, @severity;
    SET NOCOUNT OFF;
END
GO

Funktionsweise

Die Prozedur besteht im Wesentlichen aus drei Bereichen. Zunächst werden die “Konstanten” in der Prozedur definiert.

 

Variable	Beschreibung
@stmt	Diese Variable beinhaltet den generellen Abfragestring, wie er später gegen die Datenbank ausgeführt wird. In dieser Variablen befindet sich ein Synonym $(Where), das zur Laufzeit durch den im zweiten Teil konkatenierten WHERE-Teil der Abfrage ersetzt wird.
@parms	Eine Liste von Variablen, die in @stmt verwendet werden. Im zweiten Teil der Prozedur werden die übergebenen Variablen ausgewertet. Bei der Überprüfung wird nur dann ein Prädikat generiert, wenn auch tatsächlich ein Wert für diese Variable übergeben worden ist.
@where	Die Variable @where wird innerhalb der Stored Procedure auf Basis der übergebenen Parameter konkateniert. Für das obige Beispiel ergeben sich 6 Kombinationsmöglichkeiten

Im zweiten Teil der Prozedur werden die Übergabeparameter überprüft. Sofern für einen Parameter ein Wert übergeben wurde, wird die WHERE-Klausel mit Hilfe der Variablen @where konkateniert. Insgesamt ergeben sich so – für das obige Beispiel – maximal 6 Kombinationsmöglichkeiten!

Abschließend wird das auszuführende SQL Statement konkateniert und mit Hilfe von sp_executesql ausgeführt.

Die Ausführung de Abfrage erfolgt abschließend mittels sp_executeSQL. Da nun parametrisierte Abfragen verwendet werden, können bestehende Ausführungspläne wiederverwendet werden. Für die drei nachfolgenden Beispielausführungen ergeben sich folgende Statements:

Ausführungsbeispiel 1

EXEC dbo.FindMessage
    @message_id = 601,
    @language_id = 1033,
    @severity = 12;
GO

Ergebnis: “SELECT * FROM dbo.messages WHERE message_id = @message_id AND language_id = @language_id AND severity = @severity”

Ausführungsbeispiel 2

EXEC dbo.FindMessage
    @message_id = NULL,
    @language_id = 1033,
    @severity = 12;
GO

Ergebnis: “SELECT * FROM dbo.messages WHERE language_id = @language_id AND severity = @severity”

Ausführungsbeispiel 3

EXEC dbo.FindMessage
    @message_id = NULL,
    @language_id = NULL,
    @severity = 12;
GO

Ergebnis: “SELECT * FROM dbo.messages WHERE severity = @severity”

Herzlichen Dank fürs Lesen!