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
  • Allowlisting

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 allowlisting are not valid defenses against SOQL injection? A final approach for your tool belt is character replacement, also known as blocklisting. This approach removes “bad characters” from user input.

In security, blocklisting will never be as strong as allowlisting because it is far easier to predict a few good inputs than it is to predict all possible bad inputs. That said, blocklisting 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 allowlisting 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.

Allowlisting

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 allowlisting. 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.

Allowlisting 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

Note

Note

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