7 Using Dynamic SQL

Dynamic SQL is a programming methodology for generating and executing SQL statements at run time. It is useful when writing general-purpose and flexible programs like ad hoc query systems, when writing programs that must execute DDL statements, or when you do not know at compilation time the full text of a SQL statement or the number or datatypes of its input and output variables.

PL/SQL provides two ways to write dynamic SQL:

Native dynamic SQL, a PL/SQL language (that is, native) feature for building and executing dynamic SQL statements
DBMS_SQL package, an API for building, executing, and describing dynamic SQL statements

Native dynamic SQL code is easier to read and write than equivalent code that uses the DBMS_SQL package, and runs noticeably faster (especially when it can be optimized by the compiler). However, to write native dynamic SQL code, you must know at compile time the number and datatypes of the input and output variables of the dynamic SQL statement. If you do not know this information at compile time, you must use the DBMS_SQL package.

When you need both the DBMS_SQL package and native dynamic SQL, you can switch between them, using the "DBMS_SQL.TO_REFCURSOR Function" and "DBMS_SQL.TO_CURSOR_NUMBER Function".

Topics:

When You Need Dynamic SQL
Using Native Dynamic SQL
Using DBMS_SQL Package
Avoiding SQL Injection in PL/SQL

When You Need Dynamic SQL

In PL/SQL, you need dynamic SQL in order to execute the following:

SQL whose text is unknown at compile time

For example, a SELECT statement that includes an identifier that is unknown at compile time (such as a table name) or a WHERE clause in which the number of subclauses is unknown at compile time.
SQL that is not supported as static SQL

That is, any SQL construct not included in "Description of Static SQL".

If you do not need dynamic SQL, use static SQL, which has the following advantages:

Successful compilation verifies that static SQL statements reference valid database objects and that the necessary privileges are in place to access those objects.
Successful compilation creates schema object dependencies.

For information about schema object dependencies, see Oracle Database Concepts.

For information about using static SQL statements with PL/SQL, see Chapter 6, "Using Static SQL".

Using Native Dynamic SQL

Native dynamic SQL processes most dynamic SQL statements by means of the EXECUTE IMMEDIATE statement.

If the dynamic SQL statement is a SELECT statement that returns multiple rows, native dynamic SQL gives you the following choices:

Use the EXECUTE IMMEDIATE statement with the BULK COLLECT INTO clause.
Use the OPEN-FOR, FETCH, and CLOSE statements.

The SQL cursor attributes work the same way after native dynamic SQL INSERT, UPDATE, DELETE, and single-row SELECT statements as they do for their static SQL counterparts. For more information about SQL cursor attributes, see "Managing Cursors in PL/SQL".

Topics:

Using the EXECUTE IMMEDIATE Statement
Using the OPEN-FOR, FETCH, and CLOSE Statements
Repeating Placeholder Names in Dynamic SQL Statements

Using the EXECUTE IMMEDIATE Statement

The EXECUTE IMMEDIATE statement is the means by which native dynamic SQL processes most dynamic SQL statements.

If the dynamic SQL statement is self-contained (that is, if it has no placeholders for bind arguments and the only result that it can possibly return is an error), then the EXECUTE IMMEDIATE statement needs no clauses.

If the dynamic SQL statement includes placeholders for bind arguments, each placeholder must have a corresponding bind argument in the appropriate clause of the EXECUTE IMMEDIATE statement, as follows:

If the dynamic SQL statement is a SELECT statement that can return at most one row, put out-bind arguments (defines) in the INTO clause and in-bind arguments in the USING clause.
If the dynamic SQL statement is a SELECT statement that can return multiple rows, put out-bind arguments (defines) in the BULK COLLECT INTO clause and in-bind arguments in the USING clause.
If the dynamic SQL statement is a DML statement without a RETURNING INTO clause, other than SELECT, put all bind arguments in the USING clause.
If the dynamic SQL statement is a DML statement with a RETURNING INTO clause, put in-bind arguments in the USING clause and out-bind arguments in the RETURNING INTO clause.
If the dynamic SQL statement is an anonymous PL/SQL block or a CALL statement, put all bind arguments in the USING clause.

