3

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

Colorified, easier to read snippet

  • 1
    A sidenote maybe, but how are you hoping to 'safely' run user generated SOQL anyways? – Willem Mulder Sep 16 '15 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 Sep 16 '15 at 19:13
  • Yes, that's coincidental for simplicity. Users may, in fact, filter on whatever they want. – RenegadeCoder Sep 16 '15 at 19:29
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.