Donnerstag, 25. April 2013

Flexible Parameterübergabe als Filterkriterien für dynamisches SQL in Stored Procedures

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:

Variable Beschreibung
@base_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 @base_stmt verwendet werden. Weitere Details zur Verwendung von Variablen in sp_executeSQL finden sich in den Links am Ende dieses Artikels.

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!

Referenzen Links
sp_executeSQL http://msdn.microsoft.com/de-de/library/ms188001.aspx
SQL-Injection http://www.sommarskog.se/dynamic_sql.html
sp_execute und Speicherung von Abfrageplänen http://db-berater.blogspot.de/2012/11/tucken-bei-der-verwendung-von.html

Kommentare :

  1. Hallo Uwe,

    ich finde die Prozedur an sich sehr gut und vor allem die Flexibilität und Sicherheit mit denen sie ausgestattet ist.

    Wie wird sie aber angewendet, wenn man ein mit VS 2010 erstelles Gridview auf ein Webformular filtern möchte?

    VG,
    Etien

    AntwortenLöschen
  2. Hallo Etien,

    herzlichen Dank für Dein Interesse an meinem Blog.
    Ich bin nicht der große .NET-Experte aber einem Gridview kannst Du eine Stored Procedure als Datasoure übergeben.

    Dann solltest Du auch in der Lage sein, dieser Datenquelle (ähnlich wie bein den Reporting Services - die kenne ich dann doch etwas besser :) )für die Parameter entsprechende Werte zu übergeben!

    AntwortenLöschen
    Antworten
    1. Hallo Uwe,

      es is mir gelungen, die von dir konzipierten Stored Procedure dem Gridview zu übegeben. Probleme habe ich aber noch mit der Filterung, also mit dem Aufbau der Where-Klausel.

      Auf meinem Webform habe ich folgende Textboxen, über welche der Benutzer Daten eingeben können zwecks Filterung.
      Wie schaffe ich, dass beim Leerlassen einer Textbox alle Datensätze angezeigt werden?

      Außerdem kommentiere ich die folgenden Zeilen nicht aus, funtioniert die Filterung mit Datumswerten nicht. Wieso?
      --IF CHARINDEX('%', @startDatum) != 0
      --IF CHARINDEX('%', @Enddatum) != 0


      Hier meine Where-Klausel:

      -- Konkatenation der WHERE-Klausel

      IF @tbVon IS NOT NULL AND LEN(@startDatum) != 0
      --IF CHARINDEX('%', @startDatum) != 0
      SET @where_stmt = @where_stmt + CASE WHEN LEN(@where_stmt) != 0
      THEN ' AND '
      ELSE ''
      END + 'End >= @startDatum'

      IF @Enddatum IS NOT NULL AND LEN(@Enddatum) != 0
      --IF CHARINDEX('%', @Enddatum) != 0
      SET @where_stmt = @where_stmt + CASE WHEN LEN(@where_stmt) != 0
      THEN ' AND '
      ELSE ''
      END + '[Begin] <= @Enddatum'

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

      VG und Danke im Voraus!
      Etien

      Löschen
  3. Hallo Etien,

    ich habe mal ein Beispiel "on the fly" programmiert, dass mit einer Datumsangabe funktioniert. Ansonsten wäre sicherlich die Frage in den msdn-Foren sehr gut aufgehoben :)

    IF OBJECT_ID('dbo.proc_Members', 'P') IS NOT NULL
    DROP PROC dbo.proc_Members
    GO

    CREATE PROC dbo.proc_Members
    @StartDate char(8),
    @EndDate char(8)
    AS
    SET NOCOUNT ON

    DECLARE @stmt nvarchar(1000) = N'SELECT * FROM dbo.view_Members $(where);'
    DECLARE @parms nvarchar(100) = N'@StartDate datetime, @EndDate datetime';
    DECLARE @where nvarchar(200) = N'';

    -- Wenn es sich um ein gültiges AnfangsDatum handelt!
    IF @StartDate IS NOT NULL AND ISDATE(@StartDate) = 1
    SET @where = @where + 'MemberSince >= @StartDate'

    -- Wenn es sich um ein gültiges Endedatum handelt
    IF @EndDate IS NOT NULL AND ISDATE(@EndDate) = 1
    BEGIN
    IF LEN(@where) != 0
    SET @where = @where + ' AND '

    SET @where = @where + 'MemberSince <= @EndDate'
    END

    IF LEN(@where) != 0
    SET @where = ' WHERE ' + @where;

    SET @stmt = REPLACE(@stmt, '$(where)', @where);
    EXEC sp_executeSQL @stmt, @parms, @StartDate = @StartDate, @EndDate = @EndDate;

    SET NOCOUNT OFF
    GO

    -- Test
    EXEC dbo.proc_Members NULL, '20121231'

    Bei dem Beispiel handelt es sich um eine Relation mit Mitgliedsdaten (MemberSince). Hier kann man ein Start- und ein Endedatum eingeben, nach dem gefiltert werden soll...

    AntwortenLöschen