The Database Hacker's Handbook: Defending Database Servers
by David Litchfield, Chris Anley, John Heasman, Bill Grindlay
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: ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Read now
Unlock full access