As a Salesforce ISV, you need to be aware of the risk of SOQL injection attacks. This type of injection attack allows attackers to execute malicious SOQL statements in your ISV package, potentially bypassing your application’s security measures and gaining access to sensitive data. Even though standard queries are typically safe from SOQL injection, dynamic SOQL statements in your package can be vulnerable to this type of attack. Public-facing UIs are especially vulnerable to SOQL injection. In this blog post, we’ll discuss the risks of SOQL injection attacks in Salesforce ISV packages and share techniques for protecting your package from this serious threat.
SOQL Injection Code Example
Suppose we examine the following SOQL query. The user inputs a value for the “searchText” parameter, which can be anything, and is not validated. The query is constructed dynamically and then executed using the Database.query method.
String query = 'SELECT Id, Name FROM Opportunity';
String whereClause = 'Name like \'%'+searchText+'%\' ';
List<Opportunity> result = database.query(query+' WHERE '+whereClause);
If the user provides a name like “Foo“, the query will be as follows, and there will be no issue:
SELECT Id, Name FROM Opportunity WHERE Name like '%Foo%'
However, if the user provides an input such as %Foo%\’ OR Amount > 0, the query will be as follows:
SELECT Id, Name FROM Opportunity WHERE NAME like '%Foo%' OR Amount > 0;
The query should ideally only filter on the Name field and display the results. However, when this query executes, it retrieves all opportunities with an Amount value greater than a specific value. The user can obtain other opportunity data by injecting other SOQL filters.
Preventing SOQL Injection
SOQL injection can have an impact on both data security and the application’s performance. However, there are several techniques that we can use to prevent SOQL injection, including:
- Static queries with bind variables
- String.escapeSingleQuotes()
- Blocklisting
- Typecasting
- Allowlisting
Static queries with bind variables
Ensure that user input is treated as a variable rather than as executable code by using static queries with bind variables, which is the first and most recommended method to prevent SOQL injection. For instance, instead of using dynamic queries where user input is concatenated into the query string, use static queries like:
SELECT Id, Name FROM Opportunity WHERE Name like :searchText
By doing so, the database will treat the user input as a variable, and not as a part of the query to be executed. For example, if a user inputs “Foo’ OR Amount > 0“, the query will search for Opportunity Name that contains “Foo’ OR Amount > 0” and return no data.
String.escapeSingleQuotes()
The function utilizes the backslash (\) escape character to escape any occurrence of a single quote mark (‘) found in the string, thereby ensuring that the attacker’s input is confined within the boundaries of the string and cannot be executed as code. For instance, to prevent a potential SOQL injection attack, we can use the escapeSingleQuotes function to escape single quote marks in a SOQL query. In the following example:
String query = 'SELECT Id, Name FROM Opportunity;
String whereClause = ‘Name like%’ + String.escapeSingleQuotes(searchText)+’%’;
List<Opportunity> result = database.query(query+' WHERE '+whereClause);
If the user input is “%Foo’ AND Amount > 0“, the query will be executed as:
SELECT Id, Name FROM Opportunity WHERE NAME like '%Foo\' OR Amount > 0\''
As a result, the Name field will not match the input, and no data will be returned.
Blocklisting
In certain scenarios where alternative techniques are ineffective in defending against SOQL injection, removing unwanted characters from the user input variable can be an effective measure to prevent such attacks.
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.
If we use the query below to replace our SOQL, all spaces will be removed. This is because the regular expression ‘[^\w\s]’ identifies any non-word or non-space character and replaces it with an empty string.
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
Typecasting
In some scenarios, casting all variables as strings can result in vulnerabilities to SOQL injection. To prevent such attacks, it’s recommended to validate input values as integers or booleans if applicable, by casting them as such before using them in a query. Finally, the variable can be converted back to a string using the string.valueOf() method for safe insertion into the query.
When we enter a simple SOQL injection payload like “1 limit 1” and execute the search, the query returns only one result because the input is treated as code.
The corresponding Apex code would resemble:
public String textualAge {get; set;}
[...]
whereClause+='Age__c >'+textualAge+'';
whereclause_records = database.query(query+' where '+whereClause);
If a user passes input like “1 limit 1“, the actual query that gets executed will look like:
'SELECT Name, Role__c, Title__c, Age__c FROM Personnel__c WHERE Age__c > 1 limit 1'
Since there are no single quotes within this query, using string.escapeSingleQuotes() won’t have any effect. However, if we typecast the variable to an Integer before inserting it into the query, any SOQL injection attempt will result in an error.
Allowlisting
We cannot rely solely on String.escapeSingleQuotes() to prevent all types of SOQL injection. Blocklisting only covers non-string input. However, what if user-controlled values must be text but do not contain single quotes? In such cases, we can use Allowlisting. This technique involves defining a specific set of values that the user can input, and only allowing those values to be accepted by the application. All other inputs are rejected.
Conclusion
In this blog, we discussed the risks of SOQL injection attacks on Salesforce ISV packages and shared techniques for protecting your application from this type of attack. We covered five techniques to prevent SOQL injection attacks, including static queries with bind variables, String.escapeSingleQuotes(), blocklisting, typecasting, and allowlisting. By implementing these techniques, you can significantly reduce the risk of SOQL injection attacks and keep your package secure.
