[SalesForce] no viable alternative at character ‘\’ dynamic soql issue

I have below method which gets called from page by supplying the search keywords:

public PageReference runIntermediariesSearch() {

    soql = intermediariesSoql + ' where Name != null ';

    if (string.isNotBlank(dbAssetsLowerBound))
        soql += ' and Total_DB_Assets__c >=\''+String.escapeSingleQuotes(dbAssetsLowerBound)+'\'';
    if (string.isNotBlank(dbAssetsUpperBound))
        soql += ' and Total_DB_Assets__c <=\''+String.escapeSingleQuotes(dbAssetsUpperBound)+'\'';
      rest of the code   
    string finalQuery = String.escapeSingleQuotes(soql);
    intermediaries = Database.query(finalQuery); // intermediaries is nothing but list of the object
    return null;

when i run this i am getting the below error:

System.QueryException: line 1:318 no viable alternative at character '\'.

when i tried to debug the query :

select Id, Name, Assets_under_Advisement__c, Total_AUM__c, Total_DB_Assets__c , Total_DC_Assets__c, Total_E_F_Assets__c,
  Total_PS_Assets__c, Hedge_Mgr__c, Fund_of_Funds_Mgr__c, Instit_Mgr__c ,
  Private_Eq_Mgr__c, RE_Mgr__c, Financial_Adv__c, Country__c 
from intermediary__c 
where Total_DB_Assets__c >=\'10\' and Total_DB_Assets__c <=\'20\' 

I tried to fix it but no luck. Can any one help me.

Best Answer

You need to query against numbers, not strings. Your final WHERE clause should look like:

WHERE Total_DB_Assets__c >= 10
AND Total_DB_Assets__c <= 20

So your String manipulation should look like:

Integer min;
Integer max;
    if (String.isNotBlank(dbAssetsLowerBound))
        min = Integer.valueOf(dbAssetsLowerBound);
    if (String.isNotBlank(dbAssetsUpperBound))
        max = Integer.valueOf(dbAssetsUpperBound);
catch (TypeException ex)
    return new List<SObject>(); // try not to pass around null collections

if (min != null)
    whereClauses.add('Total_DB_Assets__c  >= ' + min);
if (max != null)
    whereClauses.add('Total_DB_Assets__c <= ' + max);

Note that you no longer really need String.escapeSingleQuotes because if any are contained in your inputs they will throw a TypeException. It may affect your security scanner results not to use it though...here I favored brevity.

Related Topic