8 Coding Dynamic SQL

Native Dynamic SQL is Easy to Use

Because native dynamic SQL is integrated with SQL, you can use it in the same way that you use static SQL within PL/SQL code. Native dynamic SQL code is typically more compact and readable than equivalent code that uses the DBMS_SQL package.

With the DBMS_SQL package you must call many procedures and functions in a strict sequence, which means that even simple operations require extensive code. You can avoid this complexity by using native dynamic SQL instead.

Table 8-1 illustrates the difference in the amount of code required to perform the same operation with the DBMS_SQL package and native dynamic SQL.

CREATE OR REPLACE PROCEDURE insert_into_table 
  (p_table_name VARCHAR2, p_deptnumber NUMBER, 
   p_deptname VARCHAR2,   p_location    VARCHAR2) 
IS
  v_cur_hdl         INTEGER;
  v_stmt_str        VARCHAR2(200);
  v_rows_processed  BINARY_INTEGER;

BEGIN
  v_stmt_str := 'INSERT INTO ' 
  || p_table_name 
  || ' VALUES (:g_deptno, :g_dname, :g_loc)';
  v_cur_hdl := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cur_hdl, v_stmt_str, 
                 DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl,':g_deptno',  
                          p_deptnumber);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':g_dname', 
                          p_deptname);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':g_loc', 
                          p_location);
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  DBMS_SQL.CLOSE_CURSOR(v_cur_hdl);
END;
/

CREATE OR REPLACE PROCEDURE insert_into_table 
 (p_table_name VARCHAR2, p_deptnumber NUMBER,
  p_deptname VARCHAR2,   p_location VARCHAR2) 
IS
  v_stmt_str    VARCHAR2(200);



BEGIN
  v_stmt_str := 'INSERT INTO ' 
  || p_table_name 
  || ' VALUES (:g_deptno, :g_dname, :g_loc)';
  EXECUTE IMMEDIATE v_stmt_str 
    USING p_deptnumber, p_deptname, 
          p_location;








END;
/

Native Dynamic SQL is Faster than DBMS_SQL

Native dynamic SQL in PL/SQL performs comparably to static SQL because the PL/SQL interpreter has built-in support. Programs that use native dynamic SQL are much faster than programs that use the DBMS_SQL package. Typically, native dynamic SQL statements perform 1.5 to 3 times better than equivalent DBMS_SQL calls. Of course, performance gains may vary depending on your application.

Native dynamic SQL bundles the statement preparation, binding, and execution steps into a single operation, which minimizes the data copying and procedure call overhead and improves performance.

The DBMS_SQL package is based on a procedural API and incurs high procedure call and data copy overhead. Each time you bind a variable, the DBMS_SQL package copies the PL/SQL bind variable into its space for use during execution. Each time you execute a fetch, the data is copied into the space managed by the DBMS_SQL package and then the fetched data is copied, one column at a time, into the appropriate PL/SQL variables, resulting in substantial overhead.

CREATE OR REPLACE PROCEDURE del_dept
  (p_department_id  departments.department_id%TYPE) 
IS
BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM departments WHERE department_id = ' 
  || TO_CHAR(p_department_id);
END;
/
SHOW ERRORS;

CREATE OR REPLACE PROCEDURE del_dept
  (p_department_id  departments.department_id%TYPE)
IS
BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM departments WHERE department_id = :1' 
    USING p_department_id;
END;
/
SHOW ERRORS;

Native Dynamic SQL Supports User-Defined Types

Native dynamic SQL supports all of the types supported by static SQL in PL/SQL, including user-defined types such as user-defined objects, collections, and REFs. The DBMS_SQL package does not support these user-defined types.

Native Dynamic SQL Supports Fetching into Records

Native dynamic SQL and static SQL both support fetching into records, but the DBMS_SQL package does not. With native dynamic SQL, the rows resulting from a query can be directly fetched into PL/SQL records. In Example 8-12 the rows from a query are fetched into the v_emp_rec variable.

DECLARE
  TYPE EmpCurTyp  IS REF CURSOR;
  v_emp_cursor    EmpCurTyp;
  emp_record      emp%ROWTYPE;
  v_stmt_str      VARCHAR2(200);
  v_e_job         emp.job%TYPE;
