Dynamic SOQL Done Safely

The finding came back on a Friday, which is how these things always seem to land. A managed package I'd inherited had failed the AppExchange security review, and the top item on the report was a SOQL injection point in a class a contractor named Daniel had written three years earlier. The class was called DynamicQueryBuilder, it was a little over four hundred lines long, and roughly a third of the package called into it. Nobody left on the team knew exactly how it worked.

That's the thing about dynamic SOQL. It's genuinely useful — sometimes there's no other way to build the query you need. But it's also the single easiest way to hand an attacker a read of every record the running user can touch, and the mistake looks almost identical to the correct code. I've cleaned this up in enough orgs now that I have a fixed set of rules I apply, and I want to walk through them.

Where Dynamic SOQL Earns Its Keep

Most of the time you don't need dynamic SOQL, and reaching for it out of habit is its own problem. Static queries are compiled, checked at save time, and can't be injected by construction. If you can express the query as a static one, do that and move on — I've written separately about SOQL query patterns that won't kill your governor limits, and almost none of them need a query string.

Dynamic SOQL earns its place in a narrow set of cases. Building a query where the selected fields aren't known until runtime — a configurable list view, a generic export tool. Filtering on a field the user picks from a dropdown. Constructing a WHERE clause from a search form with an unknown number of criteria. In those situations you are assembling a string, and the moment you assemble a string from anything a user supplied, you've opened the door.

Bind Variables Do Most Of The Work

The single most important rule is this: never concatenate a value into a query. Bind it. In dynamic SOQL, a colon-prefixed reference in the query string binds to an Apex variable, and the bound value is never parsed as SOQL. It goes in as data, not as query syntax. Injection through a bound value is not difficult to prevent — it's structurally impossible.

Here's the pattern people get wrong, the same shape that failed my review:

// Do not do this
String searchTerm = ApexPages.currentPage().getParameters().get('name');
String query = 'SELECT Id, Name FROM Account WHERE Name = \'' + searchTerm + '\'';
List<Account> results = Database.query(query);

If searchTerm comes in as x' OR Name != ', the WHERE clause evaluates to something that's true for every row, and you've just returned the entire Account table to whoever filled in the search box. Now here's the same query done properly:

String searchTerm = ApexPages.currentPage().getParameters().get('name');
String query = 'SELECT Id, Name FROM Account WHERE Name = :searchTerm';
List<Account> results = Database.query(query);

The :searchTerm reference binds the Apex variable in scope. It doesn't matter what the user types — quotes, SOQL keywords, a whole subquery — it's treated as a literal value to match against Name. The difference between the dangerous version and the safe one is a single character.

I've moved to the explicit form on new work, because relying on a variable happening to be in scope with the right name is fragile. Database.queryWithBinds takes a map, so the binding is spelled out:

Map<String, Object> binds = new Map<String, Object>{
    'searchTerm' => searchTerm
};
String query = 'SELECT Id, Name FROM Account WHERE Name = :searchTerm';
List<Account> results = Database.queryWithBinds(
    query, binds, AccessLevel.USER_MODE
);

The map makes the intent obvious to the next person reading it, and the USER_MODE argument does something the older calls didn't, which I'll come to below.

escapeSingleQuotes Is Not A Security Strategy

This is where I part ways with a lot of the advice floating around. String.escapeSingleQuotes() gets treated as the answer to SOQL injection, and it isn't. It's a narrow tool that escapes single quote characters so a value can't break out of a string literal. That's all it does. It does nothing for anything that isn't inside a quoted literal.

Consider a numeric filter:

// escapeSingleQuotes buys you nothing here
String minRevenue = ApexPages.currentPage().getParameters().get('min');
String query = 'SELECT Id FROM Account WHERE AnnualRevenue > '
    + String.escapeSingleQuotes(minRevenue);

There are no single quotes in that clause, so the escaping is a no-op. If minRevenue arrives as 0 OR Name != null, you leak every account, and the call to escapeSingleQuotes sits right there in the code looking like protection. I've watched a reviewer wave a class through because it "used escapeSingleQuotes everywhere," while the real injection was in an unquoted integer comparison two lines down.

