SOQL Injection Defense: Bind Variables, queryWithBinds & User Mode Guide

💬 In plain words: Salesforce Object Query Language (SOQL) injection happens when you glue raw user input directly into a query string, allowing the user's text to change the logic of the query itself. Your strongest defense is using bind variables (:userInput), which force Salesforce to treat input strictly as data—never as executable code. For dynamic queries, your next best tools are Database.queryWithBinds, allow-listing field names, and using String.escapeSingleQuotes as a last resort.

📌 Quick Example: Imagine writing String query = 'SELECT Id FROM Account WHERE Name LIKE \'' + userInput + '\'';. If a user types xyz' OR Name != ' into the search box, your query rewrites itself to return every single Account in the database. The fix is simple: use WHERE Name LIKE :searchTerm. Because it is a bind variable, that malicious text is just searched literally and can never become part of the query structure.

🧠 Core Rule: Bind variables turn user input into passive data, never executable code. That single concept solves 90% of SOQL injection bugs.

🔑 Key Points & Latest Salesforce Security Updates

Before diving into the code, here are the most important takeaways and modern Salesforce platform updates you need to know:

• Static SOQL is Naturally Safe: Whenever you use square brackets [SELECT ... WHERE Field = :myVar], Salesforce automatically separates the query structure from the variable data.
• Modern Dynamic Binds (Database.queryWithBinds): You no longer need to rely on messy string concatenation or local scope tricks for dynamic queries. Introduced in recent Salesforce releases, Database.queryWithBinds lets you pass a Map<String, Object> directly into a dynamic query string safely.
• Enforce User Permissions (WITH USER_MODE): Always pair your queries with WITH USER_MODE or pass AccessLevel.USER_MODE into your Database.query() calls. Even if a developer makes a mistake, the query cannot leak records or fields the logged-in user doesn't already have permission to see.
• Escaping Quotes Has Major Blind Spots: String.escapeSingleQuotes() only protects values wrapped inside single quotes. It offers zero protection for dynamic ORDER BY fields, LIMIT numbers, or object names.

🛡️ Understanding the Concept: How SOQL Injection Works

Any time you build a dynamic SOQL query by concatenating (gluing together) strings that come from a user interface, a URL parameter, or an external web service, your code is vulnerable to injection.

An attacker doesn't need special hacking tools to exploit this. They simply type a single quote (') to prematurely close your intended filter, and then type their own SOQL commands—like OR Id != null—to trick the database into handing over sensitive data.

When I architect Salesforce solutions or review pull requests, I enforce defenses in this strict order of strength:

• 1. Static SOQL with Bind Variables (:var): The gold standard. Because the variable is compiled separately from the query syntax, the value can never alter the query logic.
• 2. Database.queryWithBinds: When your WHERE clauses must be built dynamically at runtime, this method lets you pass a bind map cleanly without concatenating values into the string.
• 3. Strict Allow-Listing (Whitelisting): Bind variables cannot replace object names, field names, ORDER BY columns, or LIMIT clauses. You must validate these tokens against a hardcoded Set<String> or the Schema describe map.
• 4. String.escapeSingleQuotes(): Use this only for unavoidable legacy string splicing. It adds a backslash (\) before any single quote so the user cannot break out of a text literal.
• 5. Defense in Depth (WITH USER_MODE and Security.stripInaccessible): Layer user-mode security on top of every query. This ensures that Field-Level Security (FLS), Object permissions (CRUD), and sharing rules act as a permanent safety net.

🛠️ Real-World Story: How I Fixed a Vulnerable Partner Portal Search

💻 The Project Problem: A few years ago, I inherited a custom Lightning Web Component (LWC) search tool built for a Partner Community. Partners could search for Invoices by typing a keyword and selecting a column to sort by. During an internal security check before going live, I noticed the previous developer had written an @AuraEnabled Apex controller running inside a without sharing class to bypass complex partner visibility rules.

Here is what the vulnerable code looked like:

// ❌ VULNERABLE CODE - DO NOT USE
String query = 'SELECT Id, Name, Total_Amount__c, Tax_ID__c FROM Invoice__c ' +
               'WHERE Name LIKE \'%' + searchKey + '%\' ' +
               'ORDER BY ' + sortField + ' ASC';
return Database.query(query);

🔍 How I Tested the Exploit: In the search box, I typed test%' OR Total_Amount__c > 0 OR Name LIKE '%. Because the class ran without sharing, the portal immediately returned every single invoice across all partner companies in the entire org! Worse, by passing Tax_ID__c into the sortField parameter from the browser console, a user could infer sensitive financial ordering even on fields hidden from the UI.

✅ How I Solved It: I refactored the method using a three-step modern approach:

• First, I moved the wildcard % symbols into a clean Apex variable and put it inside a bind Map<String, Object>.
• Second, I built a strict server-side Set<String> allow-list for the sortField so users could only sort by Name or CreatedDate.
• Third, I replaced Database.query() with Database.queryWithBinds() and enforced AccessLevel.USER_MODE.

