Start tracking your progress
Trailhead Home
Trailhead Home

Prevent SOQL Injection in Your Code

Learning Objectives

After completing this unit, you'll be able to:
  • Learn the different patterns of SOQL injection prevention.
  • Prevent SOQL Injection using string.escapeSingleQuotes().
  • List the cases where the use of string.escapeSingleQuotes isn’t sufficient.

SOQL Injection Prevention

As you learned in the previous unit, a SOQL injection attack can be used by attackers to access otherwise restricted data in your org. So how do you prevent this?

There are a number of techniques you can use to prevent SOQL injection, but how you use them depends on what you’re trying to accomplish with your query. We’ll cover these techniques in this unit.

  • Static queries with bind variables
  • String.escapeSingleQuotes()
  • Type casting
  • Replacing characters
  • Whitelisting

Static Query and Bind Variables

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

String query = ‘select id from contact where firstname =\’’+var+’\’’;
queryResult = Database.execute(query);

As you’ve learned, using user input (the var variable) directly in a SOQL query opens the application up to SOQL injection. To mitigate the risk, translate the query into a static query like this one.

queryResult = [select id from contact where firstname =:var];

This ensures that the user input is treated as a variable, not as an executable element of the query. If a user types a value like test’ LIMIT 1 when the database performs the query, it looks for any first names that are “test’ LIMIT 1” in the database. With a bind variable, the attacker isn’t able to break out and control the SOQL query.

While using bind variables is recommended, there are some limitations. They can only be used in these types of clauses.

  • The search string in FIND clauses.
  • The filter literals in WHERE clauses.
  • The value of the IN or NOT IN operator in WHERE clauses, enabling filtering on a dynamic set of values. Note that this is of particular use with a list of IDs or strings, though it works with lists of any type.
  • The division names in WITH DIVISION clauses.
  • The numeric value in LIMIT clauses.
  • The numeric value in OFFSET clauses.

However, you can use other mitigation techniques if bind variables aren’t possible in your code.

Typecasting

Another strategy to prevent SOQL injection is to use typecasting. By casting all variables as strings, user input can drift outside of expectation. By typecasting variables as integers or Booleans, when applicable, erroneous user input is not permitted. The variable can then be transformed back to a string for insertion into the query using string.valueOf() (remember with dynamic queries, the database.query() method only accepts a string).

Try this out in your Kingdom Management developer org.

  1. Log in to the Kingdom Management developer org and navigate to the SOQL Injection app using the application picker, then open the SOQL Injection Type Casting Fix tab.

    In this app you’re presented with a Visualforce page that lets you search personnel by providing an age filter value. Let’s test to see if this page is vulnerable to SOQL injection.

  2. Enter a simple SOQL injection payload “1 limit 1” and search. The query returns only 1 result because your input is treated as code.
  3. View the Apex controller using the link at the bottom of the page. You find the following code:
    public String textualAge {get; set;}
    [...]
    whereClause+='Age__c >'+textualAge+'';
    whereclause_records = database.query(query+' where '+whereClause);
    

    The variable textualAge is placed directly into the query, allowing your input to be treated as code. You will also see that there are no single quotes around textualAge in the query. For demonstration purposes, however, test out string.escapeSingleQuotes().

  4. Edit the controller SOQL_Injection_Typecasting_Fix and wrap textualAge in string.escapeSingleQuotes().
  5. Save the controller, and reload the demo page.
  6. Apply the same SOQL injection payload of “1 limit 1” to your search and you see that the SOQL injection still functions.

    Here’s what the actual query with your SOQL injection looks like:

    ‘Select Name, Role__c, Title__c, Age__c from Personnel__c where Age__c > 1 limit 1’
    

    There are no single quotes within this query, so string.escapeSingleQuotes() has no effect. You will need another solution to prevent SOQL injection.

  7. Edit the controller SOQL_Injection_Typecasting_Fix again, and remove string.escapeSingleQuotes().
  8. Find the variable declaration for textualAge and change it from String to Integer (age is an integer, so this typecasting is appropriate).
  9. Now before saving, locate where textualAge is added to the query. The query is expecting a string but textualAge is now an integer, so you will need to wrap textualAge in string.valueOf() as follows:
    whereClause+='Age__c >'+string.valueOf(textualAge)+'';
    
  10. Save the controller and reload the demo page.
  11. Submit your SOQL injection payload “1 limit 1” in the search area again, and you see an error rather than a SOQL injection. “1 limit 1” is not considered an integer, and the SOQL injection is prevented.

Typecasting can be used like this to prevent many kinds of SOQL injection where the user is not entering text.

Escaping Single Quotes

Another XSS mitigation option that is commonly used by developers who include user-controlled strings in their queries is the platform-provided escape function string.escapeSingleQuotes().

This function escapes any instance that it finds of a ‘ quote in the string using the backslash (\) escape character. This prevents an attacker’s input from being treated as code by constraining them to the boundary of the string

