Dynamic SOQL in Loops: Security Review Impact for Managed Packages

If you build managed packages for the AppExchange, Security Review is not optional, and one worry comes up over and over: SOQL queries inside a loop. It surfaces during internal development and again right before the official scan. When the logic says the object or the fields have to be worked out at runtime, developers end up building the SOQL statement inside the iteration. So the question package vendors keep asking is whether unavoidable dynamic SOQL inside a loop will fail the Salesforce Security Review.

Understanding the security review focus: security versus best practices

Salesforce Security Review exists to protect the tenant's data and infrastructure from malicious or exploitative code. It looks hard at permission boundary violations, access control bypasses, injection vulnerabilities, and resource consumption heavy enough to threaten multi-tenant stability.

There is a real difference between a security vulnerability and a coding practice aimed at performance and scalability. SOQL statements inside a loop, often called the 'N+1' query problem, are the second kind.

The answer, based on established review criteria, is no. Unavoidable dynamic SOQL inside a loop will generally not fail the Security Review for a managed package, as long as it is structurally sound on security enforcement such as Field Level Security checks.

The reason is the tooling. Configured for AppExchange security scanning, with Code Analyzer or Checkmarx set to security-only checks, the scanner hunts genuine security flaws. Apex best practice around governor limits and efficiency sits outside its remit. SOQL in a loop is a governor limit issue, and it does not compromise tenant data isolation or integrity.

Key distinctions

A security failure is code that tries to bypass WITH SECURITY_ENFORCED, queries data the calling user should not see, or runs without checking CRUD and FLS permissions.

A performance and best practice problem is running too many SOQL queries, past the 100 limit, because of where the query sits. That throws a LimitException at runtime, which is an operational failure and not usually a security flag.

If your dynamic SOQL respects the security context, using UserMode.System only where it is genuinely necessary and handling it carefully, or running in SharingMode.ExcludesSharing while still respecting FLS, the scanner is less likely to flag it as a security risk.

The necessity of dynamic SOQL in object discovery scenarios

In package development, especially for highly configurable solutions, the exact object type, relationship name, or field list often cannot be hardcoded at compile time. That happens when the solution reads configuration from Custom Settings, Custom Metadata Types, or an external source that decides which objects to interact with; when you are writing framework-level code meant to handle any SObject passed to it dynamically; or when you are building dynamic reports whose schema comes from the user or an external integration.

If you are discovering child records under a parent record and the object name itself is variable, a loop starts to look necessary.

Here is the common, and often inefficient, pattern:

List<SObject> parents = [SELECT Id FROM Account WHERE Name LIKE 'Acme%'];

for (SObject parent : parents) {
    // The object type 'Contact' might need to be dynamic, e.g., based on metadata lookup
    String childObjectType = 'Contact'; 
    
    // Dynamic SOQL inside the loop
    List<SObject> children = Database.query('SELECT Id, Name FROM ' + childObjectType + ' WHERE ParentId = :parent.Id');
    
    System.debug('Found ' + children.size() + ' children for ' + parent.Id);
}

The segment is syntactically correct and it gets past the scanner's security check, because a string built from trusted metadata is not an injection risk. It still screams for a governor limit exception once parents gets large. The Security Review might note it, but it generally will not fail the security gate unless it reveals data outside the scope of the running user's permissions.

The preferred architectural pattern: dynamic subqueries

Dynamic SOQL in a loop is not an automatic Security Review failure, and architects are still expected to use patterns that take the performance risk out. When you need related records for a set of parents, the dynamic subquery is the expected, mature solution.

Instead of iterating over parents and firing a separate query for each one, fold the whole operation into a single, dynamically constructed SOQL statement with a subquery.

Step 1: Identify dynamic elements

Work out which parts of the query genuinely have to stay dynamic. Usually that is the object type or the selected fields. A fixed relationship name, like Contacts for Account, makes this easier. If the entire structure has to be dynamic, the query string needs more care.

Step 2: Constructing the dynamic outer query with a subquery

If the relationship name (Contacts) is known but the outer object and the field list are resolved at runtime, a subquery structure still works. In the example below the outer object resolves to Account and the relationship to Contacts, with the child objects determined by configuration.

// Configuration driven: we know the outer object, relationship, and needed child fields
String parentType = 'Account';
String relationshipName = 'Contacts'; // This MUST be the actual relationship name, NOT the object API name
List<String> childFields = new List<String>{'Id', 'Email'};

// Build the subquery string
String subqueryFields = String.join(childFields, ', ');
String dynamicSubquery = ' (SELECT ' + subqueryFields + ' FROM ' + relationshipName + ')';

// Build the main query string
List<String> parentFields = new List<String>{'Id', 'Name'};
parentFields.add(dynamicSubquery);

String finalQuery = 'SELECT ' + String.join(parentFields, ', ') + ' FROM ' + parentType + ' WHERE Industry = :configIndustry';

// Execute the single, optimized query
List<Account> results = Database.query(finalQuery);

// Now, access children safely
for (Account acc : results) {
    for (Contact child : acc.Contacts) { // Safe cast/access is required if using SObject accessor
        System.debug('Child: ' + child.Email);
    }
}

Database.query() still means a dynamic SOQL statement, which might trigger a warning in static analysis tools. The consolidated query runs once, outside any execution loop, and pulls all the related data in bulk, so it respects governor limits (assuming the total records retrieved respect the heap and row limits) and it reads as architectural maturity to the reviewer.

Handling truly dynamic relationships

What if the relationship name itself is determined dynamically from configuration? Building a true subquery structure gets significantly harder, and it can push you back toward the loop approach.

In these exceedingly rare, highly dynamic cases, if you must iterate, employ rigorous defensive coding to mitigate the governor limit risk, even though the Security Review ignores the limit issue itself:

1. Query the parent set in small batches, at most 50 parents, if the total set is large, and re-query in batches if necessary, though this complicates asynchronous processing.
2. Cache results heavily within the scope of the execution context.
3. Explicitly verify that every field selected in the dynamic query is visible and accessible to the running user, even in system context. Standard Database.query respects user context unless WITH USER_MODE or similar constructs are used improperly.

Security review tooling expectations

When security scanners run specifically against the AppExchange security rules, dynamic SOQL inside a loop is typically ignored, unless it presents a direct injection vector, which standard string concatenation of non-user-controlled metadata rarely does.

To verify this yourself before submission, run the Code Analyzer through the Salesforce CLI. Install the latest version, then run the command that focuses only on the security aspects mandated for AppExchange review:

sfdx force:code:analysis:run --target-dir force-app --project-dir . --format json --ruleset ApexCodeScanning --filter-rules security

If your code builds SOQL dynamically with Database.query(), the scanner will identify it as dynamic SOQL. It might flag it as an area for Apex best practice review over governor limits, but it should not trigger a hard failure under the dedicated security rule set, provided no data access bypasses are present.

Key takeaways

For developers and architects preparing a managed package for AppExchange submission, here is where dynamic SOQL inside a loop leaves you:

1. Dynamic SOQL in a loop is a governor limit risk, the N+1 query problem, and it is not a Salesforce security vulnerability that triggers an automatic Security Review failure.
2. Security scanners configured for AppExchange compliance look for security vulnerabilities such as injection or access control gaps, and they leave operational efficiency alone.
3. The ideal pattern for both performance and architectural scrutiny is a single, dynamically constructed SOQL query using relationship subqueries, fetching every required child record in bulk.
4. If the relationship structure is completely metadata-driven and rules out subqueries, the loop pattern is sometimes unavoidable. Keep the query safe from injection and make it respect CRUD and FLS permissions rigorously if it runs in a partial trust context.