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 :
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.
The following procedure does the trick for you:
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:
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.
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.