Let’s walk through an example in your Kingdom Management developer org.

  1. Log in to the Kingdom Management developer org and navigate to the SOQL Injection app using the application picker.
  2. Select the SOQL Injection String Fix tab.

    You see a Visualforce page that lets you search for Personnel by Title, similar to the previous SOQL injection example. Let’s see if our previous payload would work in this case to leak information about low performers in the kingdom.

  3. Try our previous payload: %' and Performance_rating__c<2 and name like'%

    The single result returns as before, and through SOQL injection we’ve learned a piece of information we were not supposed to know!

  4. Click the Apex link at the bottom to examine the code. In the controller you’ll find the following:
    whereClause += 'Title__c like  \'%'+textualTitle+'%\' ';
    whereclause_records = database.query(query+' where '+whereClause);
    

    The search string “textualTitle” is placed directly into the query string, allowing user input to be treated as code an enabling our SOQL injection. Because the variable is wrapped in single quotes in the final query, we can fix this SOQL injection through string.escapeSingleQuotes().

  5. Edit the Apex class “SOQL_Injection_String_Fix” and add string.escapeSingleQuotes() around the variable textualTitle.
  6. Save the code, and reload the demo page. Now try the previous SOQL injection payload again.

The SOQL injection payload will no longer function! Due to the single quote escaping, it is now impossible to close the string context in the query and add additional query functionality.

This time we’re using string.escapesinglequotes() to make sure the user-provided single quote is treated as data rather than code. Thus, the application is no longer vulnerable!

It is important to point out, however, that this solution only applies to strings. Not all variables are strings, and not all SOQL injection attacks require the use of a single quote character. Other solutions will be required to prevent SOQL injections in these types of code.

Replacing Characters

What if you have a scenario in your code where string.escapeSingleQuotes, typecasting, and whitelisting are not valid defenses against SOQL injection? A final approach for your tool belt is character replacement, also known as blacklisting. This approach removes “bad characters” from user input.

In security, blacklisting will never be as strong as whitelisting because it is far easier to predict a few good inputs than it is to predict all possible bad inputs. That said, blacklisting through character replacement can often effectively mitigate simple problems. Take the following code:

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

While typecasting or whitelisting would be effective here, an equally effective approach would be to remove all spaces from the supplied input. In that way, a SOQL injection payload of:

true AND ReceivesAdminInfoEmails=true

becomes

trueANDRecievesAdminInfoEmails=true

The code to remove all spaces from a string can be written as follows:

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

While it should not be considered the first line of defense, development is about flexible solutions to varied problems, and this solution is a valid one to keep in your toolbelt.

Whitelisting

As we just saw, we can’t use string.escapeSingleQuotes() to prevent all forms of SOQL injection. The previous solution of typecasting was only effective against non-string input however. What if user controlled values need to be text, but don’t have any single quotes? This often occurs when other portions of the query are put under a user’s control like the select fields, or the from object.

Another way to prevent SOQL injection without string.escapeSingleQuotes() is whitelisting. Create a list of all “known good” values that the user is allowed to supply. If the user enters anything else, you reject the response.

Let’s try one last demo to explore this in our Kingdom Management developer org.

  1. Log in to the Kingdom Management developer org and navigate to the SOQL Injection app using the application picker, then open the SOQL Injection Whitelist Fix tab.
    This page lets you query the personnel, property, or supply object.
  2. Select the personnel object from the picklist, and click Perform Search to see the results

    If you look at the URL, you will notice the following:

    /apex/soql_injection_whitelist_fix?object=Personnel__c
    

    It appears the object name is under the user’s control, via the URL parameter. You can view the Apex controller to verify that the “object” param is directly concatenated in the SOQL query.

  3. To attempt a SOQL injection, append where Performance_rating__c<2 to the URL, and load the page again. The URL changes to look like this:
    /apex/soql_injection_whitelist_fix?object=Personnel__c%20where%20Performance_rating__c<2
    

    You will also see that the SOQL injection took place and you can infer the performance review ratings.

  4. Using the controller link, view the Apex controller “SOQL_Injection_Whitelist_Fix”. You see the following code:
    public String objectName {get;set;}
    [...]
    string obj = ApexPages.currentPage().getParameters().get('object');
    if(obj != null){ 
        string query = 'select id, name from '+obj+' limit 10';
        [...]
    }
    

    The variable obj is acquired from the URL parameter object and passed directly into the query, enabling the SOQL injection. The SOQL injection did not involve any single quotes, so string.escapeSingleQuotes() is not effective. The variable obj is a string, not a Boolean or integer, so typecasting also is not effective. Another solution is needed!

    In this demo, you are choosing objects from a defined list of 3 objects. A good solution is a whitelist, or list of known good values that the user input should conform to. Whitelists are a common security approach, Salesforce uses them in many places, IP range restrictions being a prime example.

    To prevent this SOQL injection, add in whitelisting by verifying that the value of the object URL parameter conforms to one of the expected values: Personnel__c, Property__c, Supply__c.

  5. Edit the Apex controller “SOQL_Injection_Whitelist_Fix”. Locate the existing If validation:
    if(obj != null){
    
  6. Modify this if statement to check for expected input. The code looks similar to this (you will need to enter the correct values to be validated):
    if(obj=='...'||obj =='...'||obj =='...'){
    
  7. Save the controller, then reload the page.
  8. Submit the SOQL injection through the URL again and you see that the payload no longer returns data. Your input does not match the expected value so the query is not performed.

Whitelisting is an excellent approach for validating that user input conforms to an expected set of values, and is very effective at preventing SOQL injection.

Resources

Apex Developer Guide - SOQL Injection

Open Web Application Security Project (OWASP) - SQL Injection

Flower icon used to indicate that the content is for Salesforce Classic

Remember, this module is meant for Salesforce Classic. When you launch your hands-on org, switch to Salesforce Classic to complete this challenge.

retargeting