| Security Advisory |
| REF CURSORS in Oracle make it possible to define cursors that can be passed as parameters and to define cursors using dynamic SQL. REF CURSORS are powerful tools that belong in the toolbox of every PL/SQL Developer. But with that strong endorsement comes the need for a health dose of preventative medicine: Weakly Typed REF CURSORS open up all of the same security concerns as does the DBMS_SQL package and Native Dynamic SQL. |
| |
| Recommended Security Rules |
NEVER
- Use Weakly Typed Ref Cursors without using the DBMS_ASSERT package to sanitize the inputs.
WITH GREAT CARE
- Test any code using Weakly Typed REF CURSORS to assure that a SQL Injection attack is not possible.
CAUTIONS
- In addition to the use of DBMS_ASSERT use Bind Variables whenever possible.
- Do not rely on the expertise of anyone that does not know how to craft and deploy a SQL Injection attack to validate the code.
|
| |
| How Oracle Works |
There are two different types of REF CURSORS, strongly typed which are not dynamic and weakly typed that are. Here are examples of each.
After this section everything on this page will focus solely on the Weakly Typed version as they are the ones that introduce concerns about SQL Injection and the inability, as with Native Dynamic SQL, to identify objects they reference using the DBA_DEPENDENCIES view.
From the security standpoint treat Weakly Typed REF CURSORS as just another form of Native Dynamic SQL. One that has shares both its strengths and its security issues. |
Strongly Typed REF CURSOR
The REF CURSOR is the OUT parameter of the procedure "CHILD" |
CREATE OR REPLACE PACKAGE strongly_typed IS
TYPE return_cur IS REF CURSOR RETURN all_tables%ROWTYPE;
PROCEDURE child(p_return_rec OUT return_cur);
PROCEDURE parent(p_NumRecs PLS_INTEGER);
END strongly_typed;
/
CREATE OR REPLACE PACKAGE BODY strongly_typed IS
PROCEDURE child(p_return_rec OUT return_cur) IS
BEGIN
OPEN p_return_rec FOR
SELECT * FROM all_tables;
END child;
--==================================================
PROCEDURE parent(p_NumRecs PLS_INTEGER) IS
p_retcur return_cur;
at_rec all_tables%ROWTYPE;
BEGIN
child(p_retcur);
FOR i IN 1 .. p_NumRecs LOOP
FETCH p_retcur
INTO at_rec;
dbms_output.put_line(at_rec.table_name ||
' - ' || at_rec.tablespace_name ||
' - ' || TO_CHAR(at_rec.initial_extent) ||
' - ' || TO_CHAR(at_rec.next_extent));
END LOOP;
END parent;
END strongly_typed;
/
set serveroutput on
exec strongly_typed.parent(1)
exec strongly_typed.parent(8) |
Weakly Typed REF CURSOR
In this example the REF CURSOR is defined as
p_retcur SYS_REFCURSOR.
Using SYS_REFCURSOR is not required but it is the most commonly used technique. |
CREATE OR REPLACE PROCEDURE child(
p_NumRecs IN PLS_INTEGER,
p_return_cur OUT SYS_REFCURSOR)
IS
BEGIN
OPEN p_return_cur FOR
'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs;
END child;
/
CREATE OR REPLACE PROCEDURE parent(pNumRecs VARCHAR2) IS
p_retcur SYS_REFCURSOR;
at_rec all_tables%ROWTYPE;
BEGIN
child(pNumRecs, p_retcur);
FOR i IN 1 .. pNumRecs LOOP
FETCH p_retcur INTO at_rec;
dbms_output.put_line(at_rec.table_name ||
' - ' || at_rec.tablespace_name ||
' - ' || TO_CHAR(at_rec.initial_extent) ||
' - ' || TO_CHAR(at_rec.next_extent));
END LOOP;
END parent;
/
set serveroutput on
exec parent(1)
exec parent(17) |
Weakly Typed REF CURSOR
In this example SYS_REFCURSOR is not used and the cursor is defined by string concatenation |
CREATE OR REPLACE PROCEDURE child(
p_NumRecs IN PLS_INTEGER,
p_return_cur OUT SYS_REFCURSOR)
IS
BEGIN
OPEN p_return_cur FOR
'SELECT * FROM all_tables WHERE rownum <= '
|| p_NumRecs;
END child;
/ |
| |
| REF CURSOR Demos |
REF CURSOR as the OUT parameter of a stored procedure
The REF CURSOR in this demo is defined in two paces. First in the input parameter of the procedure pass_ref_cur and also in the declaration section of the anonymous block that calls pass_ref_cur.
The name of the table, employees, is defined within a string that could be created dynamically, perhaps creating the SQL statement as the result of parameters passed resulting in SQL that has never been tested being executed for the first time in production..
Code like this demands the use of DBMS_ASSERT, demands that inputs be sanitized, demands well crafted exception handling, and hides object dependencies. |
CREATE TABLE employees (
empid NUMBER(5),
empname VARCHAR2(30));
INSERT INTO employees (empid, empname) VALUES (1, 'Dan Morgan');
INSERT INTO employees (empid, empname) VALUES (2, 'Hans Forbrich');
INSERT INTO employees (empid, empname) VALUES (3, 'Caleb Small');
COMMIT;
CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor IN SYS_REFCURSOR) IS
TYPE array_t IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;
rec_array array_t;
BEGIN
FETCH p_cursor BULK COLLECT INTO rec_array;
FOR i IN rec_array.FIRST .. rec_array.LAST
LOOP
dbms_output.put_line(rec_array(i));
END LOOP;
END pass_ref_cur;
/
set serveroutput on
DECLARE
rec_array SYS_REFCURSOR;
BEGIN
OPEN rec_array FOR
'SELECT empname FROM employees';
pass_ref_cur(rec_array);
CLOSE rec_array;
END;
/ |
VARRAY REF CURSORS
This example demonstrates how to use a REF CURSOR to pass a VARRAY and disassemble it on the receiving end |
CREATE OR REPLACE TYPE Project AUTHID DEFINER AS OBJECT (
project_no NUMBER(2),
title VARCHAR2(35),
cost NUMBER(7,2));
/
CREATE OR REPLACE TYPE ProjectList AS VARRAY(50) OF Project;
/
CREATE TABLE department (
dept_id NUMBER(2),
dname VARCHAR2(15),
budget NUMBER(11,2),
projects ProjectList);
CREATE OR REPLACE PROCEDURE varray_refcur(outparm OUT NOCOPY SYS_REFCURSOR)
AUTHID DEFINER IS
BEGIN
OPEN outparm FOR
SELECT *
FROM department;
END varray_refcur;
/
INSERT INTO department
VALUES(30, 'Accounting', 1205700,
ProjectList (Project(1, 'Design New Expense Report', 3250),
Project(2, 'Outsource Payroll', 12350),
Project(3, 'Evaluate Merger Proposal', 2750),
Project(4, 'Audit Accounts Payable', 1425)));
INSERT INTO department
VALUES(50, 'Maintenance', 925300,
ProjectList (Project(1, 'Repair Leak in Roof', 2850),
Project(2, 'Install New Door Locks', 1700),
Project(3, 'Wash Front Windows', 975),
Project(4, 'Repair Faulty Wiring', 1350),
Project(5, 'Winterize Cooling System', 1125)));
INSERT INTO department
VALUES(60, 'Security', 750400,
ProjectList (Project(1, 'Issue New Employee Badges', 13500),
Project(2, 'Find Missing IC Chips', 2750),
Project(3, 'Upgrade Alarm System', 3350),
Project(4, 'Inspect Emergency Exits', 1900)));
COMMIT;
set serveroutput on
DECLARE
drow SYS_REFCURSOR;
drec department%ROWTYPE;
BEGIN
-- call the proc that returns the weakly typed ref cursor
varray_refcur(drow);
FOR i IN 1 .. 3 LOOP
FETCH drow INTO drec;
dbms_output.put_line('Dept#: ' || drec.dept_id);
dbms_output.put_line('DName: ' || drec.dname);
dbms_output.put_line('Budget:' || drec.budget);
dbms_output.put_line('Proj#: ' || drec.projects(i).project_no);
dbms_output.put_line('Title: ' || drec.projects(i).title);
dbms_output.put_line('PCost: ' || drec.projects(i).cost);
dbms_output.put_line('------------------------------');
END LOOP;
END;
/ |