Saturday, 15 August 2015

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

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.


Patrick Wolf said...
This comment has been removed by the author.
Patrick Wolf said...

Hi Dick,

I would also suggest to not use your concatenation trick and instead have a look at DBMS_ASSERT. If it's just about the APEX_UTIL.JSON_FROM_SQL api (which is not documented), I would rather suggest to have a look at our new documented APEX_JSON.WRITE ( api which does accept a REFCURSOR to emit the result of a SQL statement as JSON. This api will allow that you can use bind variables in your SQL statement.


Recx Ltd said...

May we suggest the following code, it is important to additionally escape any single quotes in the search term, in case the user wants to search something with a single quote (e.g. O'Niel);

v_safe_paramater := dbms_assert.enquote_literal(replace(apex_application.x01, '''', ''''''));

v_sql := replace(v_sql, '#EMP#', v_safe_paramater);

The additional replace correctly encodes the single quotes in the input.
The dbm_assert.enquote_literal provides the security barrier against SQLi.


Recx Ltd

Dick Dral said...


Thanks for the suggestions.
At the company I work now, we still use Apex 4.2, so we are not able to use APEX_JSON.WRITE.
We can use DBMS_ASSERT.