Tuesday, 14 January 2014

PL/SQL - Dynamic SQL

#=======================================================================
# Writer          : Lokanadham Thandlam
#=======================================================================
--PL/SQL Provides two ways to write dynamic SQL:
--Native dynamic SQL, a PL/SQL language (that is, native) feature for building and running dynamic SQL statements
--DBMS_SQL package, an API for building, running, and describing dynamic SQL statements.


--Example 7–1 Invoking Subprogram from Dynamic PL/SQL Block
-- Subprogram that dynamic PL/SQL block invokes:

CREATE OR REPLACE PROCEDURE create_dept (deptid   IN OUT NUMBER,
                                         dname    IN     VARCHAR2,
                                         mgrid    IN     NUMBER,
                                         locid    IN     NUMBER)
   AUTHID DEFINER
AS
BEGIN
   deptid := departments_seq.NEXTVAL;

   INSERT INTO departments (department_id,
                            department_name,
                            manager_id,
                            location_id)
        VALUES (deptid,
                dname,
                mgrid,
                locid);
END;
/

DECLARE
   plsql_block   VARCHAR2 (500);
   new_deptid    NUMBER (4);
   new_dname     VARCHAR2 (30) := 'Advertising';
   new_mgrid     NUMBER (6) := 200;
   new_locid     NUMBER (4) := 1700;
BEGIN
   -- Dynamic PL/SQL block invokes subprogram:
   plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';

   /* Specify bind variables in USING clause.
   Specify mode for first parameter.
   Modes of other parameters are correct by default. */
   EXECUTE IMMEDIATE plsql_block
      USING IN OUT new_deptid,
            new_dname,
            new_mgrid,
            new_locid;
END;
/


--Example 7–2 Dynamically Invoking Subprogram WITH BOOLEAN Formal Parameter

CREATE OR REPLACE PROCEDURE p (x BOOLEAN)
   AUTHID DEFINER
AS
BEGIN
   IF x
   THEN
      DBMS_OUTPUT.PUT_LINE ('x is true');
   END IF;
END;
/

DECLARE
   dyn_stmt   VARCHAR2 (200);
   b          BOOLEAN := TRUE;
BEGIN
   dyn_stmt := 'BEGIN p(:x); END;';

   EXECUTE IMMEDIATE dyn_stmt USING b;
END;
/



--Example 7–3 Dynamically Invoking Subprogram WITH RECORD Formal Parameter

CREATE OR REPLACE PACKAGE pkg
   AUTHID DEFINER
AS
   TYPE rec IS RECORD
   (
      n1   NUMBER,
      n2   NUMBER
   );

   PROCEDURE p (x OUT rec, y NUMBER, z NUMBER);
END pkg;
/

CREATE OR REPLACE PACKAGE BODY pkg
AS
   PROCEDURE p (x OUT rec, y NUMBER, z NUMBER)
   AS
   BEGIN
      x.n1 := y;
      x.n2 := z;
   END p;
END pkg;
/

DECLARE
   r         pkg.rec;
   dyn_str   VARCHAR2 (3000);
BEGIN
   dyn_str := 'BEGIN pkg.p(:x, 6, 8); END;';

   EXECUTE IMMEDIATE dyn_str USING OUT r;

   DBMS_OUTPUT.PUT_LINE ('r.n1 = ' || r.n1);
   DBMS_OUTPUT.PUT_LINE ('r.n2 = ' || r.n2);
END;



--Example 7–4 Dynamically Invoking Subprogram WITH Assoc. ARRAY Formal Parameter

CREATE OR REPLACE PACKAGE pkg
   AUTHID DEFINER
AS
   TYPE number_names IS TABLE OF VARCHAR2 (5)
                           INDEX BY PLS_INTEGER;

   PROCEDURE print_number_names (x number_names);
END pkg;
/

CREATE OR REPLACE PACKAGE BODY pkg
AS
   PROCEDURE print_number_names (x number_names)
   IS
   BEGIN
      FOR i IN x.FIRST .. x.LAST
      LOOP
         DBMS_OUTPUT.PUT_LINE (x (i));
      END LOOP;
   END;
END pkg;
/

DECLARE
   digit_names   pkg.number_names;
   dyn_stmt      VARCHAR2 (3000);
