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: