SfSkills Domains Docs GitHub

Home / apex / apex-dynamic-soql-binding-safety

apex-dynamic-soql-binding-safety

Safe construction of dynamic SOQL — Database.query bind variables (:varName, API 60+ semantics), Database.queryWithBinds(query, Map<String,Object>, AccessLevel) (API 55+), field-name allowlisting, ORDER BY direction whitelist, LIMIT/OFFSET typing, and WITH USER_MODE interaction (WITH SECURITY_ENFORCED was removed in API 67.0). NOT for reviewing a query for injection or CRUD/FLS generally — use apex/soql-security. NOT for Schema.describe and runtime type inspection — use apex/dynamic-apex.

Open the skill folder on GitHub

Apex Dynamic SOQL Binding Safety

Activate when ANY part of a SOQL query string is built at runtime from variables — filter values, field names, sort columns, limits, or the WHERE clause itself. Dynamic SOQL is necessary for reporting tools, configurable list views, and search; it is also the single most common SOQL-injection vector in Apex. This skill establishes the safe construction pattern for every value, identifier, and clause that flows into Database.query or Database.queryWithBinds.

Before Starting

Gather this context before working on anything in this domain:

Core Concepts

Why string concatenation is SOQL injection

Consider:

String name = ApexPages.currentPage().getParameters().get('name');
String soql = 'SELECT Id, Name FROM Account WHERE Name = \'' + name + '\'';
List<Account> accs = Database.query(soql);

If a user supplies name = ' OR Id != null --, the resulting query becomes SELECT Id, Name FROM Account WHERE Name = '' OR Id != null --'. Every Account in the org is returned, FLS-aware or not. The -- swallows the trailing quote so the parser is happy. Variants exist for sub-selects, UNION-style trickery via semi-joins, and ORDER BY exfiltration.

String.escapeSingleQuotes(input) defangs the quote-breakout vector but provides ZERO protection if the attacker controls a field name, ORDER BY column, or LIMIT integer represented as a string. It is necessary but not sufficient.

Bind variables in Database.query

When the query string contains a colon-prefixed token like :accountName, Database.query(soql) resolves it against the local scope of the call site. The platform parameterizes the value — the user input never participates in SOQL parsing. Caveats:

Database.queryWithBinds (API 55+) — the modern explicit-bind variant

Map<String, Object> binds = new Map<String, Object>{
    'searchName' => '%' + sanitizedTerm + '%',
    'minAmount'  => userMinAmount
};
List<Opportunity> opps = Database.queryWithBinds(
    'SELECT Id, Name, Amount FROM Opportunity ' +
    'WHERE Name LIKE :searchName AND Amount >= :minAmount',
    binds,
    AccessLevel.USER_MODE
);

Use queryWithBinds whenever a query is constructed across methods or service layers — bind values travel as a map, not a hidden lexical-scope contract. Always pair with AccessLevel.USER_MODE unless SYSTEM_MODE is documented.

Allowlisting field names, sObject names, and clauses

Bind variables cannot bind identifiers. For fields:

Map<String, Schema.SObjectField> fieldMap =
    Schema.SObjectType.Account.fields.getMap();
String requested = userInput.toLowerCase();
if (!fieldMap.containsKey(requested)) {
    throw new IllegalArgumentException('Unknown field: ' + userInput);
}
String fieldName = fieldMap.get(requested).getDescribe().getName();

Use the canonical name returned by getDescribe().getName() (not the raw user input) when concatenating into the query. Apply analogous patterns for sObject names (Schema.getGlobalDescribe()).

For ORDER BY direction, hard-code an allowlist:

String dir = ('DESC'.equalsIgnoreCase(userDir)) ? 'DESC' : 'ASC';

For LIMIT and OFFSET, parse to Integer first; never let a String reach the query. Apex Integer.valueOf throws on non-numeric input — that is the desired failure mode.

Dynamic SOQL with WITH USER_MODE

WITH USER_MODE (GA at API 57.0, Spring '23) is the access-mode clause to put inside a dynamic query string, and it is enforced at parse time on the field list. WITH SECURITY_ENFORCED is the older, weaker equivalent: it was removed in API 67.0 (Summer '26) and a class pinned at 67.0 or above fails to compile with WITH SECURITY_ENFORCED is no longer supported, use WITH USER_MODE instead. Because the clause lives inside a string here, a dynamic query hides that compile error until the string is parsed at run time — so it surfaces as a QueryException in production rather than a build failure. Never build WITH SECURITY_ENFORCED into a query string. Per-version detail: 5 § Apex security idiom by API version.

The access-mode clause protects what the query CAN ASK FOR; it does not protect HOW the query is built. A dynamic query with WITH USER_MODE is still injectable if you concatenate user input into the WHERE clause — WITH USER_MODE just stops the attacker from selecting fields they cannot see.

Prefer Database.queryWithBinds(..., AccessLevel.USER_MODE) over an in-string WITH USER_MODE; the AccessLevel argument does the same job and stays out of the parsed string.

Common Patterns

Pattern: queryWithBinds for user-supplied values

When to use: Any time the WHERE clause contains a value derived from user input.

How it works:

public List<Contact> searchContacts(String term, Integer maxRows) {
    Map<String, Object> binds = new Map<String, Object>{
        'term' => '%' + String.escapeSingleQuotes(term) + '%',
        'cap'  => Math.min(maxRows, 200)
    };
    return Database.queryWithBinds(
        'SELECT Id, Name FROM Contact WHERE Name LIKE :term LIMIT :cap',
        binds,
        AccessLevel.USER_MODE
    );
}

