Prevent SOQL Injection in Your Code

Question

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.

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

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

    return null;
}
}

Answer 1

I see a few things wrong with your code. The first is, you're not utilizing the type casting that the challenge talks about. Try changing the "numberOne" member variable to an Integer and using the appropriate type casting in stringSearchThree.

I also added checks into stringSearchThree to check whether the comparison operator is < or >. I'm not sure if you need that piece or not.

Last, some of the checks are case sensitive, so make sure you update all the occurrences of string.escapeSingleQuotes to String.escapeSingleQuotes

Answer 2

Based on the fact that comparator has a public set and that you don't actually set the value anywhere in your code, I'm going to guess you pull this value from a user input on the page (you didn't share your markup). Same goes for numberOne.

Even if you use a picklist, set the input type to number, etc. the value sent back to the server can be manipulated, and these strings should be escaped as well. Note that it doesn't make sense to call String.valueOf on a String instance. Your numberOne property is already a string.

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