Salesforce - Developer - Security - SOQL Injection

Security

// Salesforce - Developer - Security - SOQL Injection:

SOQL is just the “Select” command of SQL:

1. No Insert, Update, or Delete
2. No command execution

We can not use wild card (*) for fields, all fields must be explicitly typed.

SOQL does not include the “join” statement, but can directly include parent 
information:

Select name, phone, account.name from contact

SOQL does not permit the Union operator.

SOQL can only query Salesforce objects.  Metadata records cannot be queried 
using SOQL.

Queries cannot be chained together.

SOQL is more narrow than SQL, which reduces the “attack surface,” or the amount 
of conceptual area in which an attacker might find a way to exploit an 
application.

1. No command injection, therefore no ability to exploit the underlying OS 
   running the Salesforce service.

2. No delete method, therefore no ability to interact destructively.

3. No insert or update methods, therefore no ability to add data, user accounts, 
   or permissions to the system.

For Salesforce, SOQL injection only occurs when a developer uses dynamic SOQL 
Queries via the database.query.  For example:

String query = ‘select id from account where name =\’’;
query += userinput +’\’’
queryResult = Database.query(query);

In the above code block, a user would only need to input a name that looks like 
the following to fundamentally change the query:

Bob’ OR AnnualRevenue > 1000000 OR name =‘Bob

This would result in a SOQL statement:

select id from account where name = ‘Bob’ OR AnnualRevenue > 1000000 
  OR name =‘Bob’

To prevent SOQL injection:

1. Static Query + Bind Variable:
   queryResult = [select id from account where name:=var];

2. Wrap strings in string.escapeSingleQuotes():

3. Replace unexpected characters:
   if a field is boolean there is no need for spaces, quotes, or digits to be 
   acceptable.

This SOQL injection is relatively simple, but not always obvious. Suppose you have the following code:

public string x {get; set;}
string query = 'select id from Account where numberOfEmployees >' + x;

The above might be vulnerable to SOQL injection if x is under user control, but 
there is an obvious fix! numberOfEmployees is an integer, so x could be an 
integer, which would not allow any non-numerical characters (preventing SOQL 
injection). Let's use this code instead:

public integer x {get; set;}
string query = 'select id from Account where numberOfEmployees >' + 
  string.valueOf(x);

In the above code, String.valueOf(x) returns an integer similar to the parseInt
function in JavaScript.

When using dynamic queries, string.escapeSingleQuotes will prevent SOQL 
injection:

String query = ‘select id from account where name =\‘‘ + 
  string.escapesinglequotes(var)+’\’’;

This does not mean it cures all SOQL injection vulnerabilities.  Consider the 
following:

String query = ‘select id from user where isActive=‘ +
  string.escapesinglequotes(var);

As a tricky attacker, I could input the string 
“true AND ReceivesAdminInfoEmails=true”

This input does not include any single quotes, and thus bypasses the 
string.escapeSingleQuotes defense mechanism.

public class SOQLI_Where_Fix_Demo {

    /*
    SOQL #1 Fix Exercise

    The problem with the SOQL below is that the variables numerical and textual 
    are all under the users control, making them all vulnerable to SOQL 
    injection, or SOQLi. To protect against SOQLi, secure each user controlled 
    input placed into a dynamic query such that the user can only enter expected 
    answers. Each variable will require a different approach, so lets address 
    them in order:

    a) string numerical: This might be easily fixed by changing the variable 
       definition from string to something a little more strict...

    b) string textual: This item is a string that needs to be a string, so what 
       can we do? Try the string method String.EscapeSingleQuotes()! It will 
       take any single quote (') it finds and  escape it. This prevents the 
       single quote from being used in code context and changing the meaning 
       of the query (no more escaping).

    */
    public string numerical {get; set;}
    public string comparator {get; set;}
    public string textual {get; set;}

    public List<Opportunity> whereclause_records {get; set;}

