• Troy Larssen 10
  • NEWBIE
  • 0 Points
  • Member since 2016

  • Chatter
    Feed
  • 0
    Best Answers
  • 1
    Likes Received
  • 0
    Likes Given
  • 1
    Questions
  • 0
    Replies

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;
    }

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;
    }