BEGIN
   digit_names (0) := 'zero';
   digit_names (1) := 'one';
   digit_names (2) := 'two';
   digit_names (3) := 'three';
   digit_names (4) := 'four';
   digit_names (5) := 'five';
   digit_names (6) := 'six';
   digit_names (7) := 'seven';
   digit_names (8) := 'eight';
   digit_names (9) := 'nine';
   dyn_stmt := 'BEGIN pkg.print_number_names(:x); END;';

   EXECUTE IMMEDIATE dyn_stmt USING digit_names;
END;
/



--Example 7–5 Dynamically Invoking Subprogram WITH NESTED TABLE Formal Parameter

CREATE OR REPLACE PACKAGE pkg
   AUTHID DEFINER
AS
   TYPE names IS TABLE OF VARCHAR2 (10);

   PROCEDURE print_names (x names);
END pkg;
/

CREATE OR REPLACE PACKAGE BODY pkg
AS
   PROCEDURE print_names (x names)
   IS
   BEGIN
      FOR i IN x.FIRST .. x.LAST
      LOOP
         DBMS_OUTPUT.PUT_LINE (x (i));
      END LOOP;
   END;
END pkg;
/

DECLARE
   fruits     pkg.names;
   dyn_stmt   VARCHAR2 (3000);
BEGIN
   fruits := pkg.names ('apple', 'banana', 'cherry');
   dyn_stmt := 'BEGIN pkg.print_names(:x); END;';

   EXECUTE IMMEDIATE dyn_stmt USING fruits;
END;
/



--Example 7–6 Dynamically Invoking Subprogram WITH VARRAY Formal Parameter

CREATE OR REPLACE PACKAGE pkg
   AUTHID DEFINER
AS
   TYPE foursome IS VARRAY (4) OF VARCHAR2 (5);

   PROCEDURE print_foursome (x foursome);
END pkg;
/

CREATE OR REPLACE PACKAGE BODY pkg
AS
   PROCEDURE print_foursome (x foursome)
   IS
   BEGIN
      IF x.COUNT = 0
      THEN
         DBMS_OUTPUT.PUT_LINE ('Empty');
      ELSE
         FOR i IN x.FIRST .. x.LAST
         LOOP
            DBMS_OUTPUT.PUT_LINE (x (i));
         END LOOP;
      END IF;
   END;
END pkg;
/

DECLARE
   directions   pkg.foursome;
   dyn_stmt     VARCHAR2 (3000);
BEGIN
   directions :=
      pkg.foursome ('north',
                    'south',
                    'east',
                    'west');
   dyn_stmt := 'BEGIN pkg.print_foursome(:x); END;';

   EXECUTE IMMEDIATE dyn_stmt USING directions;
END;
/


--Example 7–7 Uninitialized VARIABLE Represents NULL IN USING Clause

CREATE TABLE employees_temp
AS
   SELECT * FROM EMPLOYEES;

DECLARE
   a_null   CHAR (1);                 -- Set to NULL automatically at run time
BEGIN
   EXECUTE IMMEDIATE 'UPDATE employees_temp SET commission_pct = :x'
      USING a_null;
END;
/


--Example 7–8 Native Dynamic SQL WITH OPEN FOR, FETCH, AND CLOSE STATEMENTS

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;

   v_emp_cursor   EmpCurTyp;
   emp_record     employees%ROWTYPE;
   v_stmt_str     VARCHAR2 (200);
   v_e_job        employees.job%TYPE;
BEGIN
   -- Dynamic SQL statement with placeholder:
   v_stmt_str := 'SELECT * FROM employees WHERE job_id = :j';

   -- Open cursor & specify bind variable in USING clause:
   OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';

   -- Fetch rows from result set one at a time:
   LOOP
      FETCH v_emp_cursor INTO emp_record;

      EXIT WHEN v_emp_cursor%NOTFOUND;
   END LOOP;

   -- Close cursor:
   CLOSE v_emp_cursor;
END;



--Example 7–9 Querying A Collection WITH Native Dynamic SQL

CREATE OR REPLACE PACKAGE pkg
   AUTHID DEFINER
AS
   TYPE rec IS RECORD
   (
      f1   NUMBER,
      f2   VARCHAR2 (30)
   );

   TYPE mytab IS TABLE OF rec
                    INDEX BY PLS_INTEGER;
END;
/

DECLARE
   v1   pkg.mytab;                                    -- collection of records
   v2   pkg.rec;
   c1   SYS_REFCURSOR;
BEGIN
   OPEN c1 FOR 'SELECT * FROM TABLE(:1)' USING v1;

   FETCH c1 INTO v2;

   CLOSE c1;

   DBMS_OUTPUT.PUT_LINE (
      'Values in record are ' || v2.f1 || ' and ' || v2.f2);
END;
/



--Example 7–10 Repeated Placeholder Names IN Dynamic PL/SQL BLOCK

CREATE PROCEDURE calc_stats (w    NUMBER,
                             x    NUMBER,
                             y    NUMBER,
                             z    NUMBER)
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE (w + x + y + z);
END;
/

DECLARE
   a             NUMBER := 4;
   b             NUMBER := 7;
   plsql_block   VARCHAR2 (100);
BEGIN
   plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END;';

   EXECUTE IMMEDIATE plsql_block USING a, b;         -- calc_stats(a, a, b, a)
END;
/



--Example 7–11 DBMS_SQL.RETURN_RESULT PROCEDURE

CREATE OR REPLACE PROCEDURE p
   AUTHID DEFINER
AS
   c1   SYS_REFCURSOR;
   c2   SYS_REFCURSOR;
BEGIN
   OPEN c1 FOR
      SELECT first_name, last_name
        FROM employees
       WHERE employee_id = 176;

   DBMS_SQL.RETURN_RESULT (c1);

   -- Now p cannot access the result.
   OPEN c2 FOR
      SELECT city, state_province
        FROM locations
       WHERE country_id = 'AU';

   DBMS_SQL.RETURN_RESULT (c2);
-- Now p cannot access the result.
END;
/

BEGIN
   p;
END;
/



--Example 7–12 DBMS_SQL.GET_NEXT_RESULT PROCEDURE

CREATE OR REPLACE PROCEDURE get_employee_info (id IN VARCHAR2)
   AUTHID DEFINER
AS
   rc   SYS_REFCURSOR;
BEGIN
   -- Return employee info
   OPEN rc FOR
      SELECT first_name,
             last_name,
             email,
             phone_number
        FROM employees
       WHERE employee_id = id;

   DBMS_SQL.RETURN_RESULT (rc);

   -- Return employee job history
   OPEN RC FOR
        SELECT job_title, start_date, end_date
          FROM job_history jh, jobs j
         WHERE jh.employee_id = id AND jh.job_id = j.job_id
      ORDER BY start_date DESC;

   DBMS_SQL.RETURN_RESULT (rc);
END;
/

<<main>>
DECLARE
   c              INTEGER;
   rc             SYS_REFCURSOR;
   n              NUMBER;
   first_name     VARCHAR2 (20);
   last_name      VARCHAR2 (25);
   email          VARCHAR2 (25);
   phone_number   VARCHAR2 (20);
   job_title      VARCHAR2 (35);
   start_date     DATE;
   end_date       DATE;
BEGIN
   c := DBMS_SQL.OPEN_CURSOR (TRUE);
   DBMS_SQL.PARSE (c, 'BEGIN get_employee_info(:id); END;', DBMS_SQL.NATIVE);
   DBMS_SQL.BIND_VARIABLE (c, ':id', 176);
   n := DBMS_SQL.EXECUTE (c);
   -- Get employee info
   DBMS_SQL.get_next_result (c, rc);

   FETCH rc
   INTO first_name, last_name, email, phone_number;

   DBMS_OUTPUT.PUT_LINE ('Employee: ' || first_name || ' ' || last_name);
   DBMS_OUTPUT.PUT_LINE ('Email: ' || email);
   DBMS_OUTPUT.PUT_LINE ('Phone: ' || phone_number);
   -- Get employee job history
   DBMS_OUTPUT.PUT_LINE ('Titles:');
   DBMS_SQL.GET_NEXT_RESULT (c, rc);

   LOOP
      FETCH rc
      INTO job_title, start_date, end_date;

      EXIT WHEN rc%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE (
         '- ' || job_title || ' (' || start_date || ' - ' || end_date || ')');
   END LOOP;

   DBMS_SQL.CLOSE_CURSOR (c);
END main;
/



--Example 7–13 Switching FROM DBMS_SQL PACKAGE TO Native Dynamic SQL

CREATE OR REPLACE TYPE vc_array IS TABLE OF VARCHAR2 (200);
/

CREATE OR REPLACE TYPE numlist IS TABLE OF NUMBER;
/

