Dynamic Query / escapeSingleQuotes And "no viable alternative at character '\'"

Question

Read all the articles, tried most everything but cannot find a solution other than faith that users will not enter malicious data but:

I am trying to create a user entered query filter but when I use string.escapeSingleQuotes() as suggested it all breaks with:

no viable alternative at character '\'

Yet if I use the debug of the constructed query, put it in execute anon and wrap it in ' then the query works....

So how then do I take a user entered where clause and append it to an dynamic query string and perform the query safely.

Reproducible example

Class

public with sharing class Example_Controller {


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

    public void doQuery(){
        results = null;

        String tmp = whereClause == null ? 'Where Id != null' : (' Where ' + string.escapeSingleQuotes(whereClause));
        integer d;

        try{
            d = database.countQuery('Select Count() From Contact ' + tmp);
            results = 'Found ' + d + 'Contact(s)';
        }catch(Exception e){
            results = e.getMessage();
        }
    }

}

VF Page

<apex:page id="Example_Page" controller="Example_Controller">

    <apex:form id="tmp">
        <apex:inputText value="{!whereClause}"/>
        <apex:commandButton action="{!doQuery}" value="Click Me"/>
        <apex:outPutPanel layout="block" rendered="{!!isBlank(results)}">
            {!results}
        </apex:outPutPanel>
    </apex:form>

</apex:page>

Using the user input:

Name Like 'Liz%'

I get the following on the screen

Now, if I remove the string.escapSingleQuotes() it works fine but it is vulnerable:

String tmp = whereClause == null ? 'Where Id != null' : (' Where ' + whereClause); //string.escapeSingleQuotes(whereClause));

How do I construct the dynamic query string to safely perform this count query as well as regular SOQL queries......

The GOAL here is so an advance user can type in a query where clause that could be anything (validated prior to execution) so that a specific query can be filtered according to their needs. It needs to be generic and simple....

Initial Result

based on comments below I contracted this hacked together quickly test and it seems to work....Any comments on making it better?

public static String parseWhereClause(String whereClause){
    if(whereClause == null) return '';


    String[] tmp = whereClause.replace('\\','').split('\'');

    String result = tmp[0];
    if(tmp.size() > 1){
        for(integer x=1;x<tmp.size();x++) {
            result += '\'' + string.escapeSingleQuotes(tmp[x]) + '\'';

            if(tmp.size() > x+1){
                x++;
                result += tmp[x];

            }
        }
    }

    return result;
}

The above at least throws an error when trying to use an injection query string:

%' or Performance_rating__c<2 or name like '%

And work fine for both

Name Like 'Liz%'
Name Like \'Liz%\'

Although like sfdcfox says allowing the string to be typed in essentially is in itself an SOQL injection. But in this case I expect the user to specify the records returned and the class respects sharing...

Answer 1

Allowing total control of the where clause is, by definition, an injection vulnerability. It is for this reason that most forms expect users to enter either just a value, where the fields are in control of the developer, or provide a dropdown of fields they select from. Either way, if you're allowing the user to just put whatever they want for the WHERE clause, they can just type in something like IsDeleted = FALSE and get all the records in the database (assuming, of course, they don't hit governor limits first). The String.escapeSingleQuotes function is designed to escape just a value, not an entire field-operator-value tuple.

Answer 2

This is expected behavior. If you look at the output query string, you'll see:

SELECT count() FROM Contact WHERE Name LIKE \'Liz %\'

Of course you cannot run this query. I'm not sure how much you need to worry about injection vulnerability for a count query, but if you absolutely must prevent injection in this case, you likely have to specify field, operator, and value all separately.