Was sind parametrisierte SQL-Statements und wann anwenden?

[juergen]

Hallo zusammen,

immer wieder liest man hier etwas von "parametrisierten SQL-Statements" anwenden...
Z.B. auch heute:

Zitat:

Zitat von Uwe Raabe (Beitrag 1261159)

Zitat:

Zitat von DeddyH (Beitrag 1261148)

Verwendest Du keine parametrisierten SQL-Statements?

<Sarcasm>So'n neumodisches Zeugs braucht doch eh niemand!</Sarcasm>

Dazu habe ich folgende Fragen:
1. Wann sollte man das anwenden?
2. Wie wendet man das an?
3. Was ist der Vorteil gegenüber einem normalen adden des SQL-Statements?

Ich würde mich über Antworten freuen welche ein Anfänger versteht. Ich will erst mal das "Grobe" verstehen, dann hoffe ich komme ich damit weiter.

Vielen Dank schon mal im Voraus!

[mkinzler]

Zitat:

1. Wann sollte man das anwenden?

Ich verwende eigentlich so gut wie immer parametrsierte Abfragen.

Zitat:

2. Wie wendet man das an?

Anstatt der Werte trägt man Parameter (Variablen) ein. Die Abfrage wird dann vom SQL-Server geparst (vorbereitet). Bei der eigentlichen Abfrage werden dann nur noch die Werte-Tupel übertragen.

Zitat:

3. Was ist der Vorteil gegenüber einem normalen adden des SQL-Statements?

-System sorgt für richtiges Format
-kein Quoten o.ä. nötig
-sicherer gegenüber sql injection
-bei mehrfacher Ausführung muss die Abfarge nur einmal "kompilliert" werden (Parsse, Zurgriffsplan erstellen usw.)-> bessere Performance

[juergen]

@mkinzler,

schon mal Danke!

Den Punkt 2 verstehe ich noch nicht wie ich das umsetzen muss.
Angenommen ich habe eine Query. Die hat die Eigenschaft "Params".
Muss ich dort anstelle in "SQL" das Statement hinterlegen?

Wie müsste dann folgender Aufruf implementiert werden?

Code:

'select A.Name, A.Kd-Nr from Adressstamm as A'

//Edit: wobei A.Name = string und A.Kd-Nr = Integer

[Union]

Parameter kann man natürlich nur setzen, wo auch welche sinnvoll sind. Deine Beispielabfrage lät sich nicht parametrisieren. Ein Beispiel wäre:

Code:

select A.Name, A.Kd-Nr from Adressstamm as A
where A.Kd-nr = :Kdnr

Den Parameter der Query füllst Du dann z.B. so und führst die Abfrage durch:

Delphi-Quellcode:

Query.Active := False;
Query.ParamByName('Kdnr') := '12345';
Query.Active := True;

Sinnvoll ist das besonders, wenn die selbe Abfrage in einer Schleife mehrfach ausgeführt werden soll. Je nach Intelligenz des Servers wird dann beim ersten Mal wie bereits von den anderern beschrieben, die Query vorbereitet und in den Folgeaufrufen nur noch der Parameterwert gesetzt.

[himitsu]

1: fast immer
2: kommt auf die Zugriffskomponenten drauf an, aber oftmals gibt es neben den FieldByName-Methoden auch die passenden ParamByName usw.
3: Sicherheit, Einfachheit, Sicherheit, Fehlerunanfälliger, Sicherheit, ...

Erstmal muß man die Strings nicht umständlich "quoten" und wenn man das nicht machen würde, dann ist man lebensmüde.

PS: Gerade das ist eines der Hauptgründe, warum sich jemand in irgendeine Webseite reinhacken konnte.
- Eingaben nicht oder nicht ausreichend geprüft
- Daten ungeschützt in SQLs eingebaut => Code-Injection

