SOQL Vulnerability Prevention

Introduction: 

The most important aspect of any application is the ability to store and retrieve data from a datastore. Structured Query Language (SQL) is used widely in a traditional web application for effective retrieval of data. When we build dynamic queries using SQL, it is most important to understand how our SQL query is defensive against the user supplied data.

What is SQL Injection? 

We can’t trust the users’ input blindly without any additional validation; such data input can cause SQL injection, meaning a malicious user modifies the resulting query by injecting characters with special meaning to SQL.

Will it affect SOQL queries?

Salesforce uses SOQL (Salesforce Object Query Language) protects the application against most attacks. In SOQL, we can only perform SELECT operation and not UPDATE or DELETE. So, when compared to SQL, SOQL has a lower risk on SOQL injection but still the attacks are nearly identical to the traditional SQL injection. For example, a user can see the fields or an object which he doesn’t have access.

SQL vs SOQL: 

SOQL has some restrictions over SQL.

1. No INSERT, UPDATE or DELETE statements, only SELECT
2. No wild cards for fields; all fields must be explicitly typed
3. No JOIN statement; but can be written as Select name, phone, account.name from contact
4. No UNION operator
5. Queries cannot be chained together

Let’s look upon an example how a malicious user input is modifying the resulting query. The following code is intended to retrieve accounts which are not deleted.

1. <! – A simple VF Page –!>
2. <apex:page controller=”SOQLInjectionController” >
3.     <apex:form>
4.         <apex:outputText value=”Enter Name” />
5.         <apex:inputText value=”{!name}” />
6.         <apex:commandButton value=”Query” action=”{!dynamicquery}“ />
7.     </apex:form>
8. </apex:page>
9. //Controller class
10. public class SOQLInjectionController{
11.     public String name {
12.         get {return name;}
13.         set {name = value;}
14.     }
15.     public PageReference dynamicquery() {
16.         String qryString = ’SELECT Id FROM Account WHERE ’ +
17.             '(IsDeleted = false and Name like \’%’ + name + ’%\’)’;
18.         queryResult = Database.query(qryString);
19.         return null;
20.     }
21. }

Let’s consider the user input under two scenarios.

When the user enters a plain input like name = XYZConsulting, the query will execute as expected.

1. // Query string
2. SELECT Id FROM Account WHERE (IsDeleted = false and Name like ’%XYZConsulting%’)

When a malicious user gives an input like this, name = test%’) OR (Name LIKE ‘ then the resulting query will be,

1. SELECT Id FROM Account WHERE (IsDeleted = false AND Name LIKE ’%test%’) OR (Name LIKE ’%’)

Since the query is not validated, the result of this query shows all the accounts not just the deleted ones.

SOQL Defense Techniques: 

There are some techniques we can apply to validate our SOQL query to prevent the SOQL injection. Some of them are listed below:

1. Static queries with bind variables
2. Type casting
3. Replacing characters
4. Whitelisting

Let’s see how these techniques are handled to prevent the vulnerability.

Static queries with bind variables

Using static queries in our application is the most recommended solution to prevent the SOQL Injection. Consider the following query which uses the user input (the var variable) directly in a SOQL query opens the application up to SOQL injection.

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

We can modify the query as below so that the user input is taken as a variable and not as an executable statement in the query.

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

For this query, even though the user gives the value as test’ LIMIT 1, it looks for any first names that are “test’ LIMIT 1” in the database and the query is efficient now.

Type Casting

In our queries, if we are sure about what data type we are going to refer, then we can type cast it; so that we can eliminate the fake input. For example, if you are expecting an input type such as Boolean or integer, you can cast the user input value to the expected type.

1. String query = ’Select Name, Address from Contact where isActive = ’ + Boolean(input);

Even though we get an erroneous input, the type casting will throw error.

Whitelisting

If we want to support dynamically selecting fields from the object we need to query, and also if we know what fields needs are expected to be selected, then we can check that the user input is one of those field names.

1. Set<String> fields = new Set<String>();
2. fields.add(‘myField1’);
3. fields.add(‘myField2’);
4. fields.add(‘myField3’);
5. if(!(fields.contains(inputField)){
6.   .. Throw error ..
7. }

We can throw error if the user input is coming in for unexpected fields.

Replacing Characters

When Type casting / whitelisting doesn’t prevent the adequate vulnerability against prevention, blacklisting otherwise called as Replacing Characters will be handy.

Consider our user input is like this,

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

And the SOQL injection input as

1. true AND ReceivesAdminInfoEmails=true

thus, the resulting query will lead to retrieving unintended data.

So, the fix can be removing all white spaces to make the erroneous input invalid.

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

Conclusion 

When we expose our data to the outside world, it is most important to check for the vulnerability against our queries. We cannot expect the users to give a valid input every time. Thus, by implementing the SOQL defense techniques, we can have a strict guideline against CRUD operations.