Soql Injection in dynamic query in checkmarx Scanner report

Question

My Apex class for dynamic query is

if(!String.isEmpty(SearchGrpName))
 SearchGrpNum = '\'%' + String.escapeSingleQuotes(GrpNumZ.trim()) + '%\'';
  whereClause += 'Accountnumber like ' +SearchGrpNum+' and '; 
  }           
 }
  if(!String.isEmpty(SearchGrpName))
  {
  SearchGrpName = '\'' + String.escapeSingleQuotes(SearchGrpName.trim())+'%\'';
  whereClause += 'Name like ' +SearchGrpName+' and ';
  }
 if(!String.isEmpty(SearchGrpYr) && sgrpnm != 'Y')
  {
   SearchGrpYr = '\'' + String.escapeSingleQuotes(SearchGrpYr.trim())+'\'';
   whereclause += 'HBC_Grp_Yr__c = ' +SearchGrpYr ;
  }

 whereClause = whereClause.removeEnd(' and ');
 if(!String.isEmpty(whereClause))
 {
      queryString = 'select id,AccountNumber,Name,Type from Account where '+whereClause   +  ' LIMIT :LimitSize OFFSET :OffsetSize' ;   
      acctlist = Database.query(queryString); 
}

These are the errors I'm getting

SOQL SOSL Injection result path 4: Object: searchgrpnum in file: /classes/GroupSearch.cls

L 82: whereClause += 'Accountnumber like ' +SearchGrpNum+' and ';

Object: whereclause in file: /classes/GroupSearch.cls

L 106: whereClause = whereClause.removeEnd(' and ');

Object: whereclause in file: /classes/GroupSearch.cls

L 111: String querystring = 'select id,AccountNumber,Name,Type from Account where '+whereClause + ' LIMIT :LimitSize OFFSET :OffsetSize' ;

Object: query in file: /classes/GroupSearch.cls

L 112: acctlist = Database.query(query);

SOQL SOSL Injection result path 5: Similarity Id: -37089001 Object: searchgrpname in file: /classes/GroupSearch.cls

L 88: whereClause += 'Name like ' +SearchGrpName+' and ';

Object: whereclause in file: /classes/GroupSearch.cls

L 106: whereClause = whereClause.removeEnd(' and ');

Object: whereclause in file: /classes/GroupSearch.cls

L 111: String querystring = 'select id,AccountNumber,Name,Type from Account where '+whereClause + ' LIMIT :LimitSize OFFSET :OffsetSize' ;

Object: query in file: /classes/GroupSearch.cls

L 112: acctlist = Database.query(query);

I'm escaping the singlequotes everywhere but still getting the soql injections. Help Please

Answer 1

The problem is you are not using escaping everywhere. SearchGrpNum is not escaped at

whereClause += 'Accountnumber like ' +SearchGrpNum+' and ';

similarly, the where clause at

queryString = 'select id,AccountNumber,Name,Type from Account where '+whereClause   +  ' LIMIT :LimitSize OFFSET :OffsetSize' ;  

You must use escape single quote in each concatenation. Practically, this is not needed but, Checkmarx rules will consider it as injection threat.

Solution:- Instead of using String concatenation, use Apex variable reference. Below I am trying to give example:-

  if(!String.isEmpty(SearchGrpName))
      SearchGrpNum = '\'%' + GrpNumZ.trim() + '%\'';
      whereClause += 'Accountnumber like :SearchGrpNum and '; 
    }           
  }
  if(!String.isEmpty(SearchGrpName))
  {
     SearchGrpName = '\'' + SearchGrpName.trim()+'%\'';
     whereClause += 'Name like :SearchGrpName and ';
  }
  if(!String.isEmpty(SearchGrpYr) && sgrpnm != 'Y')
  {
     SearchGrpYr = '\'' + SearchGrpYr.trim()+'\'';
     whereclause += 'HBC_Grp_Yr__c =:SearchGrpYr' ;
  }

 whereClause = whereClause.removeEnd(' and ');
 if(!String.isEmpty(whereClause))
 {
      queryString = 'select id,AccountNumber,Name,Type from Account where '+String.escapeSingleQuotes(whereClause)   +  ' LIMIT :LimitSize OFFSET :OffsetSize' ;   
      acctlist = Database.query(queryString); 
 }

Note: You may need to tweak the solution a little bit as I didn't compile.