Stell es dir wie den Format-Befehl vor:
- Im SQL-String gibt es Platzhalter, welche dann mit den eigentlichen Werten gefüllt werden, wobei hierbei die Datenbank/Zugriffskomponenten quasi aber die zugewiesenen Werte prüfen.
- Außerdem kann es schneller werden, da der SQL-Parser das SQL nur einmal pürfen/auswerten muß und man dann ganz schnell viele Werte/Datensätze verarbeiten kann, indem man nur noch die Parameterwerte anpasst.

[mkinzler]

Das ist ein schlechtes Beispiel, da hier nichts parammetrisierbar ist

SQL-Code:

select 
    A.Name, 
    A.Kd-Nr 
from 
    Adressstamm
where
  A.Kd-Nr = :kdnr;

Der Parameter kdnr ist durch den Doppelpunkt erkennbar

Anderes Beispiel

SQL-Code:

insert into person ( vorname, name, gebdat, ...)
values ( :vorname, :name, :gebdat, ...)  ;

Delphi-Quellcode:

Deklaration Abfrage und deren Vorbereitung:
Query.SQL.Text := <Abfrage>;
Query.PrepareSQL;
....

Ausführung der Abfrage (mehrmals möglich):

Delphi-Quellcode:

Query.ParamByName('vorname').Value := 'Hans';
Query.Params[1].Value := 'Mueller';
Query.ParamByName('gebdat').Value := ...;    
Query.ExecuteSQL;
Query.ParamByName('vorname').Value := 'Susi';
Query.Params[1].Value := 'Mueller';
Query.ExecuteSQL;

Hans und Susi sind Zwillinge, der Nachname und das Geburtsdatum ist gleich.
Dem Parameter kannst Du direkt ein Datum zuweisen und musst Dir keine Gedanken um das Datumsformat machen.

[juergen]

Ahhhh, supi, es lichtet sich so langsam. Danke an Alle! :dp:
Es geht doch nichts über anschauliche Beispiele. Dann werde ich das in Zukunft mit den Parameter-Übergaben so umsetzen.

Allen eine Gute N8!

[joachimd]

eine Anmerkung noch zu mkinzlers Beispiel: ich würde nicht unbedingt auf Value gehen, sondern direkt mit AsString, AsDate usw arbeiten, damit der zu erwartende Datentyp auchschon im Code klar ist:

Delphi-Quellcode:

Query.ParamByName('vorname').AsString := 'Hans';
Query.Params[1].AsString := 'Mueller';
Query.ParamByName('gebdat').AsDate := ...;
Query.ExecuteSQL;

[TRomano]

Hallo zusammen,

ich sehe leider in verschiedenen Projekten Unmengen an String-Operationen, die immer wieder in Schleifen dasselbe SQL-Statement zusammen basteln, um die Parameter zu setzen.
Da gehen Statements bis in den Kilobyte-Bereich und keiner sieht mehr durch.
Es ist dort sinnvoller (aus Performance-Sicht und der Übersichtlichkeit wegen) ein SQL-Statement einmalig zu "basteln" (z.B. in einem Query-Tool) und die dann vielleicht 20 Parameter zu setzen. Das ganze Gedönse der unterschiedlichen Behandlung von Datums-Formaten (z.B.) entfällt auch. :thumb:

Gruß Thomas

[mkinzler]

Zitat:

Zitat von joachimd (Beitrag 1261538)

eine Anmerkung noch zu mkinzlers Beispiel: ich würde nicht unbedingt auf Value gehen, sondern direkt mit AsString, AsDate usw arbeiten, damit der zu erwartende Datentyp auchschon im Code klar ist:

Delphi-Quellcode:

Query.ParamByName('vorname').AsString := 'Hans';
Query.Params[1].AsString := 'Mueller';
Query.ParamByName('gebdat').AsDate := ...;
Query.ExecuteSQL;

Dann hat man u.U. aber viele unnötigen Cast-Operationen.

[jobo]

Zitat:

Zitat von TRomano (Beitrag 1261539)

