Tuesday, December 13, 2016

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:

SELECT 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:

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

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


6 comments:

  1. Thank You for sharing your article. I like it. We provide TIBCO Online Training in Hyderabad.

    ReplyDelete
  2. How about we start with a 3 course feast for 2. Each get to pick a tidbit, entre, and dessert, yet every one of the parts will be not exactly the standard bits you follow through on your normal menu.https://hostinglelo.in/

    ReplyDelete
  3. Thus, it would be for all intents and purposes unbelievable that you would observe such a re-vender of the BLCO rough, accepting he is a LEGITIMATE re-merchant who authentically got his item, consenting to sell his unrefined under such methodology as the above in a TTO bargain.https://onohosting.com/

    ReplyDelete
  4. I think it is miserable that individuals are sucked into figuring the main way they can bring in cash is by going on game shows or purchasing a lottery ticket or far more detestable, joining betting locales that are being moved by early evening TV promoting, particularly when there is a lot more straightforward way. I have said this previously and I will say it once more "bringing in cash is an ability and like everything abilities, it very well may be learned. https://sites.google.com/view/seoservicesindelhiindia

    ReplyDelete