If the dynamic SQL statement invokes a subprogram, ensure that every bind argument that corresponds to a placeholder for a subprogram parameter has the same parameter mode as that subprogram parameter (as in Example 7-1) and that no bind argument has a datatype that SQL does not support (such as BOOLEAN in Example 7-2).

The USING clause cannot contain the literal NULL. To work around this restriction, use an uninitialized variable where you want to use NULL, as in Example 7-3.

For syntax details of the EXECUTE IMMEDIATE statement, see "EXECUTE IMMEDIATE Statement".

Example 7-1 Invoking a Subprogram from a Dynamic PL/SQL Block

-- Subprogram that dynamic PL/SQL block invokes:
CREATE PROCEDURE create_dept ( deptid IN OUT NUMBER,
                               dname  IN VARCHAR2,
                               mgrid  IN NUMBER,
                               locid  IN NUMBER
                             ) AS
BEGIN

deptid := departments_seq.NEXTVAL;
  INSERT INTO departments 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 arguments 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 Unsupported Datatype in Native Dynamic SQL

DECLARE
  FUNCTION f (x INTEGER)
    RETURN BOOLEAN
  AS
  BEGIN
    ...
  END f;
  dyn_stmt VARCHAR2(200);
  b1       BOOLEAN;
BEGIN
  dyn_stmt := 'BEGIN :b := f(5); END;';
  -- Fails because SQL does not support BOOLEAN datatype:
  EXECUTE IMMEDIATE dyn_stmt USING OUT b1;
END;

Example 7-3 Uninitialized Variable for 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;
/

Using the OPEN-FOR, FETCH, and CLOSE Statements

If the dynamic SQL statement represents a SELECT statement that returns multiple rows, you can process it with native dynamic SQL as follows:

Use an OPEN-FOR statement to associate a cursor variable with the dynamic SQL statement. In the USING clause of the OPEN-FOR statement, specify a bind argument for each placeholder in the dynamic SQL statement.

The USING clause cannot contain the literal NULL. To work around this restriction, use an uninitialized variable where you want to use NULL, as in Example 7-3.

For syntax details, see "OPEN-FOR Statement".
Use the FETCH statement to retrieve result set rows one at a time, several at a time, or all at once.

For syntax details, see "FETCH Statement".
Use the CLOSE statement to close the cursor variable.

For syntax details, see "CLOSE Statement".

Example 7-4 lists all employees who are managers, retrieving result set rows one at a time.

Example 7-4 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 argument 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;
/

Repeating Placeholder Names in Dynamic SQL Statements

If you repeat placeholder names in dynamic SQL statements, be aware that the way placeholders are associated with bind arguments depends on the kind of dynamic SQL statement.

Topics:

Dynamic SQL Statement is Not Anonymous Block or CALL Statement
Dynamic SQL Statement is Anonymous Block or CALL Statement

Dynamic SQL Statement is Not Anonymous Block or CALL Statement

If the dynamic SQL statement does not represent an anonymous PL/SQL block or a CALL statement, repetition of placeholder names is insignificant. Placeholders are associated with bind arguments in the USING clause by position, not by name.

For example, in the following dynamic SQL statement, the repetition of the name :x is insignificant:

sql_stmt := 'INSERT INTO payroll VALUES (:x, :x, :y, :x)';

In the corresponding USING clause, you must supply four bind arguments. They can be different; for example:

EXECUTE IMMEDIATE sql_stmt USING a, b, c, d;

The preceding EXECUTE IMMEDIATE statement executes the following SQL statement:

INSERT INTO payroll VALUES (a, b, c, d)

To associate the same bind argument with each occurrence of :x, you must repeat that bind argument; for example:

EXECUTE IMMEDIATE sql_stmt USING a, a, b, a;

The preceding EXECUTE IMMEDIATE statement executes the following SQL statement:

INSERT INTO payroll VALUES (a, a, b, a)

Dynamic SQL Statement is Anonymous Block or CALL Statement