ich sehe leider in verschiedenen Projekten Unmengen an String-Operationen, die immer wieder in Schleifen dasselbe SQL-Statement zusammen basteln, um die Parameter zu setzen.
Da gehen Statements bis in den Kilobyte-Bereich und keiner sieht mehr durch.
Es ist dort sinnvoller (aus Performance-Sicht und der Übersichtlichkeit wegen) ein SQL-Statement einmalig zu "basteln" (z.B. in einem Query-Tool) und die dann vielleicht 20 Parameter zu setzen. Das ganze Gedönse der unterschiedlichen Behandlung von Datums-Formaten (z.B.) entfällt auch. :thumb:

:thumb:

Zitat:

Zitat von mkinzler (Beitrag 1261541)

Zitat:

Zitat von joachimd (Beitrag 1261538)

eine Anmerkung noch zu mkinzlers Beispiel: ich würde nicht unbedingt auf Value gehen, sondern direkt mit AsString, AsDate usw arbeiten, damit der zu erwartende Datentyp auchschon im Code klar ist:

Delphi-Quellcode:

Query.ParamByName('vorname').AsString := 'Hans';
Query.Params[1].AsString := 'Mueller';
Query.ParamByName('gebdat').AsDate := ...;
Query.ExecuteSQL;

Dann hat man u.U. aber viele unnötigen Cast-Operationen.

Vielleicht sollte man beim Thema Parameter und Performance 2 Dinge unterscheiden.
Anwendungsperformance (beim Client) und Abfrageperformance (auf der DB).
Bei "einmaligen" Abfragen geht es in erster Linie um die Anwortzeit der DB und die ist weder von der clientseitigen Aufbereitung noch von der Parametrierung abhängig. Genauer, parametriert, typisiert, egal. Die Analyse dürfte vom Laufzeitverhalten nur einen Bruchteil ausmachen, gegenüber dem Aufbau des Datencursors.
Bei "massenhaften" Abfragen sieht es anders aus. Sowohl die Parametrierung im Client (Parameteransprache via Index/Name, ggF. Casts, ..) also auch die Analyse DB seitig werden zum Laufzeitfaktor. "Massenhaft" kann dabei sowohl ein Client auftreten mit einer Query Loop, als auch viele Clients (100, 1000) mit der immer gleichen Gelegenheits-Abfrage.

[Der schöne Günther]

Ich habe mich bis heute immer das gleiche gefragt, jetzt weiß ich endlich auch mit Sicherheit, was Parameter bei Queries sind 8-)

Kann jemand noch ein bisschen aus der Praxis von konkreten Datenbanken erzählen? Ich könnte mir jetzt vorstellen, dass ein DMBS es beispielsweise erlauben würde, den Tabellennamen selbst zu paramatrisieren, ein anderes hingegen nicht. Gibt es da was?

[DeddyH]

Nach meiner Kenntnis kann man grundsätzlich ausschließlich Werte parametrisieren, also weder Datenbank-, Tabellen- noch Feldnamen.

[himitsu]

Also, Parameter sind grundsätzlich erstmal nur Werte.
Namen von Tabellen und Feldern sind aber keine Werte.

Dafür gibt es dann z.B. Makros, bei einigen Komponenten, wie z.B. den xxDAC's.

Delphi-Quellcode:

Query.SQL.Text := 'SELECT AnderesFeld FROM &Table WHERE Feld = :Wert';
Query.MacroByName('Tabelle').Value := 'irgendwas';
Query.ParamByName('Wert').AsString := 'abc';
Query.Open;
ShowMessage(Query.FieldByName('AnderesFeld').AsString);

[jobo]

Zitat:

Zitat von Der schöne Günther (Beitrag 1261579)

Kann jemand noch ein bisschen aus der Praxis von konkreten Datenbanken erzählen? Ich könnte mir jetzt vorstellen, dass ein DMBS es beispielsweise erlauben würde, den Tabellennamen selbst zu paramatrisieren, ein anderes hingegen nicht. Gibt es da was?

