How to Create Dynamic Bound List Variable Names in a Loop for Dynamic SOQL

Question

I'm trying to create a dynamic SOQL string based on the type of fields being used. If the field is a text area, it contains a list of comma delimited strings. For each text area field, I create a list of the delimited values, and use "IN :TAValues" in the dynamic SOQL.

Problem is, if there is more than one text area field, the values in 'TAValues' get overwritten by the last field evaluated in the loop. I can't use a map or TAValues[i] in the dynamic query - how could this be done?

        public Map<String, String> CriteriaFieldNameToValue = new Map<String, String>();

        String QString = 'SELECT ID, Name FROM Account WHERE ';   
        
        Map<String, Schema.SObjectField> fieldMap = ((SObject)Type.forName('Schema','Account_Rule__c').newInstance()).getSObjectType().getDescribe().fields.getMap();           
                                                                      
        for (String FieldName: CriteriaFieldNameToValue.keySet()){  
            
            String FieldValue = CriteriaFieldNameToValue.get(FieldName);
            String FieldType = String.valueof(fieldMap.get(FieldName).getDescribe().getType());
           
            system.debug(FieldName +' = '+FieldValue);            
          
            if(FieldType == 'TEXTAREA'){
                String [] TAValues = FieldValue.split(',');
                QString = QString + FieldName +' IN :TAValues AND ';
                    system.debug('QString = '+QString);
                    system.debug('TAValues = '+TAValues);
            }            
        }    
        
        QString = QString.removeEndIgnoreCase(' AND ');
            system.debug('QString = '+QString);
        
        return Database.getQueryLocator(QString);   

Answer 1

UPDATE

With the introduction of dynamic bindings, via the Database.queryWithBinds method, it is possible to use bindings with "dynamically generated names" (though not with a query locator at the moment).

For your scenario, and assuming you've moved to simply executing the query (not return a query locator), you'd want to do something like:

...
String query = 'SELECT ID, Name FROM Account WHERE ';

Map<String, Object> bindingsByName = new Map<String, Object>();
List<String> conditions = new List<String>();

for (String fieldName : criteriaFieldNameToValue.keySet()) {
    String fieldValue = criteriaFieldNameToValue.get(fieldName);
    String fieldType = String.valueOf(fieldMap.get(fieldName).getDescribe().getType());
           
    if (fieldType == 'TEXTAREA') {
        String[] values = fieldValue.split(',');

        String condition = fieldName + ' IN :' + fieldName;
        bindingsByName.put(fieldName, values);

        conditions.add(condition);
    }
}

query += '(' + String.join(conditions, ') AND (') + ')';

...

List<Account> accounts = Database.queryWithBinds(query, bindingsByName, AccessLevel.SYSTEM_MODE);
...

What this does is generate different bindings (using the actual field name) for each of the text area fields coming from your configuration. Since the values are in bindings there is no need to escape these values.

NB: Choose SYSTEM_MODE or USER_MODE for the access level as makes sense in your scenario.

ORIGINAL ANSWER (still applicable for Query Locator cases)

In this scenario it is impossible to use bindings and you must instead "inline" the actual values, generating the query string so it looks like:

... Xyz IN ('value 1', 'value 2', ...) ...

The most important point here is that each value must be escaped to ensure no SOQL Injection vulnerability. Do that for each value using String.escapeSingleQuotes.

The generation can be performed in two steps:

...

List<String> escapedValues = new List<String>();

// escape the values to avoid SOQL Injection vulnerabilities
for (String value : values) {
  escapedValues.add(value.escapeSingleQuotes());
}

// generate the SOQL filtering term
String term = fieldName + ' IN (\'' + String.join(escapedValues, '\', \'') + '\')';

...

By the way, the above approach of using String.join is ideal for combining multiple clauses together with ') AND (' as the delimiter and an initial open and trailing close bracket outside the join. This avoids messing with removing trailing text.