    public PageReference whereclause_search(){
        string query = 'select id, name, type, probability, isPrivate, 
            Searchable_Description__c from Opportunity';
        string whereClause = '';

        if(numerical != null && comparator != null){
            /*
              a) With the variable definition of numerical changed, you may 
                    need only use "string.valueOf(numerical)" to update the code 
                    below

              NOTE: For production grade application, we would advise adding in 
                    nice error messages and failing gracefully rather than our 
                    quick and easy approach of quietly ignoring the input.

            */        

            whereClause += 'probability '+comparator+' '+numerical+' ';
        }

        if(textual != null && textual != ''){
            /*
                b) For textual, try using string.escapeSingleQuotes()
                https://www.salesforce.com/us/developer/docs/apexcode/Content/
                    apex_methods_system_string.htm#
                    apex_System_String_escapeSingleQuotes

            */
            if(whereClause != ''){
                whereClause += 'and Searchable_Description__c like  \'%'+textual+
                    '%\' ';
            } else {
                whereClause += 'Searchable_Description__c like  \'%'+textual+
                    '%\' ';
            }
        }

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

        return null;
    }

}

Locate the variable declaration "public string numerical {get; set;}" and change 
it to "public integer numerical {get; set;}"

Locate where numerical is added to the query and change "+numerical+" to " +
  string.valueOf(numerical)+"

The second part of this demo filters on searchable_description__c, a string, so 
our first fix does not apply here. Instead, we need to find some way to keep our 
string input from being interpreted as code. Luckily, Apex includes a method 
that does just that, string.escapeSingleQuotes(). This method adds an escape 
character in front of every single quote it finds which prevents the user from 
breaking out of the string context of the query. Consider the following 
scenario:

query = 'select id from opportunity where searchable_description__c like \'%'+
    user_input+'%\''
user_input = %' and searchable_description__c like '%
final_query =  'select id from opportunity where 
  searchable_description__c like '%%' and searchable_description__c like '%%'

The above only functions because the user can include 2 single quotes in their 
input, closing out the end of the first single quote context and opening the 
beginning of the second single quote context. Now picture this scenario again 
using string.escapeSingleQuotes:

query = 'select id from opportunity where searchable_description__c like \'%'+ 
  string.escapeSingleQuotes(user_input)+'%\''
user_input = deal%' and searchable_description__c like '%deal
final_query =  'select id from opportunity 
  where searchable_description__c like \'%deal%\' and 
  searchable_description__c like \'%deal%\'

The backslash escape characters prevent the single quotes from being 
interpreted as code, so the final result is a query with a single filter on 
searchable_description__c with a really long and ugly filter value. Since single 
quotes cannot be used to end filters and start new ones, the user is unable to 
perform a SOQL injection!

whereClause += 'and Searchable_Description__c like  \'%'+
  string.escapeSingleQuotes(textual)+'%\' ';

Replacing Unexpected Characters:

As seen previously, we cannot use string.escapeSingleQuotes() for the following 
query:

String query = ‘select id from user where isActive=‘+string.escapesinglequotes(var);

What can we use to secure the query in this circumstance? Character replacement.

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

The replaceAll method above takes all the characters included and replaces them 
with blank spaces. SOQL injection is effectively mitigated in this way.

public class SOQLI_URL_Param_Fix_Demo {
    /*
    SOQL Fix Exercise

    We often see developers try to use string.escapeSingleQuotes() as a silver 
    bullet for fixing SOQL injection. Is it?

    For the first part of this exercise, use string.escapeSingleQuotes() on 
    objectName below, and then attempt a SOQL injection attack. Try to discover 
    which opportunity is the most expensive. 

    HINT: Try writing a SOQL injection that does not use any single quotes!

    Once you have completed this step, you will have proven that 
    string.escapeSingleQuotes() is not a silver bullet for SOQL injection, 
    because SOQL does not require a single quote in order to leak information.

    To really, protect the below query, you will have to try another method. You 
    are looking for something that takes user input and validates that it meets 
    expected criteria.

    You have a couple of options here:
        1. Validate that the value in obj matches one of the expected values 
            like account or contact
        2. Validate that there are no special characters like space in obj. 
            While this generally works well for many dynamic SOQL, it is not 
            best in this situation 
           since the user could still provide an unexpected object name.
    */

