+ Start a Discussion
Troy Larssen 10Troy Larssen 10 

Bug in Prevent a SOQL injection attack challenge?

I've gotten through the first few challenges without any real issue.  I'm stuck on this one, however.  In frustration I've used 'text.escapeSingleQuotes' combined with space replacement and whitelisting for the first 2 methods and then casting for the third since it's a Number.  I fail to see how this isn't enough to prevent SOQL injection, specifically because of the white listing.  I've tested against the whitelisting and it seems to be working correctly.  

I just wonder if this is a case of the challenge looking for a particular answer or I'm just missing something.

 


    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.replaceAll('[^\\w]', ''))+'%\'';
                // textOne.replaceAll('[^\\w]', '')
        }
        Set<String> n = new Set<String>{'Barley','Beans','Bread','Cheese','Eels','Figs','Herrings','Horses','Malt','Oats','Raisins','Rice','Salt Beef','Salt Pork','Venison','Water','Wine'};
        if(whereClause != ''){
            if(n.contains(textOne)) {
                whereclause_records = database.query(query+' where '+whereClause+' Limit 10');        
            } else {
                whereclause_records = database.query(query+' where name = \'Venison\' 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.replaceAll('[^\\w]', ''))+'%\' ';
        }

        if(whereClause != ''){
            if(textTwo == 'Storeroom' || textTwo == 'Stables') {
                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+' '+string.valueOf(numberOne)+' ';
        }

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

        return null;
    }
Saravanan Gengan 9Saravanan Gengan 9
Change 
public string numberOne {get; set;}

to 
public Integer numberOne {get; set;}

and recheck.
PawanKumarPawanKumar
Below code works for me.

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){
        whereClause += 'Quantity__c '+comparator+' '+string.ValueOf(numberOne)+' ';
    }

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

    return null;
}
}

Note : Please mark it correct if it is useful for you.
Kelly FormigaKelly Formiga
I'm using this code but this is showing error: Your Apex code still appears to be vulnerable to SOQL injection. Please check your code again.
I need help please.

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){
        whereClause += 'Quantity__c '+comparator+' '+string.ValueOf(numberOne)+' ';
    }

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

    return null;
}
}