Während der Besprechung zu einer Projekterweiterung wurde unter anderem ein Problem besprochen, dass sehr häufig anzutreffen ist – Konkatenation eines SQL-String “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 dann nur noch die Parameter übergeben und die Konkatenation findet dann in der Prozedur statt. Das komplette SQL-Statement wird dann 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 sp_executeSQL und expliziter Parameterübergabe gearbeitet wird. Die Herausforderung war nicht ganz einfach aber ich habe eine funktionierende Lösung entwickeln können, die mehrere Vorteile besitzt.

Problemstellung

Am Client wird mittels Konkatenation ein dynamischer SQL-String aufgebaut, der dann gegen den SQL Server ausgeführt wird. Neben der Gefahr von SQL Injection wurde unter anderem ausgeführt, dass Ausführungspläne nicht wiederverwendet werden können, wenn die SQL-Statements nicht parametrisiert gegen die Datenbank ausgeführt werden. 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 Querystring konkateniert. Anschließend wird dieser String mittels sp_executeSQL unter Ausnutzung der Ü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 ausgeführt werden müssen
• 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, soll das nachfolgende Datenmodell mit ein paar Datensätzen dienen. Hierbei handelt es sich um eine Relation mit einem einfachen Aufbau.

CREATE TABLE dbo.tbl_exec_demo
(
    id          int           NOT NULL    IDENTITY (1, 1),
    Company     varchar(256)  NOT NULL,
    Street      varchar(128)  NOT NULL,
    ZIP         char(10)      NOT NULL,
    City        varchar(128)  NOT NULL,
    FirstName   varchar(64)   NULL,
    LastName    varchar(64)   NULL,
    CostCenter  char(10)      NOT NULL,
    Phone       char(20)      NULL,
    Fax         char(20)      NULL,
    Email       varchar(256)  NULL,

    CONSTRAINT pk_tbl_exec_demo_Id PRIMARY KEY CLUSTERED (Id)
);
GO

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

CREATE PROC dbo.proc_app_SearchDemo
    @Company    varchar(256)    =    NULL,
    @Street     varchar(128)    =    NULL,
    @ZIP        char(10)        =    NULL,
    @City       varchar(128)    =    NULL
AS
    SET NOCOUNT ON

    DECLARE @base_stmt  nvarchar(1000) = N'SELECT * FROM dbo.tbl_exec_demo $(where)';
    DECLARE @parms      nvarchar(256)  = N'@Company varchar(256), @Street varchar(128), @ZIP char(10), @City varchar(128)';
    DECLARE @where_stmt nvarchar(1000) = N'';

    -- Konkatenation der WHERE-Klausel
    IF @Company IS NOT NULL AND LEN(@Company) != 0
        IF CHARINDEX('%', @Company) != 0
            SET @where_stmt = @where_stmt + 'Company LIKE @Company'
        ELSE
            SET @where_stmt = @where_stmt + 'Company = @Company'

    IF @Street IS NOT NULL AND LEN(@Street) != 0
        IF CHARINDEX('%', @Street) != 0
            SET @where_stmt = @where_stmt + CASE WHEN LEN(@where_stmt) != 0                                                  THEN ' AND '                                                  ELSE ''                                             END + 'Street LIKE @Street'
        ELSE
            SET @where_stmt = @where_stmt + CASE WHEN LEN(@where_stmt) != 0                                                  THEN ' AND '                                                  ELSE ''                                             END + 'Street = @Street'

    IF @ZIP IS NOT NULL AND LEN(@ZIP) != 0
        IF CHARINDEX('%', @ZIP) != 0
            SET @where_stmt = @where_stmt + CASE WHEN LEN(@where_stmt) != 0                                                  THEN ' AND '                                                  ELSE ''                                             END + 'ZIP LIKE @ZIP'
        ELSE
            SET @where_stmt = @where_stmt + CASE WHEN LEN(@where_stmt) != 0                                                  THEN ' AND '                                                  ELSE ''                                             END + 'ZIP = @ZIP'

    IF @City IS NOT NULL AND LEN(@City) != 0
        IF CHARINDEX('%', @City) != 0
            SET @where_stmt = @where_stmt + CASE WHEN LEN(@where_stmt) != 0                                                  THEN ' AND '                                                  ELSE ''                                             END + 'City LIKE @City'
        ELSE
            SET @where_stmt = @where_stmt + CASE WHEN LEN(@where_stmt) != 0                                                  THEN ' AND '                                                  ELSE ''                                             END + 'City = @City'

    -- Konkatenation von Basis-Statement und WHERE-Klausel
    SET @base_stmt = REPLACE(@base_stmt, '$(where)', CASE WHEN LEN(@where_stmt) != 0 THEN 'WHERE ' ELSE '' END + @where_stmt);

    -- Ausführung des Statements
    EXEC sp_executeSQL @base_stmt, @parms, @Company = @Company, @Street = @Street, @ZIP = @ZIP, @City = @City;
    SET NOCOUNT OFF GO

Funktionsweise

Die Prozedur besteht im Wesentlichen aus drei Bereichen. Zunächst werden die “Konstanten” in der Prozedur definiert. Hierbei genießen die nachfolgenden Variablen besondere Aufmerksamkeit:

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. Um die Prozedur etwas flexibler zu gestalten, kann der Anwender auch “” übergeben, dass wie ein NULL behandelt wird. Wenn einer Variablen ein Wert übergeben wurde, wird innerhalb der Routine überprüft, ob der Anwender ein Suchmuster (%) oder einen exakten Suchparameter übergeben hat. Abhängig davon wird die WHERE-Klausel entweder mit einem “=” oder einem “LIKE” definiert.

Der dritte Teil der Prozedur behandelt abschließend die Bereitstellung und Ausführung der Abfrage. Sind so alle Parameter ausgewertet worden, kann diese WHERE-Klausel in die Variable @base_stmt implementiert werden. Hierzu wird einfach das in @base_stmt enthaltene Synonym durch die generierte WHERE-Klausel ersetzt. Übergibt ein Anwender beispielsweise folgende Parameter:

@Company = “db%”
@ZIP     = “64390”

wird der reine Abfragetext in der Variablen @base_stmt zu

SELECT * FROM dbo.tbl_exec_demo WHERE Company LIKE @Company AND ZIP = @ZIP;

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.

Herzlichen Dank fürs Lesen!