    Public String objectName {get;set;}
    Public List<sobject> sObjectList {get;set;}
    String QueryString;
    Public List<string> fieldList{get;set;}

    public PageReference objectclause_search(){
        string obj = ApexPages.currentPage().getParameters().get('object');
        // #2 add in some criteria to validate the value of obj... you could try 
        // something like the following: if(obj=='...'||obj =='...'||obj =='...'){ 
        if(obj != null){ 
            // #1 Use string.EscapeSingleQuotes() around obj
            string query = 'select id, name from '+obj+' limit 10';
            fieldList = New List<string>();
            fieldList.add('id');
            fieldList.add('name');
            sObjectList = Database.Query(query);
        }
        return null;
    }

    public PageReference add_urlParam(){  
        Pagereference P = Page.SOQLI_URL_Param_Fix_Demo;
        P.getParameters().put('object', objectName);
        P.setRedirect(true);
        return P;
    }
}

Sometimes there are situations where you cannot typecast or use 
string.escapeSingleQuotes() to protect your code and whitelisting inputs can be 
an excellent choice.

public class SOQLI_QueryString_Fix_Demo {

    /*
    SOQL #3 Fix Exercise

    Below, the developers have done something very confusing, and ill-advised 
    (and this isn't just a made up demo, we've actually seen this).

    The first method "update_Query" builds a query and returns it. As you may 
    have seen in the previous demo, this query is then stored client side in a 
    hidden field. The second method "hiddenParameter_search"
    takes this client side query and performs it. 

    Based on this architecture think about where the SOQLi protection needs to 
    be, and how you would write it. Once you are ready, scroll down.

    */
    public string hidden_Query {get; set;}
    Public boolean name_Field {get; set;}
    Public boolean numberofemployees_Field {get; set;}
    Public boolean type_Field {get; set;}
    Public boolean industry_Field {get; set;}

    Public List<sobject> sObjectList3 {get;set;}
    Public List<string> fieldList3 {get;set;}

    public PageReference update_Query(){
        hidden_Query = '';
        if(name_Field){
            if(hidden_Query == ''){
                hidden_Query+='SELECT name';
            } else {
                hidden_Query+=', name';
            }
        }
        if(numberofemployees_Field){
            if(hidden_Query == ''){
                hidden_Query+='SELECT numberofemployees';
            } else {
                hidden_Query+=', numberofemployees';
            }
        }
        if(type_Field){
            if(hidden_Query == ''){
                hidden_Query+='SELECT type';
            } else {
                hidden_Query+=', type';
            }
        }
        if(industry_Field){
            if(hidden_Query == ''){
                hidden_Query+='SELECT industry';
            } else {
                hidden_Query+=', industry';
            }
        }
        hidden_Query+=' FROM account LIMIT 10';
        return null;
    }

    public PageReference hiddenParameter_search(){
        string query = hidden_Query;
        /*
            The string "query" is passed directly to Database.query(), so any 
            fix needs to happen after the value of query is set but before it 
            is passed to Database.query(). 

            But now we come to the real problem: is it possible to secure an 
            entire query that is under the user's control?

            Using the query in update_Query() as a guide, try and secure the 
            final query string using methods you have learned from the previous 
            examples. Would these methods still function if this query was 
            more complicated? In the end, the best solution for a query like 
            this is an architecture change. Do not accept an entire query from 
            a client side request, request only the portions that should be
            controlled by the user and sanitize those. 

        */
        fieldList3 = New List<string>(
            query.substringBefore('FROM').
                substringAfter('SELECT').replaceAll(' ','').split(',',-1));
        sObjectList3 = Database.Query(query);
        return null;
    }
}

It is much easier to redesign the architecture to something more secure! If you 
must use dynamic queries, keep in mind the primary ways to protect your code 
and design around them!
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License