Preventing SQL Injection without using bind variables

SQL Injection can be a serious threat to your database. Depending of the setup of your database an attacker could destroy your data or get hold of sensitive data.
Many posts have been written about SQL Injection in Apex and the solution always has been to use bind variables instead of using substitution. There can be cases in which using bind variables is not an option or requires a lot more effort.

One example of this is the use of apex_util.json_from_sql :

declare 
  v_sql varchar2(4000);
begin
    v_sql := q'!select * from emp where ename like '#EMP#' !';
    v_sql := replace(v_sql,'#EMP#',apex_application.x01);
    apex_util.json_from_sql (v_sql);
end;

In the code above you can see that we need to incorporate the parameter into the query. There is no way we can use bind variables. This puts the door wide open to SQL Injection attacks.
However, if we split the parameter into an expression of which the result is equal to the parameter, no harm can be done. One possibility is to take the separate characters of the parameter and concatenate them.

parameter  := 'JON';
expression := 'J' ||'O'||'N';

The expression can be substituted in the query instead of the litteral parameter and you query is safe for SQL Injection.

The following procedure does the trick for you:

  function split_param ( p_param in varchar2) return varchar2 is
    l_return    varchar2(4000) := null;
    quote       char(1)        := chr(39);
  begin
    for i in 1..length(p_param) loop
      if l_return is not null then
        l_return := l_return || '||';
      end if;
      l_return := l_return || quote || substr(p_param,i,1) || quote;
    end loop;
    l_return := ltrim(rtrim(l_return,quote),quote);
    return(l_return);
  end;

This function accepts a string and outputs an expression that returns the same string.
We can use this function to convert the input parameter into an expression. This way the code will not be vulnerable to SQL Injection any more:

declare 
  v_sql varchar2(4000);
begin
    v_sql := q'!select * from emp where ename like '#EMP#' !';
    v_sql := replace(v_sql,'#EMP#',split_param(apex_application.x01));
    apex_util.json_from_sql (v_sql);
end;

I think it cannot be broken anymore. Do you agree or is this code still vulnerable to SQL Injection?

Love to hear from you!

Happy Apexing

Postscript: I have got a reaction on this blogpost by Carsten Czarski that notes that the dbms_assert package also contains functions to prevent SQL Injection. The function dbms_assert.enquote_literal ensures that all quotes within a string are paired, otherwise an exception is raised.
Thank you, Carsten.