If the dynamic SQL statement represents an anonymous PL/SQL block or a CALL statement, repetition of placeholder names is significant. Each unique placeholder name must have a corresponding bind argument in the USING clause. If you repeat a placeholder name, you do not need to repeat its corresponding bind argument. All references to that placeholder name correspond to one bind argument in the USING clause.

In Example 7-5, all references to the first unique placeholder name, :x, are associated with the first bind argument in the USING clause, a, and the second unique placeholder name, :y, is associated with the second bind argument in the USING clause, b.

Example 7-5 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;
/

Using DBMS_SQL Package

The DBMS_SQL package defines an entity called a SQL cursor number. Because the SQL cursor number is a PL/SQL integer, you can pass it across call boundaries and store it. You can also use the SQL cursor number to obtain information about the SQL statement that you are executing.

You must use the DBMS_SQL package to execute a dynamic SQL statement when you don't know either of the following until run-time:

SELECT list
What placeholders in a SELECT or DML statement must be bound

In the following situations, you must use native dynamic SQL instead of the DBMS_SQL package:

The dynamic SQL statement retrieves rows into records.
You want to use the SQL cursor attribute %FOUND, %ISOPEN, %NOTFOUND, or %ROWCOUNT after issuing a dynamic SQL statement that is an INSERT, UPDATE, DELETE, or single-row SELECT statement.

For information about native dynamic SQL, see "Using Native Dynamic SQL".

When you need both the DBMS_SQL package and native dynamic SQL, you can switch between them, using the following:

DBMS_SQL.TO_REFCURSOR Function
DBMS_SQL.TO_CURSOR_NUMBER Function

Note:

You can invoke DBMS_SQL subprograms remotely.

DBMS_SQL.TO_REFCURSOR Function

The DBMS_SQL.TO_REFCURSOR function converts a SQL cursor number to a weakly-typed variable of the PL/SQL datatype REF CURSOR, which you can use in native dynamic SQL statements.

Before passing a SQL cursor number to the DBMS_SQL.TO_REFCURSOR function, you must OPEN, PARSE, and EXECUTE it (otherwise an error occurs).

After you convert a SQL cursor number to a REF CURSOR variable, DBMS_SQL operations can access it only as the REF CURSOR variable, not as the SQL cursor number. For example, using the DBMS_SQL.IS_OPEN function to see if a converted SQL cursor number is still open causes an error.

Example 7-6 uses the DBMS_SQL.TO_REFCURSOR function to switch from the DBMS_SQL package to native dynamic SQL.

Example 7-6 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                    )
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 arguments:
  FOR i IN 1 .. bindnames.COUNT LOOP
    DBMS_SQL.BIND_VARIABLE(curid, bindnames(i), bindvars(i));
  END LOOP;

  -- Execute 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 causes an error because curid was converted to a REF CURSOR:
  isopen := DBMS_SQL.IS_OPEN(curid);

  CLOSE src_cur;
END;
/

DBMS_SQL.TO_CURSOR_NUMBER Function

The DBMS_SQL.TO_CURSOR function converts a REF CURSOR variable (either strongly or weakly typed) to a SQL cursor number, which you can pass to DBMS_SQL subprograms.

Before passing a REF CURSOR variable to the DBMS_SQL.TO_CURSOR function, you must OPEN it.

After you convert a REF CURSOR variable to a SQL cursor number, native dynamic SQL operations cannot access it.

After a FETCH operation begins, passing the DBMS_SQL cursor number to the DBMS_SQL.TO_REFCURSOR or DBMS_SQL.TO_CURSOR function causes an error.

Example 7-7 uses the DBMS_SQL.TO_CURSOR function to switch from native dynamic SQL to the DBMS_SQL package.

Example 7-7 Switching from Native Dynamic SQL to DBMS_SQL Package

CREATE OR REPLACE PROCEDURE do_query_2 (sql_stmt VARCHAR2) 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);
    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;
/

Avoiding SQL Injection in PL/SQL

SQL injection is a technique for maliciously exploiting applications that use client-supplied data in SQL statements, thereby gaining unauthorized access to a database in order to view or manipulate restricted data. This section describes SQL injection vulnerabilities in PL/SQL and explains how you can guard against them.

