+ Start a Discussion
yarramyarram 

SOQL SOSL Injection: Dynamic SOQL can not pass security check marx any one can help me?

Hello,

 

I create a apex class, which contains some dynamic SOQLs. The function used for custom pagination and custom search functionality.

 

All of them works fine. But I tried the security check marx in my account and found dynamic SOQL caused security Issues.like SOQL Injection.

 

Error : Severity - Critical


 public List<Branch__c> getRecords()

{
return (List<Branch__c>)database.query(FetchBranchRecordsQry+' LIMIT '+PaginationForBranch.queryLimit+' OFFSET' +PaginationForBranch.offset);

}

 

string  FetchBranchRecordsQry='Select id, Name, CreatedBy.Name,Branch__c.CreatedDate,BranchName__c, BranchAdmin__r.Name, BranchEstDate__c, Active__c FROM Branch__c Order by Name DESC';

 

 

Any one can help me how to use dynamic SOQL.

 

 

Thanks,

Yarram

 

 

Sagarika RoutSagarika Rout

Instead of writting query string inside single quote , Use escapeSingleQuotes(String).
It will avoid the soql injection error.

http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_System_String_escapeSingleQuotes.htm

 

 

Regards

Sagarika Rout

SFDC Developer

digamber.prasaddigamber.prasad

Hi,

 

Yes, I can confirm that it is vulnearable to SOQL injection. Please follow below URL to fix this:-

 

http://www.salesforce.com/us/developer/docs/apexcode/Content/pages_security_tips_soql_injection.htm

 

If you still have any question, let me know.

 

Happy to help you!

yarramyarram

Hi Sagarica,

 

Thanks for reply,

 

yes, i did the changes in Database.query() and i followed the String.escapeSingleQuotes() also, but i am getting the same error

 

Error : Severity - Critical

 

 

public List<Branch__c> getRecords()

{
return (List<Branch__c>)database.query(FetchBranchRecordsQry+' LIMIT '+String.escapeSingleQuotes(String.ValueOf(PaginationForBranch.queryLimit))+' OFFSET' +String.escapeSingleQuotes(String.ValueOf(PaginationForBranch.offset)));

}

 

string  FetchBranchRecordsQry='Select id, Name, CreatedBy.Name,Branch__c.CreatedDate,BranchName__c, BranchAdmin__r.Name, BranchEstDate__c, Active__c FROM Branch__c Order by Name DESC';

 

 

please help me. if you have any sample code for Database.query() using LIMIT and OFFSET keywords please share with me

 

Thanks,

Yarram.

 

 

yarramyarram

Hi,

 

Thanks for reply,

 

yes, i did the changes in Database.query() and i followed the String.escapeSingleQuotes() also, but i am getting the same error again.

 

Error : Severity - Critical

 

 

public List<Branch__c> getRecords()

{
return (List<Branch__c>)database.query(FetchBranchRecordsQry+' LIMIT '+String.escapeSingleQuotes(String.ValueOf(PaginationForBranch.queryLimit))+' OFFSET' +String.escapeSingleQuotes(String.ValueOf(PaginationForBranch.offset)));

}

 

string  FetchBranchRecordsQry='Select id, Name, CreatedBy.Name,Branch__c.CreatedDate,BranchName__c, BranchAdmin__r.Name, BranchEstDate__c, Active__c FROM Branch__c Order by Name DESC';

 

 

please help me. if you have any sample code for Database.query() using LIMIT and OFFSET keywords please share with me

 

Thanks,

Yarram.

digamber.prasaddigamber.prasad

Hi,

 

Could you please try below. I am assuming you are not using this queryString anywhere else.

 

public List<Branch__c> getRecords()
{
	List<Branch__c> lstBranch = [Select id, Name, CreatedBy.Name,Branch__c.CreatedDate,BranchName__c, BranchAdmin__r.Name, BranchEstDate__c, Active__c FROM Branch__c Order by Name DESC LIMIT :PaginationForBranch.queryLimit OFFSET :PaginationForBranch.offset]
	
	return lstBranch;
}

 Let me know if you have any specific question.

 

Happy to help you!

 

 

yarramyarram

Hi,

 

No, i am using same query string 6 more places. for that only i am using the Database.query() (Dynamic SOQL). Is there any other way to solve this SOQL Injection Issue in Dynamic SOQL queries?  please give me the suggestion for this.

public PaginationUtil PaginationForBranch{get;set;}

string  FetchBranchRecordsQry='Select id, Name, CreatedBy.Name,Branch__c.CreatedDate,BranchName__c, BranchAdmin__r.Name, BranchEstDate__c, Active__c FROM Branch__c Order by Name DESC';

 string  FetchBranchCaseRecordCount='select COUNT(id) cnt from Branch__c';

PaginationForBranch=new PaginationUtil(FetchBranchRecordsQry,FetchBranchCaseRecordCount);

public List<Branch__c> getRecords()

{
return (List<Branch__c>)database.query(FetchBranchRecordsQry+' LIMIT '+PaginationForBranch.queryLimit+' OFFSET' +PaginationForBranch.offset);

}

digamber.prasaddigamber.prasad

Hi,

 

Could you please try below code snippet. We are using the same way and have cleared technical review as well.

 

public List<Branch__c> getRecords()
{
String query = FetchBranchRecordsQry + ' limit :PaginationForBranch.queryLimit OFFSET :PaginationForBranch.offset';
return (List<Branch__c>)database.query(query);
}

 

Let me know if you have any problem.

 

Happy to help you!

 

 

yarramyarram

HI, 

     As you mentioned i tried the same way and I submitted to checkmarx review, again review was failed because of the SOQL Injection error. How can we over come this problem, please help me. below is my changed code.

 

public List<Branch__c> getRecords()
    {
        Integer qlimit=PaginationForBranch.queryLimit;
        Integer qOffset=PaginationForBranch.offset;        
        String FetchQuery=FetchBranchRecordsQry+' limit :qlimit OFFSET :qOffset';        
 
return (List<Branch__c>)database.query(FetchQuery);
    }

 

digamber.prasaddigamber.prasad

Hi,

 

Strange it is. We have code base in which we have same way of dynamic query and they passed it without any problem. Let me look at other possible way.

yarramyarram

Hi, here is i am getting actual error code part which is on the check marx PDF Report document. 

 

Query Name - SOQL_SOSL_Injection
Severity - Critical

24. public BranchCtrl() //branchctrl.cls
...
30. BranchId = apexpages.currentPage().getParameters().get('BranchId');// Why this one showing
65. <apex:column headerValue="Branch Established Date" value="!con.BranchEstDate__c}"/> //branches.page// Why this one showing
34. public List<Branch__c> getRecords() //branchctrl.cls
...
39. String FetchQuery=FetchBranchRecordsQry+' limit :qlimit OFFSET :qOffset';
...
42. return (List<Branch__c>)database.query(FetchQuery);

 i want to ask one question to you , which review(Salesforce Security(Paid) Review or Ceckmarx(Free one) Review) you have passed dynamic query code? please let me know.

digamber.prasaddigamber.prasad

Hi,

 

We went for Checkmarx Review.

yarramyarram

Ok, we also went for checkmarx review, why its giving to me dynamic soql query problems.

 

Thanks,

Yarram.

digamber.prasaddigamber.prasad

That's what puzzle me too!