BEGIN
  v_stmt_str := 'SELECT * FROM emp WHERE job = :1';
  -- in a multi-row query
  OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';
    LOOP
      FETCH v_emp_cursor INTO emp_record;
      EXIT WHEN v_emp_cursor%NOTFOUND;
    END LOOP;
  CLOSE v_emp_cursor;
  -- in a single-row query
  EXECUTE IMMEDIATE v_stmt_str INTO emp_record USING 'PRESIDENT';
END;
/

DBMS_SQL is Supported in Client-Side Programs

The DBMS_SQL package is supported in client-side programs, but native dynamic SQL is not. Every call to the DBMS_SQL package from the client-side program translates to a PL/SQL remote procedure call (RPC). These calls occur when you need to do any of the following:

• Bind a variable

• Define a variable

• Execute a statement

DBMS_SQL Supports Statements with Unknown Number of Inputs or Outputs

Native dynamic SQL does not support statements with an unknown number of inputs or outputs. The DBMS_SQL package does not have this limitation. One consequence is that you can use the DESCRIBE_COLUMNS procedure in the DBMS_SQL package to describe columns for a cursor opened and parsed through DBMS_SQL. This feature is similar to the DESCRIBE command in SQL*Plus. Native dynamic SQL does not have a DESCRIBE facility.

DBMS_SQL Supports SQL Statements Larger than 32 KB

The DBMS_SQL package supports SQL statements larger than 32 KB. Native dynamic SQL does not.

DBMS_SQL Lets You Reuse SQL Statements

The PARSE procedure in the DBMS_SQL package parses a SQL statement once. After the initial parsing, you can use the statement multiple times with different sets of bind arguments.

Native dynamic SQL prepares a SQL statement each time the statement is used, which typically involves parsing, optimization, and plan generation. Although the extra prepare operations incur a small performance penalty, the decrease in speed is typically outweighed by the performance benefits of native dynamic SQL.

Querying with Dynamic SQL: Example

The following example includes a dynamic query statement with one bind variable (:g_jobname) and two select columns (ename and sal):

v_stmt_str := 'SELECT ename, sal 
               FROM emp 
               WHERE job = :g_jobname';

This example queries for employees with the job description SALESMAN in the job column of the emp table. Table 8-2 shows sample code that accomplishes this query using the DBMS_SQL package and native dynamic SQL.

DECLARE  
  v_stmt_str       VARCHAR2(200);
  v_cur_hdl        INT;
  v_rows_processed INT;
  v_name           VARCHAR2(10);
  v_salary         INT;
BEGIN
  v_cur_hdl := DBMS_SQL.OPEN_CURSOR; -- open cursor 
  v_stmt_str := 'SELECT ename, sal 
                 FROM emp 
                 WHERE job = :g_jobname';
  DBMS_SQL.PARSE(v_cur_hdl,v_stmt_str,DBMS_SQL.NATIVE); 
  -- Supply binds (bind by name)
  DBMS_SQL.BIND_VARIABLE(v_cur_hdl, 'g_jobname', 
                        'SALESMAN'); 
  -- Describe defines 
  DBMS_SQL.DEFINE_COLUMN(v_cur_hdl, 1, v_name, 200); 
  DBMS_SQL.DEFINE_COLUMN(v_cur_hdl, 2, v_salary); 
  -- Execute
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);  
  LOOP 
    -- Fetch a row 
    IF DBMS_SQL.FETCH_ROWS(v_cur_hdl) > 0 THEN 
      -- Fetch columns from the row 
      DBMS_SQL.COLUMN_VALUE(v_cur_hdl, 1, v_name); 
      DBMS_SQL.COLUMN_VALUE(v_cur_hdl, 2, v_salary); 
      -- Process 
    ELSE
      EXIT; 
    END IF; 
  END LOOP; 
  DBMS_SQL.CLOSE_CURSOR(v_cur_hdl); -- close cursor
END;
/

DECLARE
  TYPE EmpCurTyp IS REF CURSOR;
  v_emp_cursor   EmpCurTyp;
  v_stmt_str     VARCHAR2(200);
  v_name         VARCHAR2(20);
  v_salary       NUMBER;
