What is SOQL Injection?

SOQL Injection (SOQLi) is a type of injection attack that makes it possible to execute malicious SOQL statements. Attackers can use SOQL Injection vulnerabilities to bypass application security measures. Although all standard queries are safe with SOQL injection but custom code can be vulnerable to SOQL injection. Especially public-facing applications like customer and partner portals are more vulnerable.

Let us take the below SOQL will get textualTitle parameter from LWC UI.

String query = 'SELECT Id, Name, Title FROM Account';
String whereClause = 'Title like \'%'+textualTitle+'%\' ';
List<Account> whereclause_records = database.query(query+' where '+whereClause);

If the user passes a title like Mr. then the query will be like below and there is no issue.

SELECT Id, Name, Title FROM Account where Title like '%Mr.%'

but if the user passes input %’ or Performance_rating__c<2 then the query will be like below

SELECT Id, Name, Title FROM Account where Title like '% %' or Performance_rating__c<2;

ideally query should only filter on the title field and show the result. But when the above query will execute it will get all accounts whose Performance_rating__c<2. User can get other account data as well if he will inject other SOQL filters.

Preventing SOQL Injection

SOQL injection can impact our application performance and data security. There are a number of techniques we can use to prevent SOQL injection

1. Static queries with bind variables
2. String.escapeSingleQuotes()
3. Type casting
4. Blocklisting or Replacing characters
5. Allowlisting
6. UI validation

1. Static queries with bind variables

The first and most recommended method to prevent SOQL injection is to use static queries with bind variables.

List<Acccount> acts=[SELECT Id, Name, Title FROM Account where Title=:title];

This step will ensure that the user input is treated as a variable, not as an executable element of the query. If a user types a value like Mr.’ and Performance_rating__c<2 when the database performs the query, it looks for filters that is “Mr.’ and Performance_rating__c<2” so it will not return any data.

2. String.escapeSingleQuotes()

escapeSingleQuotes method adds the escape character (\) to all single quotation marks in a string that is passed in from a user. So if we write SOQL like below using escapeSingleQuotes 

String query = 'SELECT Id, Name, Title FROM Account';
String whereClause = 'Title like \'%'+String.escapeSingleQuotes(textualTitle)+'%\' ';
List<Account> whereclause_records = database.query(query+' where '+whereClause);

So when the query executed for input Mr.’ and Performance_rating__c<2 then the query will convert as

SELECT Id, Name, Title FROM Account where Title like '%Mr.\' and Performance_rating__c<2%\''

So this way title will not match and no data will return.

3. Type casting

In a few scenarios where integer or boolean values are required to be entered as input, we can type cast input values into strings. By typecasting variables as integers or Booleans, erroneous user input is not permitted. The variable can then be transformed back to a string for insertion into the query using string.valueOf()

public String textualAge {get; set;} 
whereClause+='Age__c >'+string.valueOf(textualAge)+'';

if the user will pass 1 limit 1 then it will not be considered as integer input and SOQL will throw an error.

4. Replacing Characters

In a few scenarios, we can also remove unwanted characters from SOQL to prevent injection. It is also called blocklisting.

String query = ‘select id from user where isActive=‘+var;

If we have the above SOQL then the user can pass input like true Or ReceivesAdminEmails=true. Using this they can get all admin email data.

So if we replace our SOQL like the below query then it will remove all space.

String query = 'select id from user where isActive='+var.replaceAll('[^\w]','');

So the actual query will be like the one below. This way filter criteria will not be met and it will not return any data.

select id from user where isActive=trueOrReceivesAdminEmails=true

5. Allowlisting

In a few scenarios, we can only allow users to select input from a list of predefined values. This is called Allowlisting, we are just allowing users to select from valid values. This way they can not enter any invalid data and SOQL will not be impacted.

6. UI validation

We should properly validate input data before it is reached the Database level. This way we are allowing only valid data to SOQL formation.

Summary

Data Security is very important for applications. We should use all possible options to secure our application data. The above technique should be implemented properly to secure our data.

References:

Mitigate SOQL Injection

Similar Posts

What is PK Chunking?

What is Data Skew?

What is Light DOM?

What are Skinny Tables?

Salesforce Interview Question for Asynchronous Apex