6

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 screenenter image description here

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));

enter image description here

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...

0

2 Answers 2

6

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.

3
  • Thanks for that explanation on why it does not work like I am using it. So basically since both answers say it cannot be done I am going to have to go with a "Select field, operator, enter criteria" type model where they will have to build it out.....Sucks
    – Eric
    Dec 15, 2016 at 20:39
  • @Eric Unless you're willing to write a full SOQL validator, or perhaps some sort of simplistic formula language evaluator, I'm afraid that's your answer. It's not impossible, but it is very hard to do correctly.
    – sfdcfox
    Dec 15, 2016 at 20:40
  • Can you look at my hacked together solution in my question? it works with both ' and \' characters in the string....Curious if you can see any major flaws / improvements without putting in much effort.
    – Eric
    Dec 15, 2016 at 20:52
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.

5
  • Yea, that was what I found in the part about copying the output and wrapping it in ' working fine
    – Eric
    Dec 15, 2016 at 20:34
  • Count query was easiest but will be doing SOQL as well. No idea what fields I would even need. The desired result is the advanced user can type in the Where clause and be able to use it. I really need to have a way to do that and be OK on the security gods side
    – Eric
    Dec 15, 2016 at 20:34
  • Same comment as I made to sfdcfox. Thank you gentlemen
    – Eric
    Dec 15, 2016 at 20:54
  • @Eric Your updated solution isn't going to work that well, especially if you have any escaped backslashes. I think you must use regular expressions. I spent some time on it, but they are going to be pretty complex if you want to validate an entire WHERE clause, especially factoring in AND/OR logic.
    – Adrian Larson
    Dec 16, 2016 at 1:25
  • thanks Adrian. Looks like I need to change my expectations around this
    – Eric
    Dec 16, 2016 at 1:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .