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 :

  v_sql varchar2(4000);
    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);

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);
    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);

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:

  v_sql varchar2(4000);
    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);

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

Source Article from

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>