String.escapeSingleQuotes here is defense-in-depth; the bind itself prevents injection.

Why not the alternative: Inline concatenation of term into the query string is injectable. Database.query(soql) would also work, but queryWithBinds makes the bind contract explicit and survives refactoring.

Pattern: Field-name allowlist for dynamic field lists

When to use: Reporting tool, list view, or export where the user picks which columns appear.

How it works:

public List<SObject> runReport(String objectApi, List<String> requestedFields) {
    Schema.SObjectType sot = Schema.getGlobalDescribe().get(objectApi);
    if (sot == null) throw new IllegalArgumentException('Unknown object');
    Map<String, Schema.SObjectField> fmap = sot.getDescribe().fields.getMap();

    List<String> safeFields = new List<String>();
    for (String f : requestedFields) {
        Schema.SObjectField sf = fmap.get(f.toLowerCase());
        if (sf == null) throw new IllegalArgumentException('Unknown field: ' + f);
        safeFields.add(sf.getDescribe().getName());
    }

    String soql = 'SELECT ' + String.join(safeFields, ', ') +
                  ' FROM ' + sot.getDescribe().getName() +
                  ' WITH USER_MODE LIMIT 200';
    return Database.queryWithBinds(soql, new Map<String, Object>(), AccessLevel.USER_MODE);
}

Why not the alternative: Concatenating requestedFields directly lets an attacker inject Id), (SELECT Username FROM User or similar. The lookup-then-canonical-name pattern guarantees only real, current fields reach the parser.

Pattern: ORDER BY direction whitelist

When to use: Sortable list views or table headers backed by Apex.

How it works:

String safeField = canonicalFieldName(sortField);   // allowlist as above
String dir = 'DESC'.equalsIgnoreCase(sortDir) ? 'DESC' : 'ASC';
String soql = 'SELECT Id, Name FROM Account ORDER BY ' + safeField + ' ' + dir +
              ' LIMIT :pageSize';
Map<String, Object> binds = new Map<String, Object>{ 'pageSize' => pageSize };
return Database.queryWithBinds(soql, binds, AccessLevel.USER_MODE);

Why not the alternative: A user-supplied direction string can carry , (SELECT ...) or NULLS FIRST OFFSET 9999. Two-value choice removes the entire class.

Decision Guidance

SituationRecommended ApproachReason
User-supplied filter valueDatabase.queryWithBinds + bind mapParameterizes value; explicit AccessLevel
Single-method dynamic query, value onlyDatabase.query with :localVarSimplest; bind in lexical scope
User-supplied field or object nameSchema describe lookup + canonical nameBind variables cannot bind identifiers
User-supplied sort directionTwo-value ASC/DESC ternaryAllowlist of two; nothing else can sneak in
User-supplied LIMIT/OFFSETParse to Integer then bindType system rejects non-numeric input
Need FLS at runtime on dynamic queryAccessLevel.USER_MODE on queryWithBindsSingle-knob enforcement, cleaner than WITH USER_MODE in string
Background job that legitimately needs SYSTEM_MODEAccessLevel.SYSTEM_MODE + comment block explaining whyDocumented exception, reviewable
  1. Inventory every variable that flows into the query string. Mark each as VALUE or IDENTIFIER.
  2. Bind every VALUE via Database.queryWithBinds (preferred) or Database.query with :varName. Never concatenate a value.
  3. Allowlist every IDENTIFIER — field names via Schema.SObjectType.X.fields.getMap() (lowercase the input, use the canonical name from getDescribe().getName()); sObject names via Schema.getGlobalDescribe(); ORDER BY direction via two-value ternary; LIMIT/OFFSET via Integer parsing.
  4. Choose AccessLevel deliberately — default USER_MODE; document SYSTEM_MODE with a justification comment.
  5. Write a SOQL-injection negative test — feed ' OR Id != null -- and ; DELETE FROM Account style payloads; assert the call returns expected rows or throws QueryException, never extra rows.
  6. Run the skill checker — python3 skills/apex/apex-dynamic-soql-binding-safety/scripts/check_apex_dynamic_soql_binding_safety.py --manifest-dir path/to/classes.
  7. Code review pass — verify no Database.query('... ' + patterns escaped the audit, especially ones hidden behind helper methods.

Review Checklist

Run through these before marking work in this area complete:

Salesforce-Specific Gotchas

Non-obvious platform behaviors that cause real production problems:

  1. Database.query bind scope is lexical, not class-wide. :term resolves against the executing method's local variables, not a class field with the same name in some cases — and definitely not a variable in the caller. Refactoring a query into a helper method routinely produces Variable does not exist at runtime. Use Database.queryWithBinds to pass binds explicitly.
  2. Database.queryWithBinds requires Map<String, Object>. A Map<String, String> will compile but throw at runtime when the platform tries to bind a non-string value (Integer for LIMIT, List for IN). Always declare the map as Map<String, Object>.
  3. AccessLevel.SYSTEM_MODE silently bypasses FLS and CRUD. It is the right choice for some background jobs but is a security regression in user-facing controllers. Default to USER_MODE; require a code comment for any SYSTEM_MODE call site.

Output Artifacts

ArtifactDescription
queryWithBinds call siteThe canonical safe call: query string + bind map + AccessLevel
Field-name allowlist helperMethod that maps user input → canonical field name via Schema
ORDER BY direction whitelistTwo-value ternary collapsed to ASC/DESC
Injection negative testTest method asserting attack payloads return expected rows or throw

Package files