BEGIN
  v_stmt_str := 'SELECT ename, sal   
                 FROM emp 
                 WHERE job = :1';
  OPEN v_emp_cursor FOR v_stmt_str 
    USING 'SALESMAN'; 
    LOOP
      FETCH v_emp_cursor 
        INTO v_name, v_salary; 
       EXIT WHEN v_emp_cursor%NOTFOUND; 
     -- Process data
    END LOOP; 
  CLOSE v_emp_cursor;













END;
/

Performing DML with Dynamic SQL: Example

The following example includes a dynamic INSERT statement for a table with three columns.

v_stmt_str := 'INSERT INTO dept_new VALUES (:g_deptno, :g_dname, :g_loc)';

This example inserts a new row for which the column values are in the PL/SQL variables deptnumber, deptname, and location. Table 8-3 shows sample code that accomplishes this task with the DBMS_SQL package and native dynamic SQL.

DECLARE
  v_stmt_str         VARCHAR2(200);
  v_cur_hdl          NUMBER;
  v_deptnumber       NUMBER := 99;
  v_deptname         VARCHAR2(20);
  v_location         VARCHAR2(10);
  v_rows_processed   NUMBER;
BEGIN
  v_stmt_str := 'INSERT INTO dept 
    VALUES (:g_deptno, :g_dname, :g_loc)';
  v_cur_hdl := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cur_hdl, v_stmt_str, 
                 DBMS_SQL.NATIVE);
  -- Supply binds
  DBMS_SQL.BIND_VARIABLE
    (v_cur_hdl, ':g_deptno', v_deptnumber);
  DBMS_SQL.BIND_VARIABLE
    (v_cur_hdl, ':g_dname', v_deptname);
  DBMS_SQL.BIND_VARIABLE
    (v_cur_hdl, ':g_loc', v_location);
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);  
  DBMS_SQL.CLOSE_CURSOR(v_cur_hdl);
END;
/

DECLARE
  v_stmt_str      VARCHAR2(200);
  v_deptnumber    NUMBER := 99;
  v_deptname      VARCHAR2(20);
  v_location      VARCHAR2(10);


BEGIN
  v_stmt_str := 'INSERT INTO dept 
    VALUES (:g_deptno, :g_dname, :g_loc)';
  EXECUTE IMMEDIATE v_stmt_str 
    USING v_deptnumber, v_deptname, 
          v_location;









END;
/

Performing DML with RETURNING Clause Using Dynamic SQL: Example

The following example uses a dynamic UPDATE statement to update the location of a department, then returns the name of the department:

v_stmt_str := 'UPDATE dept_new 
               SET loc = :g_newloc
               WHERE deptno = :g_deptno
               RETURNING dname INTO :g_dname';

Table 8-4 shows sample code that accomplishes this operation using both the DBMS_SQL package and native dynamic SQL.

DECLARE 
  deptname_array   DBMS_SQL.VARCHAR2_TABLE; 
  v_cur_hdl        INT;
  v_stmt_str       VARCHAR2(200);
  v_location       VARCHAR2(20);
  v_deptnumber     NUMBER := 10;
  v_rows_procsd    NUMBER;
BEGIN 
  v_stmt_str := 'UPDATE dept 
    SET loc = :g_newloc
    WHERE deptno = :g_deptno
    RETURNING dname INTO :g_dname';
  v_cur_hdl := DBMS_SQL.OPEN_CURSOR; 
  DBMS_SQL.PARSE
    (v_cur_hdl, v_stmt_str, DBMS_SQL.NATIVE); 
  -- Supply binds 
  DBMS_SQL.BIND_VARIABLE
    (v_cur_hdl, ':g_newloc', v_location); 
  DBMS_SQL.BIND_VARIABLE
    (v_cur_hdl, ':g_deptno', v_deptnumber); 
  DBMS_SQL.BIND_ARRAY
    (v_cur_hdl, ':g_dname', deptname_array); 
  -- Execute cursor 
  v_rows_procsd := DBMS_SQL.EXECUTE(v_cur_hdl); 
  -- Get RETURNING column into OUT bind array 
  DBMS_SQL.VARIABLE_VALUE
    (v_cur_hdl, ':g_dname', deptname_array); 
  DBMS_SQL.CLOSE_CURSOR(v_cur_hdl); 
END;
/

