[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;
try
{
    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 Solutions

[SalesForce] SOQL Injection Solutions Help – (Used esacapeSingleQuotes(str) – gives error = no viable alternative at character ‘\’ )

The salesforce security scanner should of give you some potential mitigation information such as:

To prevent a SOQL injection attack, avoid using dynamic SOQL queries. Instead, use static queries and binding variables. The Mitigations vulnerable example above could be re-written using static SOQL as follows:
public class SOQLController { public String name {
    get { return name;}
    set { name = value;} }
    public PageReference query() {
        String queryName = '%' + name + '%'
        queryResult = [SELECT Id FROM Contact WHERE (IsDeleted = false and Name like :queryName)]; return null;
    }
}
If you must use dynamic SOQL, use the escapeSingleQuotes method to sanitize user-supplied input. This method adds the escape character () to all single quotation marks in a string that is passed in from a user. The method ensures that all single quotation marks are treated as enclosing strings, instead of database commands

You can use the escapeSingleQuotes method in the following way:

Database.Query(String.escapeSingleQuotes(squery));

More information on escapeSingleQuotes can be found here

[SalesForce] How to avoid SOQL/SOSL Injection security review issue

Here is an example. You will have to pretty it up but it shows how entering:

Name = 'test'

into the where clause causes errors with the escaped query but not the unescaped

Controller

public class SOQLInjectionTest{


    public string whereClause{
        get{
            return whereClause;
        }
        set;
    }

    public Account[] accts {get;set;}

    public string queryStringBasic{

       get{
           return 'Select ID From Account Where ' + whereClause;
       }
       set;

    }

    public string queryStringEscaped{

       get{
           return 'Select ID From Account Where ' + string.escapeSingleQuotes(whereClause);
       }
       set;

   }

   public void basicQuery(){
       accts = database.query(queryStringBasic); 

   }

   public void escapedQuery(){
       accts = database.query(queryStringEscaped); 
   }


}

VF Page (Needs prettied up but you get the point)

<apex:page controller="SOQLInjectionTest">


<apex:pageMessages id="msgs"/>


<apex:form >
    Enter Where Clause <apex:inputText value="{!whereClause}"/>
    <apex:commandButton value="No Escape Query" action="{!basicQuery}" rerender="msgs,opt_pnl"/>
    <apex:commandButton value="Escape Query" action="{!escapedQuery}" rerender="msgs,opt_pnl"/>


    <apex:outPutpanel id="opt_pnl" layout="block">
        Account: {!accts}
        Where Clause: {!whereClause}
    </apex:outPutPanel>
</apex:form>



</apex:page>

So given this how are we supposed to prevent injection. Or is this use case just a great example of what we should NOT be doing?

The examples provided here https://developer.salesforce.com/docs/atlas.en-us.pages.meta/pages/pages_security_tips_soql_injection.htm as a way to test injection results with injection: `test%') OR (Name LIKE '.

So does it depend on how the query is structured? It would seem that there is not a need to escape when you are constructing an entire where clause....but then you are leaving the query wide open anyway.....hmmmmm

So I guess you are left with trying to justify it and ensure you are only returning records the user has access to. `

Best Answer

Related Solutions

[SalesForce] SOQL Injection Solutions Help – (Used esacapeSingleQuotes(str) – gives error = no viable alternative at character ‘\’ )

[SalesForce] How to avoid SOQL/SOSL Injection security review issue

Related Topic