💃 Dynamic SOQL 🕺

Good morning, Salesforce Nerds!

Let’s face it - static SOQL is dependable, but sometimes it’s just not enough. 🤏 

It’s like ordering a plain cheese pizza when what you really need is one half with pineapple, the other with anchovies, and gluten-free crust for good measure. 🍕 

When your query needs to adapt to user input, variable filters, or shifting logic, dynamic SOQL steps in to save the day - capable, flexible, and just a little bit risky (in a good way, mostly).

It’s time to query like it’s 2025. 🎉 

TABLE OF CONTENTS

Dynamic SOQL

• What is dynamic SOQL

• Why go dynamic?

• Build-a-query workshop

• Query crimes and how to avoid them

• Dynamic SOQL is a power tool

LIKE MAD LIBS, BUT FOR QUERIES

WHAT IS DYNAMIC SOQL

Dynamic SOQL is what happens when Apex developers decide that static queries are just too vanilla. 🥱 

Unlike regular SOQL, which is hardcoded at compile-time, dynamic SOQL is constructed as a string at runtime.

This gives you the flexibility to build queries on the fly based on variables, user input, or complex logic. 🔥 

Here’s what that looks like:

String objName = 'Contact';
String query = 'SELECT Id, Name FROM ' + objName + ' WHERE LastName = \'Smith\'';
List<SObject> results = Database.query(query);

👍️ Need to add filters conditionally?

👍️ Query different objects?

👍️ Inject a bit of logic into your logic?

Dynamic SOQL lets you do all that. But, halt - there be dragons here. 🐉 

BECAUSE NOT EVERY FILTER IS A FOREVER FILTER

WHY GO DYNAMIC?

Dynamic SOQL shines in scenarios where the query structure is dependent on input:

🔍️ Search pages with optional filters

♻️ Reusable utility classes across multiple SObjects

🎛️ Admin-controlled filters stored in Custom Metadata or Custom Settings

🤔 Complex logic paths in Apex that generate different queries per user or scenario

Imagine trying to cram five optional filters into a static query. No thanks.

Suddenly you're juggling if-else spaghetti just to add a WHERE clause. 🤹 

Dynamic SOQL elegantly sidesteps that mess - if you write it carefully.

But wait, isn’t this a security risk? 🔐 

Well, yes, it can be.

Dynamic SOQL opens the door to SOQL Injection, which is basically the evil twin of convenience. 🙈 

Think SQL Injection but Salesforce-flavored. Here’s a tragic example:

String userInput = ApexPages.currentPage().getParameters().get('name');
String query = 'SELECT Id FROM Contact WHERE LastName = \'' + userInput + '\'';
List<Contact> contacts = Database.query(query);

Now imagine the user inputs: Smith' OR Name != '

This would resolve to:

SELECT Id FROM Contact WHERE LastName = 'Smith' OR Name != ''

Your WHERE clause just went rogue. Yikes. 😱 

The fix? Rather easy, actually. Use binding:

String userInput = ApexPages.currentPage().getParameters().get('name');
String query = 'SELECT Id FROM Contact WHERE LastName = :userInput';
List<Contact> contacts = Database.query(query);

The : operator in dynamic SOQL safely binds variables - no escape characters, no hijacking, just sweet, secure Apex. 😋 

BECAUSE COPY-PASTING FROM STACKOVERLOW WON’T SCALE

BUILD-A-QUERY WORKSHOP

Here’s a simple example to drive the point home. 🏘️ 

Let’s say you’re building a dynamic search for Accounts with optional filters.

public List<Account> findAccounts(String type, String industry) {
    String baseQuery = 'SELECT Id, Name FROM Account';
    List<String> conditions = new List<String>();

    if (type != null) {
        conditions.add('Type = :type');
    }

    if (industry != null) {
        conditions.add('Industry = :industry');
    }

    if (!conditions.isEmpty()) {
        baseQuery += ' WHERE ' + String.join(conditions, ' AND ');
    }

    return Database.query(baseQuery);
}

Notice:

🔗 We use String.join() to build a conditional WHERE clause

🦺 We use :binding to inject values safely

😎 We still look cool doing it

YOU CAN BE A WIZARD OR A WANTED CRIMINAL

QUERY CRIMES AND HOW TO AVOID THEM

Before you start dynamically querying everything in sight like a SOQL sorcerer, let’s talk safety. 🛟 

Dynamic SOQL can be powerful - but without a little discipline, you might end up with a heap-sized disaster, a security breach, or just a deeply disappointed future-you.

Here are the commandments to follow and the cardinal sins to avoid: 👇️ 

✅ Always use bind variables (:) for dynamic values
✅ Use Schema APIs (like Schema.getGlobalDescribe()) to validate object/field names
✅ Avoid building your entire app around dynamic queries
✅ Limit fields and rows - you’re still living in governorland
✅ Use custom metadata to define reusable filter logic for low-code configurability

❌ Concatenate user input directly into SOQL strings
❌ Skip LIMIT on large queries
❌ Assume object or field names without validation
❌ Hardcode fields when your query depends on schema differences
❌ Forget governor limits still apply

JUST DON’T BUILD A TREEHOUSE WITH A CHAINSAW

DYNAMIC SOQL IS A POWER TOOL

Dynamic SOQL gives developers superpowers: the ability to craft adaptive queries in a highly declarative platform. 🦸 

But with that power comes risk.

Used responsibly, it unlocks flexible solutions. 💪 

Used carelessly, it unlocks security holes, governor limit violations, and developer shame. 🫢 

So go forth, wield your queries wisely, and remember: just because it compiles doesn’t mean it’s safe.

SOUL FOOD

Today’s Principle

and now....Salesforce Memes