My rule: escapeSingleQuotes is a last resort for the rare case where a value genuinely can't be bound and you've already validated its shape another way. It is not a substitute for a bind variable, and seeing it in a diff is a signal to ask why the value isn't bound instead.

You Cannot Bind A Field Name

Bind variables cover values. They do nothing for identifiers — object names, field names, the field in an ORDER BY, a sort direction. You cannot write ORDER BY :fieldName; SOQL won't accept a bind in that position. This is exactly where the interesting injection points live, because the developer reached for concatenation and there was no bind option to fall back on.

The only safe approach for an identifier is an allowlist. Validate the incoming name against a known, closed set before it goes anywhere near the string:

private static final Set<String> SORTABLE_FIELDS = new Set<String>{
    'Name', 'CreatedDate', 'AnnualRevenue', 'Industry'
};

public static List<Account> search(String sortField) {
    if (!SORTABLE_FIELDS.contains(sortField)) {
        throw new AuraHandledException('Unsupported sort field: ' + sortField);
    }
    String query = 'SELECT Id, Name FROM Account '
        + 'ORDER BY ' + sortField + ' LIMIT 200';
    return Database.query(query, AccessLevel.USER_MODE);
}

If you need the allowlist to be dynamic rather than hardcoded, build it from a describe rather than trusting the input:

Map<String, Schema.SObjectField> fieldMap =
    Account.SObjectType.getDescribe().fields.getMap();
if (!fieldMap.containsKey(sortField.toLowerCase())) {
    throw new AuraHandledException('Unknown field: ' + sortField);
}

Either way the principle holds: the field name that ends up in the string is one you chose, not one the caller supplied. Concatenation is fine when both sides of the join are things you control.

Run Dynamic Queries In User Mode

Injection is one half of the problem. The other half is that dynamic SOQL, like all Apex, runs in system context by default and happily returns fields and objects the running user has no permission to see. A perfectly injection-safe query can still leak data across a field-level security boundary.

That's what AccessLevel.USER_MODE on Database.query and Database.queryWithBinds is for. It enforces the running user's object permissions, field-level security, and sharing rules on the query itself. If a field in the SELECT is invisible to the user, the query throws rather than quietly returning it. I default to USER_MODE on every dynamic query now unless there's a documented reason the code has to run elevated. This is the same discipline I go into in more depth in CRUD/FLS enforcement in Apex — dynamic SOQL is simply where those checks are easiest to forget, because there's no compiler reminding you which fields you touched.

The older WITH SECURITY_ENFORCED clause does something similar inside the query string, but it's all-or-nothing and only covers fields in the SELECT and WHERE. USER_MODE is the cleaner tool, and it's where I'd start on anything new.

When Dynamic SOQL Isn't Worth It

Back to Daniel's DynamicQueryBuilder. The automated scan flagged 23 potential injection points across the package. Nineteen turned out to be false positives — bound variables the scanner couldn't trace through the builder's layers of string handling. Four were real, and three of those were a quick fix with bind variables and an allowlist for a sortable-field parameter coming out of a Lightning component.

The fourth was the builder itself, and I'll be honest about how that went. Rewriting it properly would have meant unpicking every caller, and the resubmission deadline was the following Thursday. So we wrapped the entry points, forced USER_MODE on the query it produced, added an allowlist check on the field arguments, and shipped. It passed. The class is still there, still four hundred lines, still on the technical debt list eighteen months later. It works, so we stopped asking.

The lesson I took from it wasn't about a clever fix. It was that a generic query builder is a liability that compounds — every feature that leans on it inherits its assumptions, and by the time someone reviews it, unwinding it is a project. If a static query or a small, purpose-built method will do the job, that's almost always the better call, even if it means a bit more code. I now run the Salesforce Code Analyzer as a gate in the pipeline, part of the same CI/CD setup I've written about, so a concatenated query fails the build long before it reaches a human. If you want a second pair of eyes on a query builder before it gets that entrenched, that's the kind of review I do.

None of this is exotic. Bind your values, allowlist your identifiers, run in user mode, and prefer static SOQL whenever the shape of the query allows it. The dangerous query and the safe one differ by a single character — which is exactly why this keeps happening, and exactly why the discipline is worth the ten minutes it costs.