Best way to deal with user supplied inputs in dynamic SOQL query -SOQL Injection

Through SOQL Injection, end user can play with the data present in your organization. End users can specify input which can alter your dynamic SOQL query and return sensitive data to user on UI.

Consider a scenario where user search account present in your org by specifying account name as a search text. Below is sample VF and apex code which explain SOQL injection.

Suppose you enter "test" and click on search, system will return all account name as test. Below is SOQL query which will be executed:

Query StringSELECT Id, name, industry, BillingStreet, BillingState, BillingCity, BillingCountry FROM Account WHERE Name like '%test%' 

Now if you enter " test%' OR Name LIKE '% " and click on search account button, then system will return all account in system. 

Actually after entering above text, SOQL query will become like this:

Query StringSELECT Id, name, industry, BillingStreet, BillingState, BillingCity, BillingCountry FROM Account WHERE Name like '%test%' OR Name LIKE '%%' 

SOQL injection allow end users to reconstruct your SOQL and fetch sensitive data from your org.

Resolution:

• Try to use static SOQL and bind user input variable in that.
• If you have to use dynamic SOQL for your requirement, then user use the escapeSingleQuotes method for all user input strings. This add escape character (\) to all single quotation marks in string that is specified by end user. 

Below is modified apex code to avoid SOQL injection:

public PageReference query() {

        if(name !=null && name !=''){

            name = String.escapeSingleQuotes(name);

            accList = new List<Account>();

            queryString= 'SELECT Id, name, industry, BillingStreet, BillingState, BillingCity, BillingCountry   FROM Account WHERE ' + ' Name like \'%' + name + '%\'';

            accList = Database.query(queryString);

        }else{

            ApexPages.addmessage(new ApexPages.message(ApexPages.severity.ERROR,'Please enter search text value'));

        }

        return null;

    }

Now if user enter " test%' OR Name LIKE '% "  and click on search account button, the dynamic SOQL query will become as mentioned below and system will not display anything on UI.

Query StringSELECT Id, name, industry, BillingStreet, BillingState, BillingCity, BillingCountry FROM Account WHERE Name like '%test%\' OR Name LIKE \'%%' 

More Blogs>>: Tooling API Overview     Parsing JSON in Apex     Dynamic Apex in Salesforce