trailhead

Understand SOQL Injection

Learning Objectives

After completing this unit, you'll be able to:
  • List five or more differences between SQL and SOQL.
  • Learn how to exploit a basic SOQL injection.

A Primer on Structured Query Language (SQL)

One of the most important features of an application is the ability to store and retrieve data from a datastore. In a traditional web application, you use something called Structured Query Language (SQL). This is a special language that is used by application developers to interact with relational databases. SQL includes a lot of different features that enable you to INSERT, UPDATE, and DELETE records or entire tables. However, the most common operation performed is the SELECT query, which enables an application to query data from a database.

The basic format for this type of query is as follows.

SELECT TABLE_COLUMN FROM TABLE_NAME WHERE CONDITION

This query enables you to retrieve data from one or more (when using the wildcard * character) table columns within a specific table, restricting by an optional condition. While powerful on its own, this functionality is often expanded with additional features.

  • Join — Groups multiple tables together
  • Subquery — Uses results of one query in another query
  • Command Execution — Enables system-level commands to be run directly from a query

For an attacker looking to gain access your data, SQL is a prime target for exploitation.

So How Is SQL Attacked?

To create responsive applications customized to the user, developers often leverage user-supplied input to direct the information to retrieve from the database.

For example, think of the basic login function built into most applications.

  1. On the login page, you’re prompted to enter your username and password.
  2. You click ENTER, triggering a database query similar to the following on the back end.
    Select * from user where username = ‘CASTLE-Y_USERNAME’ and password = ‘CASTLE_PASSWORD’
    

If the database query results in data being returned, the application lets the user into the system.

If you trust the input without any additional validation, you open yourself up to SQL injection, whereby a malicious user can use characters with special meaning to SQL to modify the resulting query.

Let’s look at this flow again from an attacker’s perspective.

  1. On the login page, the attacker enters ‘admin’-- as the username and ‘password’ as the password.
  2. The attacker clicks ENTER, triggering the database query as before.
    Select * from user where username = ‘admin’-- and password = ‘password’
    

In SQL, a “--” denotes a comment, which causes the application to ignore the rest of the query. If a user with the username admin exists (a reasonable guess for most applications), our attacker successfully bypasses the login to access the underlying application!

While this is one serious example, remember that SQL isn’t just about querying data. It can also be used with more advanced actions to CREATE, INSERT, DELETE, and DROP data from your database. So SQL injection flaws can be extremely serious. A single flaw anywhere in your application may allow an attacker to read, modify, or delete your entire database.

But Salesforce Uses SOQL, Not SQL?

As a Salesforce developer, you know that on the Lightning Platform we use SOQL (Salesforce Object Query Language) not SQL. While the languages are similar in many respects, SOQL is essentially a stripped down version of SQL written specifically for the Salesforce platform.

Here are some ways that SOQL differs from SQL.

  • No INSERT, UPDATE or DELETE statements, only SELECT
  • No command execution
  • No wild cards for fields; all fields must be explicitly typed
  • No JOIN statement; however, you can include information from parent objects like Select name, phone, account.name from contact
  • No UNION operator
  • Queries cannot be chained together

Is SOQL Also Vulnerable to Injection Attacks?

With all these limitations enforced by the platform, you might be wondering if it’s even possible to execute an injection attack. In short, yes! Unfortunately a developer can still trust user input incorrectly, leading to an exposure of information via what is referred to as a SOQL injection attack.

Let’s walk through an example in the Kingdom Management developer org to see how this can be exploited.

  1. Log in to your Kingdom Management developer org.
  2. Navigate to the SOQL Injection app.
  3. Click the SOQL Injection Demo tab.

    In this app we’ve developed a custom page that enables users to search through their kingdoms to get an overview of the personnel in their kingdom. This list is filterable by providing a title or age filter in the search box.

  4. Try it out by typing in a title filter like Sir.

    The page acts as expected, returning a list of people that have the title Sir.

  5. Click the Apex Controller link at the bottom of the page, and search for the underlying SOQL query that’s being used by the application
    whereClause += 'Title__c like  \'%'+textualTitle+'%\' ';
    whereclause_records = database.query(query+' where'+whereClause);
    

    The application takes in the textualTitle parameter submitted by the user and inserts it directly into the SOQL query. This might be vulnerable to SOQL injection! As you may remember, SOQL injection occurs when an attacker modifies the structure of the query. So let’s try a more complicated filter this time and see if we can get it change the behavior of the underlying query.

  6. Navigate back to the SOQL Injection Demo tab and enter a filter like %' and name like 'Amanda%

    It works! Let’s see how we can misuse this to leak additional information.

  7. Try entering the following filter in the title search to have the database identify personnel with low performance ratings: %' and Performance_rating__c<2 and name like '%

The reason why this works is because the user input is concatenated into the SOQL query without any validation, enabling the attacker to close the single quote for the title parameter and add another condition to the query. This results in a syntactically valid query.

Before:

Title__c like '%'+textualTitle+'%'

After:

Title__c like '% %' and Performance_rating__c<2 and name like '% %'';

Another SOQL Injection Example

Let’s try another example of SOQL injection in the Kingdom Management developer org!

  1. Log in to your Kingdom Management developer org.
  2. Navigate to the SOQL Injection app.
  3. Click the SOQL Injection Demo tab.
  4. Type in an age filter like 22.

    The application should filter the list of personnel to only show those whose age is 22. Let’s try this again, but this time let’s add some valid SOQL at the end to see if the application executes it. An easy way to accomplish this is to use the LIMIT command, which limits the number of results returned by the query.

  5. Type in an age filter like 22 limit 1.

    If the application is vulnerable to SOQL injection, the “limit 1” portion of your string is executed as a SOQL command, limiting your results to only one record. It works!

    Let’s look at the code via the Apex controller link at the bottom of the page.

    if(textualAge!=null){
        whereClause+='Age__c >'+textualAge+'';
        whereclause_records = database.query(query+' where'+whereClause);
    }
    

The application takes in a string called textualAge and inserts it directly into the dynamic query, then executes all without validation or escaping. So it looks like this application is indeed vulnerable!

Impact of SOQL Injection

Luckily for Salesforce developers, since SOQL is narrower than SQL in terms of what a user can do, SOQL reduces the attack surface and limits what an attacker can do with a vulnerable query.

  • No command execution, therefore no ability to exploit the underlying OS running the Salesforce service.
  • No delete method, therefore no ability to interact destructively.
  • No insert or update methods, therefore no ability to add data, user accounts, or permissions to the system

However, SOQL is still vulnerable. An attacker who is able to successfully exploit SOQL injection can access fields that a developer did not intend to reveal or that a user should not ordinarily have access to.

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