Dynamisches SQL mit EXECUTE IMMEDIATE

Inhalt

1 Datentypprobleme
2 Schlechte Performance
3 Sicherheitslücken / SQL-Injection
4 Bindevariablen in EXECUTE IMMEDIATE
5 Die Using-Klausel
6 Die INTO-Klausel
7 BULK COLLECT INTO-Klausel

Bisher waren wir in unseren PL/SQL Programmen auf statisches SQL angewiesen. Eine gewisse Dynamik brachte der Cursor, der mit Hilfe von Parametern die SELECT Anweisung innerhalb bestimmter Grenzen anpassen konnte. Aber auch das ist unbefriedigend. Und spätestens, wenn Sie ein DDL-Kommando innerhalb von PL/SQL ausführen wollen, sind wir komplett hilflos.Gründe für den Einsatz von dynamischem SQL gibt es viele: Vielleicht steht der Name der Tabelle, aus der selektiert werden soll, erst zur Laufzeit fest, oder mehr noch, der Name der Tabelle soll als Argument beim Aufruf einer Prozedur übergeben werden. Dynamisches SQL kann benutzt werden, um alle meine Tabellen zu löschen. Besitze ich 50 Tabellen und will diese loswerden, tippe ich nicht 50mal drop table ein, sondern schreibe mir einen PL/SQL-Block, der das für mich übernimmt. Dynamisch.

Eigentlich müßte der Artikel „Dynamische Programmierung“ heißen, denn es ist nicht nur dynamisches SQL, sondern auch dynamisches PL/SQL möglich. Früher hat man dynamisches SQL ausschliesslich über das Package DBMS_SQL realisieren können, heute ist es einfacher denn je: EXECUTE IMMEDIATE.

Ein paar Dinge vorweg:

die Kommandos, die wir mit EXECUTE IMMEDIATE ausführen, sind ganz normal eingebettet in unsere Transaktion. Führen wir ein dynamisches DML-Statement aus, muss es committed werden, damit andere die Änderung sehen. Führen wir dynamisches DDL aus, wird automatisch committed, auch das, was wir vorher mit DML-Anweisungen gemacht haben.
Führen wir dynamisches SQL aus, terminieren wir unseren Befehl NICHT mit Semikolon
Führen wir dynamisches PL/SQL aus, terminieren wir wie gehabt jede Anweisung mit Semikolon, inklusive END;
Hüten Sie sich, einen SQL-Befehl mit Hilfe von String-Konkatenation zusammenzustellen. Erstens gibt es Probleme mit den Datentypen, zweitens könnten Hacker Ihren Code mit SQL-Injection-Versuchen aushebeln, drittens holen Sie nicht das Beste an Performance raus. Nutzen Sie stattdessen Bindevariablen.

Jetzt aber los:

EXECUTE IMMEDIATE ist eine ausführbare Anweisung, sie kann also nur im ausführbaren Teil eines Blocks oder im Fehlerbehandlungsteil stehen. EXECUTE IMMEDIATE wird gefolgt von einer Zeichenkette, entweder statisch in einfachen Hochkommata, oder in Form einer Variablen.

declare
  cmd_string varchar2(200) := 'delete from scott.emp';
begin
  execute immediate cmd_string;
  commit;
  execute immediate 'delete from scott.dept';
  rollback;
end;

Wenn ich einen Befehl ausführen kann, der in einer Variablen steht und ich den Wert der Variablen ja selber bestimmen kann, dann geht auch das hier. Ein Block, der alle meine Tabellen dropt (Achtung, DDL committed automatisch, also Vorsicht beim Ausprobieren)

begin
  for rec in (select table_name from user_tables) loop
     execute immediate 'drop table '||rec.table_name||' cascade constraints';
  end loop;
end;

Die Technik, Befehle durch einfache Variablen-Konkatenierung wie oben zusammenzusetzen, hat drei große Nachteile:

Wir müssen über Datentypen nachdenken.
das SQL-Kommando wird durch den SQL-Interpreter immer wieder neu geparst, sobald der Befehl „anders“ aussieht als ein schon mal ausgeführter. Und anders aussehen heißt, sobald der Befehlsstring sich von einem bereits ausgeführten unterscheidet.
Wenn man die Stringkonkatenation ungeschickt anstellt, implementiert man mögliche Sicherheitslücken. Und zuguterletzt muss man dem Aufrufer klarmachen, dass er Zeichenketten in einer Zeichenkette mit doppelten Hochkommata übergeben muss, also z.B. execute del_employee(“’SCOTT“‘)

Diese drei Nachteile möchte ich an einer stored procedure demonstrieren. Sie hat die Aufgabe, das Gehalt eines Mitarbeiters, dessen Name übergeben wird, um 10% anzuheben. Um genau zu sehen, was passiert, lasse ich das auszuführende Statement auch noch mal auf den Bildschirm schreiben.

create or replace procedure 
gehaltserhoehung (mitname scott.emp.ename%TYPE) is
  stmt varchar2(200);
begin
  stmt := 'update scott.emp set sal = sal * 1.1 '||
          'where ename = '||mitname;
  dbms_output.put_line(stmt);
  execute immediate stmt;
end;
/

So, jetzt die Nachteile dieser Art der Programmierung:

Datentypprobleme

Grund 1 ist einfach nachzuweisen. Mit einem DESCRIBE sehen wir die Schnittstelle der Procedure, sie sagt eindeutig, wir sollen ein Argument mitname vom Typ VARCHAR2(10) übergeben. Ich probiers und bin enttäuscht.

SQL> exec gehaltserhoehung('ALLEN')
update scott.emp set sal = sal * 1.1 where ename = ALLEN
BEGIN gehaltserhoehung('ALLEN'); END;
*
FEHLER in Zeile 1:
ORA-00904: "ALLEN": ung³ltiger Bezeichner
ORA-06512: in "SYSTEM.GEHALTSERHOEHUNG", Zeile 8
ORA-06512: in Zeile 1

Zum Glück habe ich mir das Statement selbst auch noch mal auf den Bildschirm schreiben lassen und so erkenne ich das Dilemma: Ich habe zwar eine Stringvariable übergeben, aber beim Konkatenieren gehen die einfachen Hochkomma verloren, sie markieren ja lediglich den Beginn und das Ende einer Stringkonstante. Bei der Übergabe an den SQL-Interpreter sind die Hochkomma aber zwingend notwendig, denn in meinem Beispiel glaubt der SQL-Interpreter, ALLEN wäre ein Ausdruck oder eine Spalte der Tabelle, nein, es muss ja auch heissen ‚ALLEN‘.

Wir wissen, dass ein Hochkomma in einem String dargestellt werden kann, indem man es einfach doppelt angibt, also

SQL> exec gehaltserhoehung(' ''ALLEN'' ')
update scott.emp set sal = sal * 1.1 where ename =  'ALLEN'

PL/SQL-Prozedur erfolgreich abgeschlossen.

Schön wäre, auf derlei Feinheiten keine Rücksicht nehmen zu müssen.

Schlechte Performance

Für den Grund 2 „schlechte Performance“ muss ich weiter ausholen: saloop ausgedrückt, parst der SQL-Interpreter ein SQL-Statement immer dann, wenn es nicht bereits vorgeparst im RAM des Servers steht. Und das kostet natürlich Zeit. Und die will man einsparen. Um zu sehen, welche Statements schon vorgeparst im RAM stehen, kann man die DataDictionary-View v$sqlarea abfragen. Hier stehen gerade für das Thema Tuning eine Reihe wichtiger Informationen zur Verfügung. Wir beschränken uns auf die Anzahl Ausführungen, die Spalte executions. Nehmen wir also an, ich hätte bereits drei Mitarbeitern ein neues Gehalt verpaßt, dann könnte da stehen:

SQL> col sql_text format a60 word
SQL> col executions format 99 heading EXEC
SQL> select sql_Text, executions from v$sqlarea
  2  where sql_text like '%update scott.emp%';
SQL_TEXT                                                     EXEC
------------------------------------------------------------ ----
update scott.emp set sal = sal * 1.1 where ename = 'KING'       1
update scott.emp set sal = sal * 1.1 where ename = 'JAMES'      1
select sql_Text, executions from v$sqlarea where sql_text       1
like '%update scott.emp%'
update scott.emp set sal = sal * 1.1 where ename = 'ALLEN'      1