Topics:

Overview of SQL Injection Techniques
Guarding Against SQL Injection

Overview of SQL Injection Techniques

SQL injection techniques differ, but they all exploit a single vulnerability: string input is not correctly validated and is concatenated into a dynamic SQL statement. This topic classifies SQL injection attacks as follows:

Statement Modification
Statement Injection

The following topics describe these techniques. To try the examples in your sample database, run the script in Example 7-8.

Example 7-8 Setup for SQL Injection Examples

CONNECT HR/password
SET SERVEROUTPUT ON
SET LINESIZE 150
SET ECHO OFF
 
DROP TABLE user_pwd;
CREATE TABLE user_pwd (username VARCHAR2(100), password VARCHAR2(100));
INSERT INTO user_pwd VALUES ('whitehat', 'secret');
DROP TABLE delemp;
CREATE TABLE delemp AS SELECT * FROM employees;
COMMIT;

Statement Modification

Statement modification means deliberately altering a dynamic SQL statement so that it executes in a way unintended by the application developer. Typically, the user retrieves unauthorized data by changing the WHERE clause of a SELECT statement or by inserting a UNION ALL clause. The classic example of this technique is bypassing password authentication by making a WHERE clause always TRUE.

Suppose a Web form prompts a user to enter a username and password. When the user clicks Submit, the form invokes a PL/SQL stored subprogram that concatenates the username and password entered in the form to build a dynamic SQL statement. The subprogram executes the SELECT statement to authenticate the user.

The password-checking procedure ckpwd in Example 7-9 uses concatenation to build a SELECT statement whose FROM clause specifies the user_pwd table. If the user enters a username and password stored in the user_pwd table, then the execution of the SELECT statement retrieves a single row and the user is authenticated. The ckpwd procedure displays the concatenated SELECT statement so that you can see which statement is executed.

Example 7-9 Password-Checking Procedure Vulnerable to Statement Modification

CREATE OR REPLACE PROCEDURE ckpwd (
  p_user IN VARCHAR2,
  p_pass IN VARCHAR2              )
IS
  v_query  VARCHAR2(100);
  v_output NUMBER;
BEGIN
  v_query :=
    q'{SELECT COUNT(*) FROM user_pwd }'
    || q'{WHERE username = '}'
    || p_user
    || q'{' AND password = '}'
    || p_pass
    || q'{'}';
  DBMS_OUTPUT.PUT_LINE (
    CHR(10
    || 'Built the following statement: '
    || CHR(10)
    || v_query         );
  EXECUTE IMMEDIATE v_query INTO v_output;
  IF v_output = 1 THEN
    DBMS_OUTPUT.PUT_LINE(CHR(10) || p_user || ' is authenticated');
  ELSE
    DBMS_OUTPUT.PUT_LINE(CHR(10) || ' access denied');
  END IF;
END;
/

Suppose that the user whitehat enters the password secret in a Web form. You can simulate this scenario by executing the following code:

BEGIN
  ckpwd (p_user => q'{whitehat}',
         p_pass => q'{secret}'
        );
END;
/

The resulting output is:

Built the following statement: SELECT COUNT(*) FROM user_pwd
  WHERE username = 'whitehat' AND password = 'secret'
whitehat is authenticated

A malicious user can exploit the concatenation vulnerability and enter the username x in the Web-based form and the following text as a password:

x' OR 'x' = 'x

You can simulate this scenario by executing the following code:

BEGIN
  ckpwd (p_user => q'{x}', p_pass => q'{x' OR 'x' = 'x}');
END;
/

The resulting output is:

Built the following statement: SELECT COUNT(*) FROM user_pwd
  WHERE username = 'x' AND password = 'x' OR 'x' = 'x'
x is authenticated

By using the cleverly constructed password, you alter the concatenated SQL statement so that the OR condition always returns TRUE. Thus, the SELECT statement whose FROM clause specifies the user_pwd table always succeeds no matter which username is entered.

Statement Injection

Statement injection means that a user appends one or more new SQL statements to a dynamically generated SQL statement. Anonymous PL/SQL blocks are vulnerable to this technique.

Suppose a Web form prompts a user to enter a username and password. When the user clicks Submit, the form invokes a PL/SQL stored subprogram that concatenates the username and password entered in the form into an anonymous PL/SQL block. The subprogram then executes the anonymous block to authenticate the user.

The procedure call_ckpwd in Example 7-10 uses concatenation to build an anonymous block that invokes the ckpwd procedure from Example 7-9. If the user enters a username and password stored in the user_pwd table, then the execution of the block retrieves a single row and the user is authenticated. The call_ckpwd procedure prints the concatenated text so that you can see which block is executed.

Example 7-10 Password-Checking Procedure Vulnerable to Statement Injection

CREATE OR REPLACE PROCEDURE call_ckpwd (
  p_user IN VARCHAR2,
  p_pass IN VARCHAR2                   )
IS
  v_block VARCHAR2(100);
BEGIN
  v_block :=
    q'{BEGIN ckpwd('}'
    ||    p_user
    || q'{', '}'
    ||    p_pass
    || q'{'); END; }';
  DBMS_OUTPUT.PUT_LINE (
    CHR(10)
    || 'Built the following anonymous block: '
    || CHR(10)
    || v_block         );
  EXECUTE IMMEDIATE v_block;