CREATE OR REPLACE PROCEDURE do_query_1 (placeholder    vc_array,
                                        bindvars       vc_array,
                                        sql_stmt       VARCHAR2)
   AUTHID DEFINER
IS
   TYPE curtype IS REF CURSOR;

   src_cur     curtype;
   curid       NUMBER;
   bindnames   vc_array;
   empnos      numlist;
   depts       numlist;
   ret         NUMBER;
   ISOPEN      BOOLEAN;
BEGIN
   -- Open SQL cursor number:
   curid := DBMS_SQL.OPEN_CURSOR;
   -- Parse SQL cursor number:
   DBMS_SQL.PARSE (curid, sql_stmt, DBMS_SQL.NATIVE);
   bindnames := placeholder;

   -- Bind variables:
   FOR i IN 1 .. bindnames.COUNT
   LOOP
      DBMS_SQL.BIND_VARIABLE (curid, bindnames (i), bindvars (i));
   END LOOP;

   -- Run SQL cursor number:
   ret := DBMS_SQL.EXECUTE (curid);
   -- Switch from DBMS_SQL to native dynamic SQL:
   src_cur := DBMS_SQL.TO_REFCURSOR (curid);

   FETCH src_cur
   BULK COLLECT INTO empnos, depts;

   -- This would cause an error because curid was converted to a REF CURSOR:
   -- isopen := DBMS_SQL.IS_OPEN(curid);
   CLOSE src_cur;
END;
/



--Example 7–14 Switching FROM Native Dynamic SQL TO DBMS_SQL PACKAGE

CREATE OR REPLACE PROCEDURE do_query_2 (sql_stmt VARCHAR2)
   AUTHID DEFINER
IS
   TYPE curtype IS REF CURSOR;

   src_cur   curtype;
   curid     NUMBER;
   desctab   DBMS_SQL.DESC_TAB;
   colcnt    NUMBER;
   namevar   VARCHAR2 (50);
   numvar    NUMBER;
   datevar   DATE;
   empno     NUMBER := 100;
BEGIN
   -- sql_stmt := SELECT ... FROM employees WHERE employee_id = :b1';
   -- Open REF CURSOR variable:
   OPEN src_cur FOR sql_stmt USING empno;

   -- Switch from native dynamic SQL to DBMS_SQL package:
   curid := DBMS_SQL.TO_CURSOR_NUMBER (src_cur);
   DBMS_SQL.DESCRIBE_COLUMNS (curid, colcnt, desctab);

   -- Define columns:
   FOR i IN 1 .. colcnt
   LOOP
      IF desctab (i).col_type = 2
      THEN
         DBMS_SQL.DEFINE_COLUMN (curid, i, numvar);
      ELSIF desctab (i).col_type = 12
      THEN
         DBMS_SQL.DEFINE_COLUMN (curid, i, datevar);
      -- statements
      ELSE
         DBMS_SQL.DEFINE_COLUMN (curid,
                                 i,
                                 namevar,
                                 50);
      END IF;
   END LOOP;

   -- Fetch rows with DBMS_SQL package:
   WHILE DBMS_SQL.FETCH_ROWS (curid) > 0
   LOOP
      FOR i IN 1 .. colcnt
      LOOP
         IF (desctab (i).col_type = 1)
         THEN
            DBMS_SQL.COLUMN_VALUE (curid, i, namevar);
         ELSIF (desctab (i).col_type = 2)
         THEN
            DBMS_SQL.COLUMN_VALUE (curid, i, numvar);
         ELSIF (desctab (i).col_type = 12)
         THEN
            DBMS_SQL.COLUMN_VALUE (curid, i, datevar);
         -- statements
         END IF;
      END LOOP;
   END LOOP;

   DBMS_SQL.CLOSE_CURSOR (curid);
END;
/



--Example 7–15 Setup FOR SQL Injection Examples
DROP TABLE secret_records;

CREATE TABLE secret_records
(
   user_name      VARCHAR2 (9),
   service_type   VARCHAR2 (12),
   VALUE          VARCHAR2 (30),
   date_created   DATE
);

INSERT INTO secret_records (user_name,
                            service_type,
                            VALUE,
                            date_created)
     VALUES ('Andy',
             'Waiter',
             'Serve dinner at Cafe Pete',
             SYSDATE);