Ich kann zu Oracle konkret sagen, die Analyse der Abfrage erfolgt ganz stumpf nach Gleichheit. Ist die Abfrage nicht exakt identisch (select * from customer where nr = 1/select * from customer where nr = 2), sind es zwei verschiedene Abfragen und es werden beide analysiert.
Bei Parametern die dann als Platzhalter im Querytext konstant bleiben, erfolgt die Analyse nur bei der ersten Abfrage.

Dynamische Tabellennamen
Wäre manchmal schön, aber
Das riecht etwas nach verkorkstem Datenmodell ala "ich habe hier alle Kunden, die 2014 dazu gekommen sind in CUSTOMER2014 und dann in tabelle CUSTOMER2013 die von .. ".
Es bedeutet im Prinzip, dass ich gleiche Strukturen vorsehe und ein Merkmal aber im Tabellenname definiere und das ist meistens schlecht.
Es gibt bei Oracle eine API mit der man dynamisch SQL konstruierten kann, aber das ist wohl ein Thema für sich.

[Dejan Vu]

Zitat:

Zitat von mkinzler (Beitrag 1261541)

Zitat:

Zitat von joachimd (Beitrag 1261538)

.. sondern direkt mit AsString, AsDate usw arbeiten, damit der zu erwartende Datentyp auch schon im Code klar ist

Dann hat man u.U. aber viele unnötigen Cast-Operationen.

Den Einwand verstehe ich nicht. Man hat gerade keine Cast-Operationen, und damit auch keine Unnötigen.

Delphi-Quellcode:

myQuery.ParamByName('Foo').Value := a;
myQuery.ParamByName('Bar').Value := b;
// vs
myQuery.ParamByName('Foo').AsString := a;
myQuery.ParamByName('Bar').AsDateTime := b;

Bezüglich der Feldnamen/Tabellen als Parameter: Wenn ich mir meine WHERE-Klausel dynamisch zusammenbauen will, wäre das vielleicht ganz praktisch, aber da muss man eben direkt an die Query ran. So wild ist das ja nun auch nicht.

[TRomano]

Zitat:

von Dejan Vu
myQuery.ParamByName('Foo').Value := a;
myQuery.ParamByName('Bar').Value := b;
// vs
myQuery.ParamByName('Foo').AsString := a;
myQuery.ParamByName('Bar').AsDateTime := b;

In deinem Beispiel beschreibst Du genau das Problem: Deinen Feldnamen sind 'Foo' und 'Bar' und woher soll man wissen, was wirklich hinter den Feldnamen an Typen steckt. Sollte mal ein anderer Entwickler an deinen Source ran ... viel Glück !
Im zweiten Teil ist es klarer und verständlicher ...

[TRomano]

