Scenario 1: Your organization has a Salesforce Experience site that allows guests to retrieve their customer profiles. To do this, it prompts the user to enter their phone number. If found, the system returns their contact record. But one day, instead of entering a phone number, a user enters a SOQL wildcard character into the field and hits enter. This brings up a table of all contact records in the system, including phone numbers and email addresses.
Scenario 2: Employees at your company can access a home page directory that allows them to look up colleagues by entering a last name. This will display the retrieved employee’s full name, phone number, and email address. One day, someone with bad intentions enters a SOQL phrase that returns a table of all employees whose salaries exceed $100,000.
Both of these scenarios are examples of SOQL injection. This is when a person intentionally (or accidentally!) hacks into your system by injecting SOQL keywords and/or wildcard characters into an input field. In turn, this alters the underlying SOQL query, tricking the system into divulging information or returning records the perpetrator shouldn’t see. In short, SOQL injection is a security threat.
Scary? You bet! But the good news is that SOQL injection is easy to prevent as long as you understand how it happens. So, let’s take a quick look at how the above scenarios played out behind the scenes in Apex.
First, it doesn’t matter how the user input was acquired (eg, screen flow, LWC, Visualforce page, etc.). As long as it was typed by the user and passed as a variable to a SOQL query then there’s a risk of a security breach.
Scenario 1: SOQL wildcard characters
In the first scenario, the user is asked for a phone number. That input is passed to a variable called searchTerm, which is spliced into a string destined for a dynamic SOQL query:
Database.query('SELECT Id, Name, Email, Phone FROM Contact WHERE Phone LIKE \'' + searchTerm + '\'');Now, if the user simply enters a phone number, this variable will act as a filter on the Contact object. As a result, we should only get one contact record returned if there’s a single match. For example, if the user enters (555) 842-1212 then the SOQL query will look like this:
SELECT Id, Name, Email, Phone FROM Contact WHERE Phone LIKE '(555) 842-1212'In the case above, one record should be returned. But now, we have a user that enters %, injecting a wildcard character into the query. The query now looks like this:
SELECT Id, Name, Email, Phone FROM Contact WHERE Phone LIKE '%'In this case, all contacts will be returned since the % wildcard character is being used along with the LIKE operator. (You can think of the LIKE keyword as activating the wildcard character, giving it the ability to return all records.)
Note: If these SOQL terms or concepts are confusing — or if you need a refresher — jump on over to the start of my 3-part SOQL 101 series! Start with Part 1 if you need the basics, such as how to use a WHERE clause, or skip to Part 2 if you need information on using LIKE and the wildcard characters % (percent) and _ (underscore).
Also, the use of the backslash (\) in the Database.query example above (and below) is used to escape the single quote. We’ll learn more about that in the follow-up post. But for now, it’s enough to know that these provide the query with the requisite single quotes on either side of the variable (searchTerm).
Scenario 2: SOQL Phrases
What about the second scenario? How can someone get salary information when the scope of the original query is so limited? Here’s what it looks like (do you see anything related to salary?):
Database.query('SELECT Id, Name, Email, Phone FROM User WHERE LastName = \'' + searchTerm + '\'');In this case, the hacker has to be both clever about how they write the SOQL phrase and knowledgeable about API names the system uses (eg, Salary__c). Here’s what they entered in the input field:
x' OR Salary__c > 100000 OR FirstName = '$And here’s how the final query was run;
SELECT Name, Email, Phone FROM User WHERE LastName = 'x' OR Salary__c > 100000 OR FirstName = '$'By using multiple OR operators (as opposed to AND) and pairing the starting and ending single quotes, they were able to input a valid SOQL phrase that only returns employees whose salaries are greater than $100,000. (Here the LastName and FirstName filters contain dummy values and won’t return any records.)
It’s also worth pointing out that the user is not going to see the salary of each employee returned since only the Name, Email, and Phone fields are in the SELECT clause. However, they will see all the names of employees who have met the salary filter. The perpetrator can then continue to tweak the salary amount to get reliable salary ranges for individual employees.
So, how do we stop SOQL injection? It’s actually pretty simple, and I bet you’ve already thought of a way or two to stop it. But if not, no worries! Join me in the next post to learn How to Stop SOQL Injection.