6. September 2013

PL/SQL-Code in einer SQL-Abfrage? Ja, Oracle12c!

PL/SQL-Code within a SQL query? Yes, in Oracle12c!
Heute geht es um ein neues SQL-Feature in Oracle12c - der PL/SQL inline WITH-Klausel. Die seit Oracle9i verfügbare SQL WITH-Klausel zum sog. Subquery Factoring ist den meisten ja bekannt (dazu gab es auch mal ein Blog Posting). Neu in Oracle12c ist, dass man innerhalb dieser WITH-Klausel PL/SQL-Funktionen definieren kann, die dann nur für diese eine Abfrage gültig sind.
with
  function half_sal(p_sal in number) return number 
  is 
  begin 
    return p_sal/2; 
  end;
  function quarter_sal(p_sal in number) return number
  is 
  begin 
    return p_sal/4; 
  end;
select empno, sal, half_sal(sal) half_sal, quarter_sal(sal) quarter_sal 
from emp
/

     EMPNO        SAL  HALF_SAL QUARTER_SAL
---------- ---------- --------- -----------
      7369        800    400,00      200,00
      7499       1600    800,00      400,00
      7521       1250    625,00      312,50
         :          :         :           : 
Die Funktionen sind tatsächlich nur zur Laufzeit der Abfrage gültig - man findet im Data Dictionary anschließend keine PL/SQL-Funktionen mit kryptischen Namen. Die Möglichkeiten, die man nun in der Datenbank mit einer SQL-Abfrage hat, vervielfachen sich damit.
Es stellt sich aber die Frage, ob damit nicht ein großes Sicherheitsloch entsteht. Ist eine Anwendung für SQL Injection Attacken verwundbar, so scheint es, als ließe sich nun ganz bequem auch noch PL/SQL Code "injizieren". Aber so einfach ist es nun auch wieder nicht. PL/SQL in der SQL WITH-Klausel ist nur in der "Top-Level"-Abfrage erlaubt - nicht dagegen in einer Subquery. Das lässt sich einfach nachvollziehen ...
select * from (
  with
    function half_sal(p_sal in number) return number 
    is 
    begin 
      return p_sal/2; 
    end;
    function quarter_sal(p_sal in number) return number
    is 
    begin 
      return p_sal/4; 
    end;
  select empno, sal, half_sal(sal) half_sal, quarter_sal(sal) quarter_sal 
  from emp
)
/

SQL> @script.sql
  with
  *
FEHLER in Zeile 2:
ORA-32034: Nicht unterstützte Benutzung von WITH-Klausel
Das Injizieren von PL/SQL kann also sehr einfach verhindert werden, indem man die SQL-Abfrage einfach als Unterabfrage in ein SELECT * FROM (...) "einrahmt". Wer allerdings sicher ist, dass seine SQL-Abfragen keinerlei SQL-Injection-Schwachstellen aufweisen und PL/SQL WITH in einer Unterabfrage verwenden möchte, kann dies mit dem Hint WITH_PLSQL "freischalten".
select /*+ WITH_PLSQL*/ * from (
  with
    function half_sal(p_sal in number) return number 
    is 
    begin 
      return p_sal/2; 
    end;
    function quarter_sal(p_sal in number) return number
    is 
    begin 
      return p_sal/4; 
    end;
  select empno, sal, half_sal(sal) half_sal, quarter_sal(sal) quarter_sal 
  from emp
)
/

     EMPNO        SAL  HALF_SAL QUARTER_SAL
---------- ---------- --------- -----------
      7369        800    400,00      200,00
      7499       1600    800,00      400,00
      7521       1250    625,00      312,50
         :          :         :           : 
Der WITH_PLSQL Hint ist immer erforderlich, wenn es kein SELECT, sondern ein DML Statement ist (UPDATE, INSERT, DELETE). Mehr Informationen zu "PLSQL in der WITH-Klausel" findet Ihr in der Dokumentation - SQL Language Reference.
This blog posting is about a new feature in the Oracle12c database: the new possibility to add PL/SQL to a SQL WITH Clause. Most readers know about the SQL WITH clause, which is available since Oracle9i - in 2008 I published a blog posting about Subquery Factoring). Now, with Oracle12c, PL/SQL functions can be defined within the WITH clause of a SQL Statement - and these functions are valid for this very query only.
with
  function half_sal(p_sal in number) return number 
  is 
  begin 
    return p_sal/2; 
  end;
  function quarter_sal(p_sal in number) return number
  is 
  begin 
    return p_sal/4; 
  end;
select empno, sal, half_sal(sal) half_sal, quarter_sal(sal) quarter_sal 
from emp
/

     EMPNO        SAL  HALF_SAL QUARTER_SAL
---------- ---------- --------- -----------
      7369        800    400,00      200,00
      7499       1600    800,00      400,00
      7521       1250    625,00      312,50
         :          :         :           : 
There are no stored PL/SQL objects in the data dictionary after query execution - the PL/SQL code is being compiled and executed in memory only. So, with this new feature, SQL queries can be much more powerful.
Given this, I see all the security questions coming up. "In an application with SQL injection vulnerabilities, it seems that an attacker now can inject not only SQL fragments, but also PL/SQL-Code - this is much more dangerous". But the Developers thought about this: Adding PL/SQL to the WITH clause is only allowed in the top-level query. In a subquery, it is forbidden, as the following example shows.
select * from (
  with
    function half_sal(p_sal in number) return number 
    is 
    begin 
      return p_sal/2; 
    end;
    function quarter_sal(p_sal in number) return number
    is 
    begin 
      return p_sal/4; 
    end;
  select empno, sal, half_sal(sal) half_sal, quarter_sal(sal) quarter_sal 
  from emp
)
/

SQL> @plsql-with.sql
  with
  *
ERROR at line 2:
ORA-32034: unsupported use of WITH clause
We can therefore prevent the injection of PL/SQL code into a SQL query very effectivley by "framing" our query as subquery into a "SELECT * FROM (...)" expression. Developers, who though need PL/SQL within a subquery (and who are sure that there is no risk), can enable it by using the WITH_PLSQL hint in the top-level query - as follows ...
select /*+ WITH_PLSQL*/ * from (
  with
    function half_sal(p_sal in number) return number 
    is 
    begin 
      return p_sal/2; 
    end;
    function quarter_sal(p_sal in number) return number
    is 
    begin 
      return p_sal/4; 
    end;
  select empno, sal, half_sal(sal) half_sal, quarter_sal(sal) quarter_sal 
  from emp
)
/

     EMPNO        SAL  HALF_SAL QUARTER_SAL
---------- ---------- --------- -----------
      7369        800    400,00      200,00
      7499       1600    800,00      400,00
      7521       1250    625,00      312,50
         :          :         :           : 
The WITH_PLSQL hint is always necessary, when the SQL statement is a DML command (INSERT, UPDATE, DELETE). More information about this feature is contained in the documentation - SQL Language Reference.

1 Kommentar:

Martin Preiss hat gesagt…

Hallo Carsten,

ein schönes Feature, allerdings scheinen ein paar Optimierungen damit noch nicht so recht zu funktionieren: so führt etwa die Definition einer with-clause-function als deterministic offenbar nicht zu einer Reduzierung der erforderlichen Aufrufe, wie Jonathan Lewis gezeigt hat: http://jonathanlewis.wordpress.com/2013/07/01/12c-determinism/

Beliebte Postings