INSERT INTO secret_records (user_name,
                            service_type,
                            VALUE,
                            date_created)
     VALUES ('Chuck',
             'Merger',
             'Buy company XYZ',
             SYSDATE);



--Example 7–16 PROCEDURE Vulnerable TO STATEMENT Modification

CREATE       vulnerable procedure:
CREATE OR REPLACE PROCEDURE get_record (
user_name IN VARCHAR2,
service_type IN VARCHAR2,
rec OUT VARCHAR2
) AUTHID DEFINER
IS
query VARCHAR2(4000);

BEGIN
   -- Following SELECT statement is vulnerable to modification
   -- because it uses concatenation to build WHERE clause.
   query :=
         'SELECT value FROM secret_records WHERE user_name='''
      || user_name
      || ''' AND service_type='''
      || service_type
      || '''';
   DBMS_OUTPUT.PUT_LINE ('Query: ' || query);

   EXECUTE IMMEDIATE query INTO rec;

   DBMS_OUTPUT.PUT_LINE ('Rec: ' || rec);
END;
/



--Demonstrate procedure without SQL injection:
SET SERVEROUTPUT ON;

DECLARE
   record_value   VARCHAR2 (4000);
BEGIN
   get_record ('Andy', 'Waiter', record_value);
END;
/

--Example of statement modification:

DECLARE
   record_value   VARCHAR2 (4000);
BEGIN
   get_record ('Anybody '' OR service_type=''Merger''--',
               'Anything',
               record_value);
END;
/


--Example 7–17 PROCEDURE Vulnerable TO STATEMENT Injection

CREATE       vulnerable procedure:
CREATE OR REPLACE PROCEDURE p (
user_name IN VARCHAR2,
service_type IN VARCHAR2
) AUTHID DEFINER
IS
block1 VARCHAR2(4000);

BEGIN
   -- Following block is vulnerable to statement injection
   -- because it is built by concatenation.
   block1 :=
         'BEGIN
DBMS_OUTPUT.PUT_LINE(''user_name: '
      || user_name
      || ''');'
      || 'DBMS_OUTPUT.PUT_LINE(''service_type: '
      || service_type
      || ''');
END;';
   DBMS_OUTPUT.PUT_LINE ('Block1: ' || block1);

   EXECUTE IMMEDIATE block1;
END;
/

--Demonstrate procedure without SQL injection:
SET SERVEROUTPUT ON;

BEGIN
   p ('Andy', 'Waiter');
END;
/


--Example of statement modification:

BEGIN
   p ('Anybody',
      'Anything'');
DELETE FROM secret_records WHERE service_type=INITCAP(''Merger');
END;
/

--Example 7–18 PROCEDURE Vulnerable TO SQL Injection THROUGH DATA TYPE Conversion

SELECT * FROM secret_records;

RESULT:
USER_NAME SERVICE_TYPE VALUE DATE_CREATE
--------- ------------ ------------------------------ -----------
Andy Waiter Serve dinner AT Cafe Pete 28-APR-2010
Chuck Merger Buy company XYZ 28-APR-2010

CREATE       vulnerable procedure:
-- Return records not older than a month
CREATE OR REPLACE PROCEDURE get_recent_record (
user_name IN VARCHAR2,
service_type IN VARCHAR2,
rec OUT VARCHAR2
) AUTHID DEFINER
IS
query VARCHAR2(4000);

BEGIN
   /* Following SELECT statement is vulnerable to modification
   because it uses concatenation to build WHERE clause
   and because SYSDATE depends on the value of NLS_DATE_FORMAT. */
   query :=
         'SELECT value FROM secret_records WHERE user_name='''
      || user_name
      || ''' AND service_type='''
      || service_type
      || ''' AND date_created>'''
      || (SYSDATE - 30)
      || '''';
   DBMS_OUTPUT.PUT_LINE ('Query: ' || query);

   EXECUTE IMMEDIATE query INTO rec;

   DBMS_OUTPUT.PUT_LINE ('Rec: ' || rec);
END;
/

--Demonstrate procedure without SQL injection:
SET SERVEROUTPUT ON;
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';

DECLARE
   record_value   VARCHAR2 (4000);
BEGIN
   get_recent_record ('Andy', 'Waiter', record_value);
END;
/

--Example of statement modification:
ALTER SESSION SET NLS_DATE_FORMAT='"'' OR service_type=''Merger"';

DECLARE
   record_value   VARCHAR2 (4000);
BEGIN
   get_recent_record ('Anybody', 'Anything', record_value);
END;
/



--Example 7–19 Bind Variables Guarding Against SQL Injection

CREATE       invulnerable procedure:
CREATE OR REPLACE PROCEDURE get_record_2 (
user_name IN VARCHAR2,
service_type IN VARCHAR2,
rec OUT VARCHAR2
) AUTHID DEFINER
IS
query VARCHAR2(4000);

BEGIN
   query := 'SELECT value FROM secret_records
WHERE user_name=:a
AND service_type=:b';
   DBMS_OUTPUT.PUT_LINE ('Query: ' || query);

   EXECUTE IMMEDIATE query INTO rec USING user_name, service_type;

   DBMS_OUTPUT.PUT_LINE ('Rec: ' || rec);
END;
/

--Demonstrate procedure without SQL injection:
SET SERVEROUTPUT ON;

DECLARE
   record_value   VARCHAR2 (4000);
BEGIN
   get_record_2 ('Andy', 'Waiter', record_value);
END;
/

--Try statement modification:

DECLARE
   record_value   VARCHAR2 (4000);
BEGIN
   get_record_2 ('Anybody '' OR service_type=''Merger''--',
                 'Anything',
                 record_value);
END;
/



--Example 7–20 VALIDATION Checks Guarding Against SQL Injection

CREATE OR REPLACE PROCEDURE raise_emp_salary (COLUMN_VALUE    NUMBER,
                                              emp_column      VARCHAR2,
                                              amount          NUMBER)
   AUTHID DEFINER
IS
   v_column   VARCHAR2 (30);
   sql_stmt   VARCHAR2 (200);
BEGIN
   -- Check validity of column name that was given as input:
   SELECT column_name
     INTO v_column
     FROM USER_TAB_COLS
    WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = emp_column;

   sql_stmt :=
         'UPDATE employees SET salary = salary + :1 WHERE '
      || DBMS_ASSERT.ENQUOTE_NAME (v_column, FALSE)
      || ' = :2';

   EXECUTE IMMEDIATE sql_stmt USING amount, COLUMN_VALUE;

   -- If column name is valid:
   IF SQL%ROWCOUNT > 0
   THEN
      DBMS_OUTPUT.PUT_LINE (
         'Salaries were updated for: ' || emp_column || ' = ' || COLUMN_VALUE);
   END IF;
-- If column name is not valid:
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.PUT_LINE ('Invalid Column: ' || emp_column);
END raise_emp_salary;
/

DECLARE
   plsql_block   VARCHAR2 (500);
BEGIN
   -- Invoke raise_emp_salary from a dynamic PL/SQL block:
   plsql_block := 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;';

   EXECUTE IMMEDIATE plsql_block USING 110, 'DEPARTMENT_ID', 10;

   -- Invoke raise_emp_salary from a dynamic SQL statement:
   EXECUTE IMMEDIATE 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;'
      USING 112, 'EMPLOYEE_ID', 10;
END;
/


--Example 7–21 Explicit Format Models Guarding Against SQL Injection

CREATE       invulnerable procedure:
-- Return records not older than a month
CREATE OR REPLACE PROCEDURE get_recent_record (
user_name IN VARCHAR2,
service_type IN VARCHAR2,
rec OUT VARCHAR2
) AUTHID DEFINER
IS
query VARCHAR2(4000);

BEGIN
   /* Following SELECT statement is vulnerable to modification
   because it uses concatenation to build WHERE clause. */
   query :=
         'SELECT value FROM secret_records WHERE user_name='''
      || user_name
      || ''' AND service_type='''
      || service_type
      || ''' AND date_created> DATE '''
      || TO_CHAR (SYSDATE - 30, 'YYYY-MM-DD')
      || '''';
   DBMS_OUTPUT.PUT_LINE ('Query: ' || query);

   EXECUTE IMMEDIATE query INTO rec;

   DBMS_OUTPUT.PUT_LINE ('Rec: ' || rec);
END;
/

--Try statement modification:
ALTER SESSION SET NLS_DATE_FORMAT='"'' OR service_type=''Merger"';

DECLARE
   record_value   VARCHAR2 (4000);
BEGIN
   get_recent_record ('Anybody', 'Anything', record_value);
END;

/
Posted by at
Labels: ,