Hier kann man erkennen, dass nicht das gleiche Statement immer wieder ausgeführt wurde, sondern das der Parser drei verschiedene Statements erkannt hat, obwohl sich diese ja eigentlich nur im WHERE-Teil unterscheiden. Der Ausführungplan, der im Rahmen des Parsens erstellt wird, ist wahrscheinlich auch immer der gleiche: Entweder erfolgt ein Zugriff mit Hilfe eines Index oder eben ein Full-Table-Scan. In solchen Situationen kann man Parse-Zeiten sparen und das Statement verwenden, das bereits geparst wurde. Der Schlüssel dazu sind sogenannte Bindevariablen.

Sicherheitslücken / SQL-Injection

Grund 3, die oben gezeigte Prozedur nicht auf diese Art und Weise zu implementieren. An diesem Beispiel ist das auch schnell erklärt. Ein Hacker, aber auch jemand, der allen eine 10%ige Gehaltserhöhung verpassen möchte, könnte die Prozedur wie folgt ausführen:

SQL> exec gehaltserhoehung(' ''ALLEN'' or 1=1')
update scott.emp set sal = sal * 1.1 where ename =  'ALLEN' or 1=1
PL/SQL-Prozedur erfolgreich abgeschlossen.

Anhand der Ausgabe von DBMS_OUTPUT sehen wir, was passiert ist. Die WHERE-Bedingung wurde „ausgehebelt“. Na, das war nicht die Intension des Programmierers, denn beim Aufruf wurde eine Bedingung in den Where-Teil des Update-Kommandos „eingeschmuggelt“, die immer TRUE ergibt und damit alle Datensätze updated. Das gleiche könnte passieren, wenn z.B. nur bestimmte Buchungsbelege mit einer angegebenen Belegnummer gelöscht werden sollen, beim Aufruf mit Hilfe dieser Technik dann aber alle Belege – vielleicht aller Geschäftsjahre – gelöscht werden. Sie werden es nicht glauben: auch hier helfen oben erwähnte Bindevariablen.

Bindevariablen in EXECUTE IMMEDIATE

Bindevariablen lösen alle oben genannten Probleme und werden wie folgt verwendet. Eine Bindevariable wird in einem SQL-Statement mit einem Doppelpunkt vor dem Variablennamen gekennzeichnet. Der Parser parst das Statement mit der Variablen, die Variablenersetzung durch den konkreten Wert erfolgt erst vor der Ausführung. Das heißt, der Parser erkennt am stets gleichen Variablennamen, dass er das Statement schon mal übersetzt hat und benutzt den im RAM stehenden Ausführungsplan. Wenn Bindevariablen benutzt werden, müssen wir uns über Datentypen und Wertübergaben nicht mehr kümmern. Die korrekte Ersetzung der Variablen durch die Konstante erfolgt automatisch. Wir brauchen also nicht mehr Strings in zweifache Hochkommata schreiben, denn allein der Typ String sorgt für korrekte Ersetzung. Und aus dem gleichen Grund ist auch SQL-Injection kaum mehr möglich. Richtig hätte ich oben genannte Prozedur wie folgt implementiert, um den Problemen aus dem Weg zu gehen:

Die Using-Klausel

create or replace procedure 
gehaltserhoehung (mitname scott.emp.ename%TYPE) is
begin
  execute immediate 'update scott.emp set sal = sal * 1.1'||
  'where ename = :bindvar' using mitname ;
end;
/

Die Variable :bindvar habe ich nach eigenem Gusto benannt, wichtig ist der Teil USING. Hier wird AUSSERHALB des Strings, der das SQL-Statement enthält, festgelegt, welcher tatsächliche Wert bei der Ausführung an die Stelle des „Platzhalter“ :bindvar tritt.

Prüfen wir unsere drei Probleme. Datentypprobleme: Sind verschwunden. Wir rufen die Prozedur auf, übergeben ganz normal einen String. Das dieser im SQL-Statement in einfachhen Hochkomma stehen muss, weiss PL/SQL anhand des Datentypen. Dadurch verflüchtigt sich auch das Problem mit SQL-Injection, denn es gibt bspw. keinen Datensatz, wo in der Spalte ename der Wert ‚Allen or 1=1‘ steht. Und zuguterletzt profitieren wir von reduzierten Parseaktivitäten.

