The Database Hacker's Handbook: Defending Database Servers

Executing User-Supplied Queries with DBMS_SQL

The DBMS_SQL default package allows SQL to be dynamically executed. Owned by SYS it has been defined with the AUTHID CURRENT_USER keyword so it runs with the privileges of the invoker. This protects the DBMS_SQL procedures against direct attacks, but if called from another PL/SQL program that uses definer rights it can be problematic. Before we get to how the DBMS_SQL procedures can be dangerous, let's examine how it works. Consider the following code:

DECLARE
C NUMBER;
R NUMBER;
STMT VARCHAR2(200);
BEGIN
      STMT:='SELECT 1 FROM DUAL';
      C :=DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(C, STMT, DBMS_SQL.NATIVE);
      R := DBMS_SQL.EXECUTE_AND_FETCH(C);
      DBMS_SQL.CLOSE_CURSOR(C);
END;

Here a cursor, C, is opened using the OPEN_CURSOR function. The SQL statement, ‘SELECT 1 FROM DUAL’, is then parsed using DBMS_SQL.PARSE(C, STMT, DBMS_SQL.NATIVE). Once parsed, the query is executed using DBMS_SQL.EXECUTE_AND_FETCH(C). Alternatively, the DBMS_SQL.EXECUTE(C) function could be called followed by a call to DBMS_SQL.FETCH_ROWS(C). Finally, the cursor is closed with DBMS_SQL.CLOSE_CURSOR(C). Any query can be executed by these procedures. This includes calls to GRANT, CREATE, and ALTER. When an attempt is made to run such a query using DBMS_SQL, however, an error is returned.

ORA-01003: no statement parsed
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1216
ORA-06512: at "SYS.DBMS_SQL", line 334

It has, however, succeeded. To see this in action, run the following queries: ...