DECLARE 
  deptname_array DBMS_SQL.VARCHAR2_TABLE; 
  v_stmt_str     VARCHAR2(200);
  v_location     VARCHAR2(20);
  v_deptnumber   NUMBER := 10;
  v_deptname     VARCHAR2(20);

BEGIN
  v_stmt_str := 'UPDATE dept 
    SET loc = :g_newloc
    WHERE deptno = :g_deptno
    RETURNING dname INTO :g_dname';
  EXECUTE IMMEDIATE v_stmt_str 
    USING v_location, v_deptnumber, OUT 
          v_deptname;













END;
/

Statement Modification

SQL modification involves 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 query 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 procedure that concatenates the username and password entered in the form to build a dynamic SQL statement. The procedure executes the query to authenticate the user.

Example 8-14 illustrates an authentication procedure that you can test in SQL*Plus. The ckpwd procedure uses concatenation to build a SQL query of the user_pwd table. If the user enters a username and password stored in the table, then the execution of the query retrieves a single row and the user is authenticated. The ckpwd procedure also displays the concatenated query so that you can see which query is executed.

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 query:'||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 invoking the code shown in Example 8-15 in SQL*Plus to authenticate whitehat (sample output included).

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

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

A malicious user could exploit the concatenation vulnerability and enter the username x in the Web-based form and the text shown in Example 8-16 as a password.

x' OR 'x' = 'x

You can simulate this scenario by executing the code in Example 8-17 in SQL*Plus (sample output included).

BEGIN
  ckpwd
  (
    p_user => q'{x}',
    p_pass => q'{x' OR 'x' = 'x}'
  );
END;
/
 
Built the following query:
SELECT COUNT(*) FROM user_pwd WHERE username = 'x' AND password = 'x' OR 'x' = 'x'
 
x is authenticated

By using the cleverly constructed password in Example 8-16, you alter the concatenated SQL statement so that the OR condition always returns TRUE. Thus, the query of the user_pwd table always succeeds no matter which username is entered.

Statement Injection

Statement injection occurs when 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 procedure that concatenates the username and password entered in the form into an anonymous PL/SQL block. The procedure then executes the anonymous block to authenticate the user.

Example 8-18 illustrates an authentication procedure that you can test in SQL*Plus. The call_ckpwd procedure uses concatenation to build an anonymous block that invokes the ckpwd procedure from Example 8-14. 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 also prints the concatenated text so that you can see which block is executed.

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-based form. You can simulate this scenario by invoking the call_ckpwd procedure shown in Example 8-19 in SQL*Plus (sample output included).

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

Built the following anonymous block:
BEGIN ckpwd( 'whitehat' , 'secret' ); END;
 
Built the following query:
SELECT COUNT(*) FROM user_pwd WHERE username = 'whitehat' AND password = 'secret'
 
whitehat is authenticated

If whitehat turns bad, then he could enter the string shown in Example 8-20 as the password in a Web form.

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

You can simulate this technique by invoking the call_ckpwd procedure shown in Example 8-21 in SQL*Plus (sample output included).

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

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

The bogus password in Example 8-20 causes the system to authenticate whitehat and silently execute the injected DELETE statement. A query of the delemp table shows that the injected statement silently removed all rows from the table:

SELECT * FROM delemp;
 
no rows selected

Using Bind Variables to Guard Against SQL Injection

"Improving Performance Through Bind Variables" shows how you can use bind variables to improve performance in dynamic SQL. Besides improving performance, binding placeholders renders your PL/SQL code immune to SQL injection attacks.

The ckpwd procedure shown in Example 8-14 used concatenation instead of bind variables. Example 8-22 shows how you could rewrite the procedure to use bind variables instead of concatenation.

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 query:'||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 tries to pass the bogus password shown in Example 8-16 to the ckpwd_bind procedure, then the technique fails to authenticate the user. You can execute the block shown in Example 8-23 in SQL*Plus to test the revised version of the code (sample output included).

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

Built the following query:
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 8-18. By using bind variables exclusively in your code, you avoid concatenating SQL statements and thereby prevent malicious users from altering or injecting additional statements. Oracle database uses the value of the bind variable 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

A program should always validate user input to ensure that it is what is intended. For example, if the user is passing in a department number for a DELETE statement, then check the validity of this department number by querying the departments table. Similarly, if a user enters the name of a table to be deleted, check that this table exists by querying the ALL_TABLES view.