In the last post, I defined SOQL injection and how it works — so if you missed it, consider reading it now. But as a quick overview, SOQL injection is when someone enters SOQL phrases and/or wildcard characters into a user input field to alter the underlying SOQL query. This causes the system to unwittingly divulge restricted data and records. Today I’ll discuss how to stop these types of attacks.
The good news is that it’s pretty easy to stop. In fact, if you follow the steps below, any SOQL query you create should be injection-proof.
1. Avoid the LIKE Keyword
This defense rarely gets mentioned but I think it’s one of the first places to start. Replace any LIKE keyword with the equal sign (=). Why? Because the LIKE keyword “activates” the wildcard characters % (percent) and _ (underscore). Used alongside LIKE, a single % character can return all records.
Here’s an example from the previous post where the perpetrator entered % into the input field (returning all Contact records):
SELECT Id, Name, Email, Phone FROM Contact WHERE Phone LIKE '%'Of course, some of you may be (and should be) questioning why LIKE was even used in the first place. Remember, LIKE alone doesn’t create a fuzzy match (it has to be used with wildcard characters). And that’s why you’ll often see LIKE used in a dynamic SOQL query with wildcard characters tacked on to both sides of the input variable like this:
Database.query('SELECT Id, Name, Email, Phone FROM Contact WHERE Phone LIKE \'%' + searchTerm + '%\'');Using the above query, if someone entered just part of a phone number (say, the last 4 digits), any phone number containing those four digits (consecutively) will cause that contact record to be returned. Now, there may be times when you need a robust fuzzy search like this. But it also opens you up to attack. So, to prevent this vulnerability, don’t use the LIKE keyword. Also, delete any hardcoded wildcard characters in your query:
Database.query('SELECT Id, Name, Email, Phone FROM Contact WHERE Phone = \'' + searchTerm + '\'');The query above is now less vulnerable than before, BUT it’s still vulnerable to attack. For this reason, you must…
2. Escape Single Quotes
But what does that even mean?
First, to escape a character you need to precede it with a backslash (\). Escaping a character (whether a single quote or wildcard character) means that it no longer has special properties and will be viewed as the literal character. (In some cases, the backslash actually gives special powers (eg, \n) but that doesn’t apply here.)
In the case of the single quote, it will no longer be recognized as an opening or closing single quote for a string. Instead, it will simply be viewed as an internal single quote or an apostrophe. Here’s a good example:
String restaurantName = 'Bob\'s BBQ';Above, if we didn’t use the backslash (\) to escape the apostrophe, we would get an error because the apostrophe would be viewed as a closing single quote for the string. And as we saw in the previous post, we escape single quotes when using a dynamic query to ensure they appear around the variable:
Database.query('SELECT Id, Name, Email, Phone FROM Contact WHERE Phone LIKE \'' + searchTerm + '\'');While we could insert our own backslashes into the user input (going through it character by character), instead we have two easier methods at our disposal:
Bind Variables and the Escape String Method
If you didn’t already know, a bind variable escapes single quotes automatically. So, as long as you insert the variable into the query as a bind variable, then you’re good to go. Here’s an example where :lastName is the bind variable:
String lastName = 'Smith';
List<Contact> cons = [SELECT Id, Name, Email FROM Contact WHERE LastName = :lastName];What some people don’t realize is that bind variables can also be used in a dynamic query:
String lastName = 'Smith';
List<Contact> cons = Database.query('SELECT Id, Name, Email FROM Contact WHERE LastName = :lastName');So let’s see the bind variable in action. Let’s go back to the example from our previous post where the perpetrator used the following SOQL phrase to get a list of employees (Users) whose salaries exceeded $100,000:
x' OR Salary__c > 100000 OR FirstName = '$The phrase above has two single quotes (one after the x and one before the $). When spliced into the dynamic query that returns User records, the resulting string looks like this:
SELECT Name, Email, Phone FROM User WHERE LastName = 'x' OR Salary__c > 100000 OR FirstName = '$'But when escaped (using a bind variable), it would result in the following query:
SELECT Name, Email, Phone FROM User WHERE LastName = 'x\' OR Salary__c > 100000 OR FirstName = \'$'Interestingly, this is still a valid query. But what LastName ultimately evaluates to (and what the system sees) is everything in yellow:
LastName = 'x' OR Salary__c > 100000 OR FirsName = '$'In other words, Salary__c and FirstName are all seen as part of the entry for LastName. For this reason, no records are returned since no LastName field in our org has that entry (highlighted in yellow above).
While there’s no reason (that I can think of) not to use a bind variable in a dynamic query, the other easy way to escape single quotes is to use the escapeSingleQuotes String method like this:
Database.query('SELECT Id, Name, Email FROM Contact WHERE LastName = \'' + String.escapeSingleQuotes(searchTerm) + '\'');Like a bind variable, all single quotes in the variable searchTerm are escaped using this method. So, that’s it. Now your query is super safe. Right?
Yes, for the most part. But what else should we do to make sure our org stays safe?
3. Use Apex Security Features
Finally, what you really need to make sure is that you’re taking full advantage of Salesforce security features. This includes ensuring user access is locked down using appropriate profiles and CRUD permissions.
Likewise, in your code, be sure to use the with sharing keyword in your Apex classes and WITH USER_MODE in your SOQL queries. Combined, these actions will make certain your users only see the objects and fields they have access to.
If you do everything above, you org should now be safe from SOQL injection. Phew!
Did I miss something important? Do you have a question? If so, leave a comment below!