Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
MedhanieHabteMedhanieHabte 

Stuck on prevent SOQL injection trailhead

Hi all, I am stuck on the prevent SOQL injection trailhead, where I am looking to determine which portions of the code I would need to invoke escapeSingleQuotes or whitelist to enable. I've made several attempts at investigation to little avail. I have appended my code are there any steps I would need to take. My goal is to assess the code to see how it works and what I would need to do.

Hope it helps.
  
public class Prevent_SOQL_Injection_Challenge {

    public string textOne {get; set;}
    public string textTwo {get; set;}
    public string comparator {get; set;}
    public string numberOne {get; set;}

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


    public PageReference stringSearchOne(){
        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';
        string whereClause = '';

        if(textOne != null && textOne!=''){
                whereClause += 'name like  \'%'+textOne+'%\' ';
        }

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

        return null;
    }


    public PageReference stringSearchTwo(){
        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';
        string whereClause = '';

        if(textTwo != null && textTwo!=''){
                whereClause += 'Storage_Location__r.name like  \'%'+textTwo+'%\' ';
        }

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

        return null;
    }


    public PageReference numberSearchOne(){
        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';
        string whereClause = '';

        if(numberOne != null && comparator != null){
            whereClause += 'Quantity__c '+comparator+' '+numberOne+' ';
        }

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

        return null;
    }

}

 
Best Answer chosen by MedhanieHabte
Michael_WhiteMichael_White
John - like most of the Apex Trailhead modules, the assessment appears to be looking for very specific values in the updated class. I spent a fair amount of time over the past couple of days trying out different permutations which all appear to address the issue but wouldn't pass the challenge.  The error message provided by the module does leave a bit to be desired as there is no clear way to determine if ANY of the code matches the assessment criteria.  

Here is a general guide for the changes I made to the class which eventually passed the assessment:
  • Used string.escapeSingleQuote for textOne
  • Used string.escapeSingleQuote for textTwo
  • Used value whitelisting for the comparator to ensure values were <,>, or =.  
Try removing the replaceAll from your statements on textOne and textTwo and I think it may work for you as you already have the whitelisting set for the comparator item.

 

All Answers

Vivek DVivek D
SOQL injection is basically when you are passing parameters from text field or URL then it can be altered to provide different information. In your example you are using 4 parameters 
public string textOne {get; set;}
public string textTwo {get; set;}
public string comparator {get; set;}
public string numberOne {get; set;}
And I guess they are coming from a input from page or url parm. You need to safegaurd your code to prevent from SOQL injection using escapeSingleQuotes method
For example :- 
  
if(textOne != null && textOne!=''){
                whereClause += 'name like  \'%'+escapeSingleQuotes(textOne)+'%\' ';
}
// You need to add this to all your parameters/attributes



 
John Lay 9John Lay 9
I'm stuck too. I've tried several of the sugguestions in various combinations but still do not pass the module.
These seem to test out via the form, not with check challenge.
Any ideas?
  
public class Prevent_SOQL_Injection_Challenge {

    public string textOne {get; set;}
    public string textTwo {get; set;}
    public string comparator {get; set;}
    public string numberOne {get; set;}

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


    public PageReference stringSearchOne(){
        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';
        string whereClause = '';
        string textNameSearch = '';

        system.debug('One');
        system.debug('textOne: ' + textOne);
        
        if(textOne != null && textOne!=''){
                whereClause += 'name like  \'%'+string.escapeSingleQuotes(textOne.replaceAll('[^\\w]',''))+'%\' ';
        }

        if(whereClause != ''){
            //whereclause_records = database.query(query+' where '+whereClause+' Limit 10');
            //textOneSearch = '%' + string.escapeSingleQuotes(textOne) + '%';
            //textOneSearch = textOneSearch.replaceAll('[^\\w]','');
            textOne = '%' + string.escapeSingleQuotes(textOne).replaceAll('[^\\w]','') + '%';
            //textOne = '%' + string.escapeSingleQuotes(textOne) + '%';
            
            whereclause_records = [SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c where name like :textOne  Limit 10];
            
        }

        return null;
    }


