2

I am trying to write dynamic SOQL queries, but I don't know the exactly correct way how to escape the multiselect picklist with multiple categories included the way it is working and not vulnerable to soql injection.

Here is the querying of multiselect picklist described without the escaping.

How can I pass variables with some categories etc to customize the query dynamically to select one or other (separated by comma) in one query?

This always return nothing. But separating with ';' works, because there are some values which consists both categories... How it is supposed work?

String category = 'ProductCat1,ProductCat2';
String soql = 'select Id, Category from Case where Category__c includes (:category) ';
Database.query(soql); // doesn't return
2

I'm not 100% sure this will work and don't have a fast way to test but based on the documentation you linked I would expect something like this to work:

String category = 'ProductCat1,ProductCat2';
List<String> categories = category.split(',');
String soql = 'select Id, Category from Case where Category__c includes :categories'
Database.query(soql);

Give it a shot and it it doesn't work I'll delete this answer.

Update:

I was able to test original answer and it did not work.

Unfortunately I think this is the only way:

String category = 'Cat1,Cat2';
List<String> categories = category.split(',');
String soql = 'SELECT Id FROM Case WHERE Category__c Includes(';

for (String s : categories) {
    soql += '\'' + EncodingUtils.escapeSingleQuotes(s) + '\',';
}

soql = soql.left(soql.length() -1); // strip off extra comma

soql += ')';

system.debug(soql);

Ultimately:

Per the coumentation using ';' within a single string is treated as an AND operation. Ex. includes ('abc;def') for an OR operation you need to separate them into two strings Ex. includes 'abc','def')

3
  • This has always worked for me. One other recommendation would be to use bracket [] query syntax instead of Database.query. This will catch any syntax errors at compile-time, and the :categories syntax has better support, e.g. for nested objects (:myVar.categories) etc. – Nathanael Schmolze Feb 5 '20 at 16:11
  • @NathanaelSchmolze I tested and it did not work for me. First error was "missing left parentesis" and second error was "invalid bind type List<String>". The updated code does work however. – gNerb Feb 5 '20 at 16:13
  • @gNerb, Hmm, so the only option is to be escaping single quotes. I don't get, why just putting :category doesn't work. You get the variable from the frontend, so Salesforce could have expected, that we want to filter by that variable. Now I tried putting the string variable with categories into bracket query, and it doesn't work either! But if I put the same string directly between ( ) of include, escaped \' manually, it WORKS! So really, the only way, how to make a request is the way you described. Through Database.query() but with string you manually have to put into variable. Thank you. – Dalton Feb 6 '20 at 11:01

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.