How to safely run User-generated SOQL query strings

Question

Background Context:
I'm building a small feature similar to Andrew Fawcett's Declarative Lookup Rollup Summary tool. We will allow users to enter in details for their own WHERE clauses for the Aggregate queries, which are very likely to include string literals; a typical clause is expected to resemble:
WHERE Status__c = 'Complete' OR Status__c = 'In Progress'

SFDC security standards dictate that I should escape single quotes before running the query to mitigate risks of SOQL injection, but that of course causes the queries to explode around these String literals.

What I need
A way to safely run these queries without breaking the literal expressions

What I've tried
I was hoping to pull out all the literals into a collection, substituting them for :collection[index] expressions, but Dynamic SOQL does not support brackets or parenthesis in bind expressions. Here is what I currently have:

String[] criteriaValues = new List<String>();
String criteria = rule.Relationship_Criteria__c;//example: Status__c = 'Complete' OR Status__c = 'In Progress'
Matcher m = Pattern.compile( '\'(.+?)\'').matcher(criteria);
while(m.find()){//iterate through the Criteria string to carefully separate and encode String literals
    criteriaValues.add(  m.group()  );
    criteria = criteria.replace(m.group(), '{'+ (criteriaValues.size()-1) +'}');
}
/**  0 -> aggregate selections;  1 -> WHERE conditions;  2 -> GROUP BY  **/
String qry = 'SELECT {0} FROM '+objectType.getDescribe().getname()+ ' WHERE {1} GROUP BY {2}';
String[] fmtArgs = new List<String>{
    'COUNT(Id), '+rule.Relationship_Field__c,
    String.format(criteria, criteriaValues),
    rule.Relationship_Field__c
};
//Executed query string should read (or perform identically to) 
//'SELECT COUNT(ID), Related__c FROM Custom__c  WHERE Status__c = 'Complete' OR Status__c = 'In Progress' GROUP BY Related__c'
return (AggregateResult[])Database.query(String.escapeSingleQuotes(String.format(qry, fmtArgs)));

A sidenote maybe, but how are you hoping to 'safely' run user generated SOQL anyways? — Willem Mulder
– Willem Mulder, Commented Sep 16, 2015 at 18:14
Should users be able to enter multiple fields to filter on? Is it a coincidence that your examples all filter on a single field? — Adrian Larson
– Adrian Larson ♦, Commented Sep 16, 2015 at 19:13
Yes, that's coincidental for simplicity. Users may, in fact, filter on whatever they want. — RenegadeCoder
– RenegadeCoder, Commented Sep 16, 2015 at 19:29

Robert Sussland · Accepted Answer · 2015-09-18 04:44:58Z

The easiest option is to use the REST/SOAP API. The API enforces CRUD/FLS and sharing, so js clients can run their own SOQL safely and your Apex code isn't involved. The risk of SOQL injection is just the risk of permissions bypass, as Salesforce SOQL is not like SQL and cannot result in anything other than a query on SOQL objects.

But if you want to make the query in Apex, then you need to not only escapeSingleQuotes the string literals in where fields, but also do full CRUD/FLS checks.

Remember the client does not know the allowed sObjects, nor the fields belonging to those objects, nor the server's schema, yet somehow the client is supposed to be generating the queries. All of the key information lives on the server and has to be passed to the client by the server, then information is lost when the client does not pass enough structure back to the server to unambiguously parse the SOQL query.

That means that you can always design this so that the server never sends any real field names or object names to the client, and the client never sends any strings to the server (except for string literals to be used in WHERE clauses), but only tokens (references), and the client returns a combination of the tokens together with commands to combine them with string literals into queries. Then the server has enough information to properly generate the SOQL query the client wants and perform all necessary escaping and CRUD/FLS checks: server side, you automatically escapeSingleQuote any string literals, and interpret all the tokens and commands as map keys, putting the corresponding value into the query after doing an isAcccessible() check on the field value. You map the commands (e.g. parenthesis, AND, etc) to the corresponding lookup value string.

Now, no strings are being passed from the client to the server that go into a SOQL query unescaped and without any CRUD/FLS checks.

It is, however, a lot of work whereas using the REST API is easy.

Stack Exchange Network

How to safely run User-generated SOQL query strings

1 Answer 1

You must log in to answer this question.

Hot Network Questions