    public PageReference stringSearchTwo(){
        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';
        string whereClause = '';
        string textStorageSearch = '';

        system.debug('Two');
        system.debug('textTwo: ' + textTwo);

        if(textTwo=='Storeroom' || textTwo=='Stables'){
                whereClause += 'Storage_Location__r.name like  \'%'+string.escapeSingleQuotes(textTwo.replaceAll('[^\\w]',''))+'%\' ';
                //whereClause += 'Storage_Location__r.name = :textTwo ';
                textStorageSearch = textTwo;
        }

        if(whereClause != ''){
            //whereclause_records = database.query(query+' where '+whereClause+' Limit 10');
            whereclause_records = [SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c where Storage_Location__r.name = :textStorageSearch];
        }

        return null;
    }


    public PageReference numberSearchOne(){
        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';
        string whereClause = '';
        Decimal numberOneValue = 0;

        system.debug('Three');
        system.debug('numberOne: ' + numberOne);

        if(numberOne != null && (comparator == '=' || comparator == '>' || comparator == '<')){
            try {
                numberOneValue = Decimal.valueof(numberOne);
            } catch(exception ex) {
                numberOneValue = 0;
            }
            //whereClause += 'Quantity__c '+string.escapeSingleQuotes(comparator)+' '+string.valueof(numberOne)+' ';
            
            
            
            if(comparator == '=') {
                whereclause_records = [SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c 
                                        FROM Supply__c Where Quantity__c = :numberOneValue Limit 10];
            }
            if(comparator == '>') {
                whereclause_records = [SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c 
                                        FROM Supply__c Where Quantity__c > :numberOneValue Limit 10];
            }
            if(comparator == '<') {
                whereclause_records = [SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c 
                                        FROM Supply__c Where Quantity__c < :numberOneValue Limit 10];
            } 
                     
        }
        /*
        system.debug('Where Clause: ' + whereClause );
        
        if(whereClause != ''){
            whereclause_records = database.query(query+' where '+whereClause+' Limit 10');
        }
        */
        
        return null;
    }

}

 
Michael_WhiteMichael_White
John - like most of the Apex Trailhead modules, the assessment appears to be looking for very specific values in the updated class. I spent a fair amount of time over the past couple of days trying out different permutations which all appear to address the issue but wouldn't pass the challenge.  The error message provided by the module does leave a bit to be desired as there is no clear way to determine if ANY of the code matches the assessment criteria.  

Here is a general guide for the changes I made to the class which eventually passed the assessment:
  • Used string.escapeSingleQuote for textOne
  • Used string.escapeSingleQuote for textTwo
  • Used value whitelisting for the comparator to ensure values were <,>, or =.  
Try removing the replaceAll from your statements on textOne and textTwo and I think it may work for you as you already have the whitelisting set for the comparator item.

 
This was selected as the best answer
John LayJohn Lay
Michael, 
Thanks for the info, but I'm still not seeing what's wrong. I've tried several combinations and find this frustrating.
I've paried it down to the simplest of code and still cannot get the code to pass. Here is my current version.
  
public class Prevent_SOQL_Injection_Challenge {

    public string textOne {get; set;}
    public string textTwo {get; set;}
    public string comparator {get; set;}
    public string numberOne {get; set;}

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


    public PageReference stringSearchOne(){
        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';
        string whereClause = '';

        if(textOne != null && textOne!=''){
                whereClause += 'name like  \'%'+String.escapeSingleQuotes(textOne)+'%\' ';
        }

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

        return null;
    }


    public PageReference stringSearchTwo(){
        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';
        string whereClause = '';

        if(textTwo != null && textTwo!=''){
                whereClause += 'Storage_Location__r.name like  \'%'+String.escapeSingleQuotes(textTwo)+'%\' ';
        }

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

        return null;
    }


    public PageReference numberSearchOne(){
        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';
        string whereClause = '';

        if(numberOne != null && comparator != null && (comparator == '=' || comparator == '<' || comparator == '>')){
            whereClause += 'Quantity__c '+String.escapeSingleQuotes(comparator)+' '+String.escapeSingleQuotes(numberOne)+' ';
        }

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

        return null;
    }

}