END;
/

Suppose that the user whitehat enters the password secret in a Web form. You can simulate this scenario by executing the following code:

BEGIN
  call_ckpwd (p_user => q'{whitehat}', p_pass => q'{secret}');
END;
/

The resulting output is:

Built the following anonymous block: BEGIN ckpwd('whitehat', 'secret'); END;
  WHERE username = 'whitehat' AND password = 'secret'
whitehat is authenticated

The user whitehat could enter the following fraudulent string as the password in a Web form:

secret'); DELETE FROM hr.delemp WHERE UPPER('x') = UPPER('x

You can simulate this scenario by executing the following code:

BEGIN
  call_ckpwd (p_user => q'{whitehat}', p_pass => q'{secret');
  DELETE FROM hr.delemp WHERE UPPER('x') = UPPER('x}');
END;
/

The resulting output is:

Built the following anonymous block:
BEGIN
  ckpwd( 'whitehat', 'secret');
  DELETE FROM hr.delemp WHERE UPPER('x') = UPPER('x');
END;
Built the following statement:
SELECT COUNT(*) FROM user_pwd
  WHERE username = 'whitehat'
  AND password = 'secret'
whitehat is authenticated

The fraudulent password causes the system to authenticate whitehat and silently execute the injected DELETE statement. A SELECT statement whose FROM clause specifies the delemp table shows that the injected statement silently removed all rows from the table:

SELECT * FROM delemp;
no rows selected

Guarding Against SQL Injection

If you use dynamic SQL in your PL/SQL applications, you must check the input text to ensure that it is exactly what you expected. You can use the following techniques:

Using Bind Arguments to Guard Against SQL Injection
Using Validation Checks to Guard Against SQL Injection

Using Bind Arguments to Guard Against SQL Injection

Besides improving performance, bind arguments make your PL/SQL code immune to SQL injection attacks.

The procedure ckpwd_bind in Example 7-11 is immune to SQL injection because it builds the dynamic SQL statement v_query with bind arguments (not by concatenation as in the vulnerable procedure in Example 7-9).

Example 7-11 Password-Checking Procedure Immune to SQL Injection

CREATE OR REPLACE PROCEDURE ckpwd_bind (
  p_user IN VARCHAR2,
  p_pass IN VARCHAR2                   )
IS
  v_query  VARCHAR2(100);
  v_output NUMBER;
BEGIN
  v_query := q'{SELECT COUNT(*) FROM user_pwd
    WHERE username = :1
      AND password = :2}';
 DBMS_OUTPUT.PUT_LINE(
   CHR(10) || 'Built the following statement: ' || CHR(10)|| v_query);
 EXECUTE IMMEDIATE v_query
   INTO v_output
     USING p_user, p_pass;
 IF v_output = 1 THEN
   DBMS_OUTPUT.PUT_LINE(CHR(10) || p_user ||' is authenticated');
 ELSE
   DBMS_OUTPUT.PUT_LINE(CHR(10) || 'access denied');
 END IF;
