Use Bind Variables with Dynamic PL/SQL

4.2 Use Bind Arguments with Dynamic PL/SQL

previous|next

As with dynamic SQL, you should avoid constructing dynamic PL/SQL with string concatenation. The impact of SQL injection vulnerabilities in dynamic PL/SQL is even more serious than in dynamic SQL because with dynamic PL/SQL, multiple statements (such DELETE or DROP) can be batched together and injected.

If you must use dynamic PL/SQL, try to use bind arguments. For example, you can rewrite this dynamic PL/SQL with concatenated string values:

v_stmt :=
  'BEGIN
     get_phone (''' || p_fname ||
                ''','''|| p_lname ||'''); END;';

EXECUTE IMMEDIATE v_stmt;

into this dynamic PL./SQL with placeholders (:1, :2) using bind arguments (p_fname, p_lname):

v_stmt :=
'BEGIN
get_phone(:1, :2); END;';

EXECUTE IMMEDIATE v_stmt USING p_fname, p_lname;

Click here for an example of a SQL injection vulnerability in dynamic PL/SQL