Und noch etwas fällt mir sehr oft auf: in großen Query-Loops wird gerne mit .FieldByName(..) oder ParamByName(..) gearbeitet. Den letzten "Rekord", den ich da sah waren ca. 30.000 Loops über 38 Felder jeweils mit .FieldByName(..) !!! Da kann man auch entweder über den DataSet-Editor persistente Felder anlegen oder eben Variablen nach dem Muster oFldName := DataSet.FieldByName('Name') anlegen. Erstens kann man Laufzeitzeiter-Fehler abfangen (if (oFldName = nil) und zweitens wird man mit einem nicht unerheblichen Performance-Plus belohnt ... oder optimiert der Delphi-Compiler so etwas weg :wink::thumb:

Gruß Thomas

[himitsu]

Zitat:

Zitat von TRomano (Beitrag 1261600)

Im zweiten Teil ist es klarer und verständlicher ...

Und der Compiler prüft auch die zugewiesenen Typen.

Ja, bei Schleifen, kommt man besser, wenn man sich die Felder vorher besorgt, falls das die Komponente nicht gleich bietet.
Einige Query-Komponenten (auf der Form abgelegt) bieten die Möglichkeiten die ensprechenden Feld-/Paramer-Komponenten (TField/TParam) direkt erstellen zu lassen.

Delphi-Quellcode:

Query.Insert;
QueryFeldA.AsString := '123';  // bzw. Query.FeldA.AsString := '123';
Query.Post;

statt

Delphi-Quellcode:

Query.Insert;
Query.FieldByName(FeldA).AsString := '123';
Query.Post;

Das gibt im Code nochmal einen Schritt mehr, in punkto Codesicherheit, da hier der Compiler die hingeschriebenen Komponentennamen prüfen kann, ob es das TField auch gibt
und die Codevervollständigung schlägt einem die Namen ebenfalls vor.

Entsprechendes gilt z.B. auch für die ORMs.





Nja, selbst wenn man nicht das Performance-Plus ausnutzt und wenn man oft im Code irgendwelche Queries aufruft, dann kann man dennoch nummerierte Parameter benutzen.

Delphi-Quellcode:

with x.LoadSql('SELECT * FROM tabelle WHERE feld = :param', ['abc']) do 
  try
    while not EoF do begin
      // mach was, mit FieldByName['xxx']
      Next;
    end;
  finally
    Free;
  end;

S := x.GetStr('SELECT xxx FROM tabelle WHERE feld = :param AND x <> :x', ['abc', 3]);
ShowMessage(S);

So ist der Code immernoch übersichtlicher und fehlerunanfälliger, als wenn da erst der SQL-String wirr zusammengemanscht wird.

Das Parameter-Array kann dabei z.B. die SQL-Parameter in der Reihenfolge der benutzen Namen enthalten, gefolgt von eventuellen Makros.

[Dejan Vu]

Zitat:

Zitat von TRomano (Beitrag 1261600)

Im zweiten Teil ist es klarer und verständlicher ...

Gerade *DAS* meinte ich ja!

[TRomano]

Okay. Dann hatte ich das zu schnell gelesen ... es geht halt stramm dem langen WE zu :? :wink: !

[Union]

Zitat:

Zitat von Der schöne Günther (Beitrag 1261579)

Kann jemand noch ein bisschen aus der Praxis von konkreten Datenbanken erzählen? Ich könnte mir jetzt vorstellen, dass ein DMBS es beispielsweise erlauben würde, den Tabellennamen selbst zu paramatrisieren, ein anderes hingegen nicht. Gibt es da was?

Tabellennamen lassen sich normalerweise nur über Tricks parametrisieren. Und dann dient das auch nicht der Optimierung und die Implementierung ist datenbankabhängig:

Code:

declare @c cursor as 
select name, 
      case 
      when substring(name,2,1) between '0' and '9' then 1990 + cast(substring(name,2,1) as SQL_INTEGER)       
      else 2000 + Ascii(substring(name,2,1)) - 65
      end as Jahr
from rechnungen;
open @c;
try
   drop table #temp;     
catch all
end try;
create table #temp (Name char(8), Id integer);
while fetch @c do
  execute immediate 
    'insert into #temp (Name, Id, Umsatz) '+
   'select name, b.id'+
   'from rechnungen inner join '+
        -- Hier wird der Name der Tabelle aus zwei Feldern des erzeugten Cursors gebildet
        -- In der gejointen Tabelle steht das Feld Umsatz
   '(select top 1 * from ['+Trim(convert(@c.Jahr, SQL_CHAR))+'\'+Trim(@c.name)+']) B '+
   'on True '+
   'where rechnungen.name = '''[e-mail]+''';';
end while;
close @c;
-- Rückgabe-Datenmenge
select * from #temp;

[Stevie]

Zitat:

Zitat von jobo (Beitrag 1261594)

Ich kann zu Oracle konkret sagen, die Analyse der Abfrage erfolgt ganz stumpf nach Gleichheit. Ist die Abfrage nicht exakt identisch (select * from customer where nr = 1/select * from customer where nr = 2), sind es zwei verschiedene Abfragen und es werden beide analysiert.
Bei Parametern die dann als Platzhalter im Querytext konstant bleiben, erfolgt die Analyse nur bei der ersten Abfrage.

Das ist auch beim SQL Server so. D.h. mit unparametrisierten Queries kann man den Prozedurcache so richtig derbst zumüllen.