END;
/

If the user passes the fraudulent password shown in Example 7-9 to the ckpwd_bind procedure, the user is not authenticated.

You can simulate this scenario by executing the following code:

BEGIN
  ckpwd_bind (p_user => q'{x}', p_pass => q'{x' OR 'x' = 'x}');
END;
/

The resulting output is:

Built the following statement:
SELECT COUNT(*) FROM user_pwd
  WHERE username = :1 AND password = :2
access denied

The same binding technique fixes the vulnerable call_ckpwd procedure shown in Example 7-10.

By using bind arguments exclusively in your code, you avoid concatenating SQL statements, preventing malicious users from altering or injecting additional statements. Oracle database uses the value of the bind argument exclusively and does not interpret its contents in any way. This technique is the most effective way to prevent SQL injection in PL/SQL programs.

Using Validation Checks to Guard Against SQL Injection

Always have your program validate user input to ensure that it is what is intended. For example, if the user is passing a department number for a DELETE statement, check the validity of this department number by selecting from the departments table. Similarly, if a user enters the name of a table to be deleted, check that this table exists by selecting from the static data dictionary view ALL_TABLES.

In Example 7-12, the procedure raise_emp_salary checks the validity of the column name that was passed to it before it updates the employees table, and then the anonymous PL/SQL block invokes the procedure from both a dynamic PL/SQL block and a dynamic SQL statement.

Example 7-12 Dynamic SQL

CREATE OR REPLACE PROCEDURE raise_emp_salary (
  column_value  NUMBER,
  emp_column    VARCHAR2,
  amount NUMBER                              )
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;
/

In validation-checking code, the subprograms in the package DBMS_ASSERT are often useful. For example, you can use the DBMS_ASSERT.ENQUOTE_LITERAL function to enclose a string literal in quotation marks. This prevents a malicious user from injecting text between an opening quotation mark and its corresponding closing quotation mark.

Caution:

Although the DBMS_ASSERT subprograms are useful in validation code, they do not replace it. For example, an input string can be a qualified SQL name (verified by DBMS_ASSERT.QUALIFIED_SQL_NAME) and still be a fraudulent password.

See:

Oracle Database PL/SQL Packages and Types Reference for information about DBMS_ASSERT subprograms.

The procedure ckpwd in Example 7-13, which checks the validity of user input (user name and password), uses the DBMS_ASSERT.ENQUOTE_LITERAL function to enquote string literals and the DBMS_ASSERT.SCHEMA_NAME function to verify that p_user is an existing schema name.

Caution:

When checking the validity of a user name and its password, always return the same error regardless of which item is invalid. Otherwise, a malicious user who receives the error message "invalid password" but not "invalid user name" (or the reverse) will realize that he or she has guessed one of these correctly.

Example 7-13 Using DBMS_ASSERT.ENQUOTE_LITERAL When Validating User Input

CREATE OR REPLACE PROCEDURE ckpwd (
  p_user IN VARCHAR2,
  p_pass IN VARCHAR2              )
IS
  v_query  VARCHAR2(100);
  v_output NUMBER;
BEGIN
  v_query :=
    q'{SELECT COUNT(*) FROM user_pwd }'
    || q'{WHERE username = }'
    || DBMS_ASSERT.ENQUOTE_LITERAL(DBMS_ASSERT.SCHEMA_NAME(p_user))
    || q'{' AND password = }'
    || DBMS_ASSERT.ENQUOTE_LITERAL(p_pass);
  DBMS_OUTPUT.PUT_LINE (
    CHR(10)
    || 'Built the following statement: '
    || CHR(10)
    || v_query
  );
 EXECUTE IMMEDIATE v_query INTO v_output;
 IF v_output = 1 THEN
   DBMS_OUTPUT.PUT_LINE(CHR(10) || p_user ||' is authenticated');
 ELSE
   DBMS_OUTPUT.PUT_LINE(CHR(10) || 'access denied');
 END IF;
END;
/