Saturday, 15 August 2015

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.

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.

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:

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.