I've also tried this verson with forced whitelisting: 
public PageReference numberSearchOne(){
        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';
        string whereClause = '';
        Decimal numberOneValue = 0;

        system.debug('Three');
        system.debug('numberOne: ' + numberOne);

        if(numberOne != null && (comparator == '=' || comparator == '>' || comparator == '<')){
            try {
                numberOneValue = Decimal.valueof(numberOne);
            } catch(exception ex) {
                numberOneValue = 0;
            }
            //whereClause += 'Quantity__c '+string.escapeSingleQuotes(comparator)+' '+string.valueof(numberOne)+' ';
            whereClause += 'Quantity__c ';
            
            
            
            if(comparator == '=') {
                //whereclause_records = [SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c 
                //                        FROM Supply__c Where Quantity__c = :numberOneValue Limit 10];
                whereClause += '= ' + string.escapeSingleQuotes(numberOne);
                //whereClause += '= ' +numberOneValue;
                //whereClause += '= ' +string.ValueOf(numberOne);
            }
            if(comparator == '>') {
                //whereclause_records = [SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c 
                //                        FROM Supply__c Where Quantity__c > :numberOneValue Limit 10];
                whereClause += '> '+ string.escapeSingleQuotes(numberOne);
                //whereClause += '> ' +numberOneValue;
                //whereClause += '> ' +string.ValueOf(numberOne);
            }
            if(comparator == '<') {
                //whereclause_records = [SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c 
                //                        FROM Supply__c Where Quantity__c < :numberOneValue Limit 10];
                whereClause += '< '+ string.escapeSingleQuotes(numberOne);
                //whereClause += '< ' +numberOneValue;
                //whereClause += '< ' +string.ValueOf(numberOne);
            } 
                     
        }
        
        system.debug('Where Clause: ' + whereClause );
        
        if(whereClause != ''){
            whereclause_records = database.query(query+' where '+whereClause+' Limit 10');
        }
        
        
        return null;
    }

Any pointers will be greatly appreciated.
EmilienGuichardEmilienGuichard
Did someone managed to complete this challenge ?
Michael_WhiteMichael_White
I was able to complete it but it seems that the challenge is looking for some very specific terms in the code. It took several tries but I eventually found a combination that worked. 
EmilienGuichardEmilienGuichard
Hi Michael, thanks for your answer.

I have escaped textOne and textTwo, changed numberOne to Integer and verified comparator values in the if condition but still got the error...

Could you please advise ?

Thanks a lot.
Michael_WhiteMichael_White
Hi Emilien,

Can you share your code for the challenge?  I can then provide some specific guidance on the differences between that and the code I used to complete the module.

-Mike
EmilienGuichardEmilienGuichard
sure, here is my code :
  
public class Prevent_SOQL_Injection_Challenge {

    public string textOne {get; set;}
    public string textTwo {get; set;}
    public string comparator {get; set;}
    public Integer numberOne {get; set;}

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


    public PageReference stringSearchOne(){
        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';
        string whereClause = '';

        if(textOne != null && textOne!=''){
                whereClause += 'name like  \'%'+String.escapeSingleQuotes(textOne)+'%\' ';
        }

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

        return null;
    }


    public PageReference stringSearchTwo(){
        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';
        string whereClause = '';

        if(textTwo != null && textTwo!=''){
                whereClause += 'Storage_Location__r.name like  \'%'+String.escapeSingleQuotes(textTwo)+'%\' ';
        }

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

        return null;
    }


    public PageReference numberSearchOne(){
        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';
        string whereClause = '';

        if(numberOne != null && comparator != null && (comparator == '=' || comparator == '<' || comparator == '>')){
            whereClause += 'Quantity__c '+comparator+' '+String.valueOf(numberOne)+' ';
        }

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

        return null;
    }

}

 
Michael_WhiteMichael_White
I only see one minor difference between your code and what I used to pass the challenge. It would appear to be inconsequential in terms of functionality so perhaps there is very strict checking of the code.  