// ✅ SECURE PRODUCTION FIX
Set<String> allowedSortFields = new Set<String>{'Name', 'CreatedDate', 'Total_Amount__c'};
String safeSort = allowedSortFields.contains(sortField) ? sortField : 'CreatedDate';

Map<String, Object> bindMap = new Map<String, Object>{
    'likeTerm' => '%' + searchKey + '%'
};

String secureQuery = 'SELECT Id, Name, Total_Amount__c FROM Invoice__c ' +
                     'WHERE Name LIKE :likeTerm ' +
                     'ORDER BY ' + safeSort + ' ASC';

return Database.queryWithBinds(secureQuery, bindMap, AccessLevel.USER_MODE);

That 10-line rewrite completely closed the vulnerability, respected partner sharing automatically, and passed our security audit on the first try.

⚠️ Common Developer Trap: Never try to put quotes or wildcard symbols around a bind variable inside the query string like this: WHERE Name LIKE '%:searchTerm%'. Salesforce will treat :searchTerm as literal text instead of a variable! Always attach the % wildcards to the String variable in Apex before binding it into the query.

🧭 360 Card — SOQL Injection Defense

• Rule: Always use bind variables (:var). User input becomes passive data, never executable code.
• Gain: A single change eliminates value-based injection, while making your code cleaner, faster, and easier to read.
• Price: Bind variables cannot substitute for field names, object names, or ORDER BY clauses. Those still require manual validation.
• Limits: String.escapeSingleQuotes() only protects text inside single quotes. Field and object names aren't quoted literals, so you must validate them against a Schema describe allow-list. Always add WITH USER_MODE so any potential bug is strictly contained by the user's own permissions.
• Mirror — Concatenation with Escaping: It looks safe on the surface, but a single unquoted integer, boolean, or field identifier reopens the security hole immediately.
• Later Connection: This exact same "input is not code" mindset governs Dynamic Apex instantiation and Formula/CSV Injection protection.
• At Volume: This is a security issue, not a governor limit issue. It doesn't matter if your table has 10 rows or 10 million rows—one vulnerable query compromises the whole org.

💬 Core Interview & Architecture Q&A

Q: Review this pattern: String q = 'SELECT Id FROM Account WHERE Name LIKE \'' + searchTerm + '%\'';. How would you attack it, and how do you fix it?

🎯 Say this first: "This is textbook SOQL injection where raw user input is glued straight into a query string. I would fix it immediately by switching to a bind variable (WHERE Name LIKE :likeTerm) and running the query in USER_MODE."

A: Here is how an attacker breaks this code, followed by the exact steps to lock it down:

• The Attack: If a user passes x' OR Name != ' as the searchTerm, the resulting query becomes SELECT Id FROM Account WHERE Name LIKE 'x' OR Name != '%'. This bypasses the intended filter and dumps every Account record the running context can see.
• Advanced Payloads: Attackers can also add conditions on sensitive fields (like OR AnnualRevenue > 10000000) to guess private data using boolean true/false responses on the screen.
• Fix #1 (Best - Static SOQL): Convert the dynamic query into static SOQL using a bind variable: WHERE Name LIKE :likeTerm. Build the wildcard string in Apex first (String likeTerm = searchTerm + '%';) so you bind the entire value cleanly.
• Fix #2 (Dynamic - queryWithBinds): If the query structure genuinely needs to be built dynamically, keep the :likeTerm placeholder in the string and pass the value inside a Map<String, Object> using Database.queryWithBinds(q, bindMap, AccessLevel.USER_MODE).
• Fix #3 (Legacy Fallback): Use String.escapeSingleQuotes(searchTerm) only if you are maintaining ancient code where binds cannot be used.
• Systemic Protection: Always append WITH USER_MODE so that Field-Level Security (FLS) and sharing rules are strictly enforced by the platform.

// 1. Build the search text with wildcards as a normal String variable first.
String likeTerm = '%' + searchTerm + '%';

// 2. The colon (:) makes it a BIND variable. 
// Salesforce treats it strictly as data, never as executable SOQL syntax.
List<Account> accs = [
    SELECT Id, Name 
    FROM Account
    WHERE Name LIKE :likeTerm
    WITH USER_MODE
];

🔍 Deep-Dive Scenario Follow-Ups

Q1: Our UI lets users pick the column to sort by (ORDER BY) and the object to search. Why doesn't String.escapeSingleQuotes() protect us here, and what does?

