Interactive Report with dynamic sql and binded variables (sql injection)

Question

I am building an Interactive Report. Source Type is Function Body returning SQL Query. And below is my query which is prone to sql injection. How can i bind variables here?

declare

vsql varchar2(4000);

begin

    vsql := 'SELECT Name FROM Customers WHERE 1 = 1 ';
    --# There are other checks as well for both SELECT and WHERE which are making this query dynamic
    if :P1_NAME is not null then
        vsql := vsql || ' AND UPPER(NAME) LIKE Upper('%''' || :P1_NAME || '%'')';
    end if;

    return vsql;
end;

Answer 1

Here is how I would do it. The example below has dynamic table and a variable in the where clause using LIKE.

The idea is to sanitize the table name (or any other sql objects) using DBMS_ASSERT.SQL_OBJECT_NAME and use bind variables where possible. So the generated query will still have bind variable substitution.

The double %% is needed because % is a special character in the apex_string.format function:

DECLARE
    l_query varchar2(4000);
    l_table_name varchar2(400);
BEGIN
    l_table_name := DBMS_ASSERT.sql_object_name(NVL(:P142_TABLE_NAME,'EMP'));
    l_query := 
    q'!select
                      EMPNO,
                      ENAME
                   from
                      %0
                   where 
                      ENAME LIKE '%%'||:P142_EMPLOYEE_NAME||'%%'!';
    l_query := apex_string.format(l_query,l_table_name);
    return(l_query);
END;

query in debug:

Answer 2

The way I see it, there's no need for dynamic SQL as there's nothing dynamic in that select statement. Region's source could've been just

SELECT name
  FROM customers
 WHERE UPPER (name) LIKE UPPER ('%' || DBMS_ASSERT.enquote_literal ( :P1_NAME) || '%');

because

• like operator (used with wildcards) will make sure that even if you don't enter anything into P1_NAME item, query will return all rows from that table
• if there's a value entered into P1_NAME, filter will be applied to result
• dbms_assert.enquote_literal will enclose value into single quotes and won't allow any injection you're concerned about
  • have a look at examples on Tim Hall's site