SQL> exec gehaltserhoehung('ALLEN')
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> exec gehaltserhoehung('''ALLEN''')
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> exec gehaltserhoehung('''ALLEN'' or 1=1')
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> exec gehaltserhoehung('SMITH')
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> select sql_Text, executions from v$sqlarea
  2  where sql_text like '%update scott.emp%';
SQL_TEXT                                                     EXEC
------------------------------------------------------------ ----
update scott.emp set sal = sal * 1.1where ename = :bindvar      4
select sql_Text, executions from v$sqlarea where sql_text       1
like '%update scott.emp%'

Erläuterung: Das erste Statement erhöht das Gehalt von ALLEN, das zweite updated nichts, denn es gibt keinen Mitarbeiter ‚ALLEN‘ (mit Hochkomma), genausowenig wie es einen Mitarbeiter ALLEN or 1=1 gibt. SMITH ist wiederum ein Mitarbeiter, der in der Tabelle existiert, sein Gehalt wird um 10% erhöht. Zuguterletzt werfen wir wieder einen Blick in v$sqlarea und erkennen, dass der Parser sagt, viermal wurde das Statement mit Bindevariable ausgeführt. Einmal hat er es geparst, dreimal konnte er die vorgeparste Form verwenden. Na, das nenn‘ ich mal einen Gewinn!

Was, wenn ich mehrere Bindevariablen verwenden möchte? Kein Problem, wir zählen sie im USING-Teil auf in der Reihenfolge, in der sie entsprechend der Position im SQL-Statement verwendet werden sollen. Alle Mitarbeiter in der Abteilung 10 deren Namen mit A beginnt, werden gelöscht.

begin
execute immediate 'delete from scott.emp where deptno=:var1 '||
      'and ename like :var2' using 10, 'A%';
end;
/

Nun ja, das ist ja erst die halbe Wahrheit, den dynamisches SQL bedeutet nicht nur, Variablen für DML-Statement zu übergeben, sondern wir wollen vielleicht ein SELECT-Statement dynamisch zusammenstellen und die Ergebnismenge in PL/SQL übernehmen. Die Technik bleibt die gleiche, wir nutzen Bindevariablen.

Die INTO-Klausel

declare
  abtnr number :=10;
  summe number; 
begin
  execute immediate' select sum(sal) from scott.emp where deptno=:var1' 
         into summe using abtnr;
end;

Hier ist lediglich wichtig, sich zu merken, dass erst die INTO-Klausel kommt und dann eine eventuelle USING-Klausel. Aber wir müssen uns auch hier Gedanken machen, was passiert, wenn das Statement mehr als einen Datensatz zurückliefert. Obiges Beispiel klappt wieder nur, weil die Gruppenfunktion genau einen Wert liefert und der perfekt in meine skalare Variable passt.

BULK COLLECT INTO-Klausel

Wenn es darum geht, mehrere Zeilen eines dynamischen Select zurückzuerhalten, brauchen wir einen Datentyp, der mehr als eine Zeile aufnehmen kann. Zum Beispiel die PL/SQL-Tabelle. Unsere Syntax ändert sich nur wenig: Anstelle von INTO tritt ein BULK COLLECT INTO.

create or replace procedure 
zeig_namen (abtnr scott.emp.deptno%TYPE) is
  type namentabtyp is table of scott.emp.ename%TYPE index by binary_integer;
  namentab namentabtyp;
begin
  execute immediate 'select ename from scott.emp '||
  'where deptno = :bindvar' 
  bulk collect into namentab
  using abtnr;
  for i in namentab.first .. namentab.last loop
    dbms_output.put_line(namentab(i));
  end loop;
end;
/

Diese Prozedur zeigt alle Mitarbeiternamen einer Abteilung auf dem Bildschirm. Es wird eine PL/SQL-Tabelle definiert, die dann mit bulk collect gefüllt wird. Im Anschluss wird der Inhalt der PL/SQL-Tabelle auf dem Bildschirm in einer Schleife angezeigt.

Der Vollständigkeit halber sei an dieser Stelle erwähnt, dass es auch noch eine RETURNING Klausel gibt. Das wird aber derart selten verwendet, dass ich hier auf eine Erläuterung verzichte.