A1: String.escapeSingleQuotes() only escapes single quote marks (') inside text literals. Object names and field names in SOQL are identifiers, not quoted strings!

• Why Escaping Fails: When you write 'ORDER BY ' + userSortField, there are no single quotes around the field name. An attacker can pass a payload that contains zero quotes—for example, injecting extra field names, boolean logic, or unauthorized sorting columns like Salary__c DESC—and escapeSingleQuotes will let it pass right through untouched.
• The Allow-List Defense: You must validate the incoming string against a strict allow-list (whitelist) of approved values before putting it near your query.
• Dynamic Schema Validation: Alternatively, check the input against Schema.getGlobalDescribe() and SObjectType.getDescribe().fields.getMap(). Even better, verify fieldDescribe.isAccessible() at the same time so your validation doubles as an FLS check.
• Server-Side Token Mapping: My favorite pattern in production is never accepting API names from the browser at all. Have the LWC send a simple word like "revenue", and map that word to "AnnualRevenue" inside an Apex Map<String, String> on the server.

Q2: Your team is preparing for a formal Salesforce AppExchange Security Review (or an enterprise security audit). What is your step-by-step methodology to certify the org against SOQL injection?

A2: Don't just tell auditors "we checked the code." Give them a repeatable five-part engineering methodology:

• 1. Inventory the Attack Surface: Search the entire codebase for every instance of Database.query(, Database.countQuery(, Database.getQueryLocator(, and dynamic SOSL (Search.query(). Static queries inside [ ] can be marked safe immediately.
• 2. Triage into Three Remediation Buckets: Group every dynamic query into:
  • Bucket A: Can be converted directly to Static SOQL with :binds.
  • Bucket B: Needs dynamic conditions, so refactor to Database.queryWithBinds().
  • Bucket C: Uses dynamic field/object identifiers, which requires adding a Set<String> or Schema allow-list check.
• 3. Trace All Entry Points: Trace every parameter coming into @AuraEnabled, @RestResource, Visualforce controllers, and InvocableMethod classes to ensure untrusted data never touches a query string unvalidated.
• 4. Automate via CI/CD Scanners: Add Salesforce Code Analyzer (sf scanner run) with PMD's ApexSOQLInjection rule directly into your GitHub Actions or GitLab CI pipeline. Configure the pipeline to block any pull request that introduces unescaped string concatenation inside a query.
• 5. Enforce USER_MODE Globally: Upgrade dynamic query calls to include AccessLevel.USER_MODE. Frame your audit summary clearly: "We didn't just patch individual bugs—we updated our architecture and CI pipeline so this vulnerability pattern cannot be merged again."

Q3: What if I am concatenating an Integer or Boolean into a dynamic LIMIT or WHERE clause? Can that still be injected?

A3: It depends entirely on the data type in your Apex method signature! This is a sneaky edge case that catches many senior developers off guard.

• If your method accepts a String maxRows from the UI and concatenates 'LIMIT ' + maxRows, an attacker can pass 10 OFFSET 50 or attach malicious syntax because there are no quotes around a number. Remember: String.escapeSingleQuotes() does nothing here!
• The Fix: Either accept the parameter strictly as an Integer or Boolean in your Apex method signature, or cast it explicitly using Integer.valueOf(userInput) before concatenating. If an attacker passes text into Integer.valueOf(), Salesforce immediately throws a TypeException and stops the query dead in its tracks. Even simpler: you can bind integers directly using LIMIT :maxRowsInt!

Q4: How does Database.queryWithBinds() solve the old "variable not in scope" headache we used to have with Database.query()?

A4: In older versions of Apex, Database.query('SELECT Id FROM Account WHERE Name = :myVar') only worked if myVar was a simple local variable currently in scope in that exact method. Furthermore, you couldn't reference dot-notation like :myFilterWrapper.accountName inside a dynamic query string without getting a runtime error.

• This limitation forced developers to build complex query helper classes using dangerous string concatenation.
• With Database.queryWithBinds(queryString, bindVariablesMap, accessLevel), the query engine looks up variable names directly from the keys of your Map<String, Object> instead of relying on local execution scope.
• You can build your query string and populate your Map across completely different helper methods or classes, pass the map to your selector layer, and execute the query with 100% bind-variable security.

🔗 Connecting the Dots: Follow-Up Question for Your Next Step

🤔 Think About This Next: Now that you've locked down your SOQL queries with bind variables and AccessLevel.USER_MODE, what happens when a legitimate user runs a query that lacks permission on a single field? Hint: USER_MODE throws a hard System.QueryException! How do you combine WITH USER_MODE and Security.stripInaccessible() so your LWC degrades gracefully instead of crashing when a user lacks field access?

📝 2-Minute Self-Check (Architecture & Data Mastery)

Q1: A developer writes String q = 'SELECT Id FROM Contact WHERE Age__c > ' + String.escapeSingleQuotes(userAgeInput);. Is this query safe from SOQL injection?

A1: No! Because Age__c is a numeric comparison without surrounding single quotes in the query string, escapeSingleQuotes provides zero protection. A user can pass 0 OR AssistantName != null without using a single quote. Fix it with a bind variable (:userAge) or Integer.valueOf().

---

Q2: A data integration load creates duplicate records every single time a failed batch is retried. What is missing from the design?

A2: An External ID field paired with an upsert operation. Using a standard insert creates brand-new records on every retry, whereas upsert against an External ID makes the integration idempotent and retry-safe.

---

Q3: It is cutover weekend for a major deployment and data validation fails at 2:00 a.m. What decides whether you roll back or push forward?

A3: The Go / No-Go rollback criteria you documented and got signed off by stakeholders before the weekend started. Having pre-agreed thresholds turns an emotional 2:00 a.m. panic into a calm, objective checklist decision.