See if changing line 47 to this makes a difference for you: 
if(numberOne != null && (comparator == '=' || comparator == '>' || comparator == '<')){

 
EmilienGuichardEmilienGuichard
Wouhou! This was it!
Very odd indeed but I successfully pass the challenge.

Thanks a lot for your help.
Alexandre Lachmann (SF)Alexandre Lachmann (SF)
Thanks a lot
If you write if(numberOne != null) { if (comparator == '=' || comparator == '>' || comparator == '<') { : it does not work !!!
Federico Giust 7Federico Giust 7
This worked for me  
public class Prevent_SOQL_Injection_Challenge {

    public string textOne {get; set;}
    public string textTwo {get; set;}
    public string comparator {get; set;}
    public string numberOne {get; set;}

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


    public PageReference stringSearchOne(){
        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';
        string whereClause = '';

        if(textOne != null && textOne!=''){
                whereClause += 'name like  \'%'+String.escapeSingleQuotes(textOne)+'%\' ';
        }

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

        return null;
    }


    public PageReference stringSearchTwo(){
        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';
        string whereClause = '';

        if(textTwo != null && textTwo!=''){
                whereClause += 'Storage_Location__r.name like  \'%'+String.escapeSingleQuotes(textTwo)+'%\' ';
        }

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

        return null;
    }


    public PageReference numberSearchOne(){
        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';
        string whereClause = '';

        if(numberOne != null && comparator != null && (comparator == '<' || comparator == '>' || comparator == '=')){
            whereClause += 'Quantity__c '+String.escapeSingleQuotes(comparator)+' '+String.valueOf(Integer.valueOf(numberOne))+' ';
        }

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

        return null;
    }

}
Boris GichevBoris Gichev
Thank you Federico Giust 7
Deepika1007Deepika1007
Thanks Federico !! It worked.
David Shannon 13David Shannon 13
You need to SingleQuotesEscape the comparator as well - even though it does not say this in the challenge requirements
ANANYA SINGHA ROYANANYA SINGHA ROY
this is work for me


public class Prevent_SOQL_Injection_Challenge {
 
    public string textOne {get; set;}
    public string textTwo {get; set;}
    public string comparator {get; set;}
    public string numberOne {get; set;}
 
  public List<Supply__c> whereclause_records {get; set;}

 

 

    public PageReference stringSearchOne(){

        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';

        string whereClause = '';

 

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

                whereClause += 'name like  \'%'+String.escapeSingleQuotes(textOne)+'%\' ';

        }

 

        if(whereClause != ''){

            whereclause_records = database.query(query+' where '+whereClause+' Limit 10');

        }

 

        return null;

    }

    public PageReference stringSearchTwo(){

        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';

        string whereClause = '';

 

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

                whereClause += 'Storage_Location__r.name like  \'%'+String.escapeSingleQuotes(textTwo)+'%\' ';

        }

        if(whereClause != ''){

            whereclause_records = database.query(query+' where '+whereClause+' Limit 10');

        }

 

        return null;

    }

 

    public PageReference numberSearchOne(){

        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';

        string whereClause = '';

 

        if(numberOne != null && comparator != null && (comparator == '<' || comparator == '>' || comparator == '=')){

            whereClause += 'Quantity__c '+String.escapeSingleQuotes(comparator)+' '+String.valueOf(Integer.valueOf(numberOne))+' ';

        }

 

        if(whereClause != ''){

            whereclause_records = database.query(query+' where '+whereClause+' Limit 10');

        }

 

        return null;

    }

 
}
 
Caleb Kuester 27Caleb Kuester 27
Federico Giust 7's solution worked for me, but the only thing I did wrong was put String.escapeSingleQuotes() on whereClause instead of on textOne/textTwo.

The problem with my approach was that whereClause string needed to have single-quotes in it.

It is unnecessary to use String.valueOf() for the Integer though. Maybe he's an experienced C/C++ programmer and there's a big difference between '7' and 7, but in Apex, they are both equal.
Akash Pandey 25Akash Pandey 25
you dont need to worry about comparator as that is a picklist on the VF page. See the correct code below-

public class Prevent_SOQL_Injection_Challenge {

    public string textOne {get; set;}
    public string textTwo {get; set;}
    public string comparator {get; set;}
    public string numberOne {get; set;}

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


    public PageReference stringSearchOne(){
        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';
        string whereClause = '';

        if(textOne != null && textOne!=''){
                whereClause += 'name like  \'%'+String.escapeSingleQuotes(textOne)+'%\' ';
        }

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

        return null;
    }


    public PageReference stringSearchTwo(){
        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';
        string whereClause = '';

        if(textTwo != null && textTwo!=''){
                whereClause += 'Storage_Location__r.name like  \'%'+String.escapeSingleQuotes(textTwo)+'%\' ';
        }

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

        return null;
    }


    public PageReference numberSearchOne(){
        string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';
        string whereClause = '';

        if(numberOne != null && comparator != null && (comparator == '<' || comparator == '>' || comparator == '=')){
            whereClause += 'Quantity__c '+comparator+String.valueOf(Integer.valueOf(numberOne));
        }

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

        return null;
    }

}