Salesforce Object Query Language (SOQL) is used to search data in your salesforce
Org. SOQL is designed specifically for Salesforce data.

Dynamic SOQL refers to the ability to create SOQL queries at runtime as strings with Apex code. This means you can create queries dynamically based on various conditions such as user inputs while in static SOQL , we have to write a hard-coded SOQL query.

Writing the Dynamic SOQL

To create a dynamic SOQL query at run time, we can use the Database.query or Database.queryWithBinds methods. Lets discuss these both methods-

Database.query in Dynamic SOQl

With Database.query(), you write your whole query as a single piece of text (a “string”).
You can include variables in the query by adding them to the string with + symbols.

Example –

String queryString = 'SELECT Id, Name FROM Account WHERE Industry = \'Technology\'';
List accounts = Database.query(queryString);

Database.queryWithBinds in Dynamic SOQl

Database.queryWithBinds() does the same thing but uses placeholders for the variables.
Instead of building the query with + symbols, you use :variable to add the variable directly.

Example

String industry = 'Banking';
String queryString = 'SELECT Id, Name FROM Account WHERE Industry = :industry';
List accounts = Database.queryWithBinds(queryString);

Real example with explanation

public static List articleSearch(String searchVal) {
// Sanitize input
    String sanitizedSearchVal = String.escapeSingleQuotes(searchVal);

    // Create a dynamic query based on the number of words included in the search string
    String[] searchStringList = sanitizedSearchVal.split(' ');
    String online = 'Online';

    // Initialize the query string
    String queryString = 'SELECT Title, Url FROM Knowledge__article WHERE Status = \'' + online + '\' AND (';

    // Build the condition string
    List<String> conditions = new List<String>();

    for (String keywordSearch : searchStringList) {
        conditions.add('Title LIKE \'%' + String.escapeSingleQuotes(keywordSearch) + '%\'');
    }

    queryString += String.join(conditions, ' AND ') + ')';

    // Debug the final query string
    System.debug('queryString: ' + queryString);

    List<Knowledge__article> tArticles = new List<Knowledge__article>();
    if (searchVal != null && searchVal.trim().length() > 0) {
        tArticles = Database.query(queryString);
    }

Explanation

String sanitizedSearchVal = String.escapeSingleQuotes(searchVal);

String.escapeSingleQuotes() is used here to sanitize the input and prevent SOQL injection by escaping any single quotes in searchVal. This makes sure that special characters in the search string don’t interfere with the SOQL query.

String[] searchStringList = sanitizedSearchVal.split(' ');

This line splits the sanitized search string into an array of keywords, using spaces . Each word in the search string becomes a separate word in searchStringList, allowing us to search each word individually in the query.

String queryString = 'SELECT Title, Url FROM Knowledge__article WHERE Status = \'' + online + '\' AND (';

The query string starts with a SELECT statement to retrieve Title and Url fields from the Knowledge__article object. It also includes a condition to only fetch articles with a Status of “Online.”

List conditions = new List();
for (String keywordSearch : searchStringList) {
conditions.add('Title LIKE \'%' + String.escapeSingleQuotes(keywordSearch) + '%\'');
}

A list called conditions is created to store each condition for the query. For each keyword in searchStringList, it adds a LIKE condition to check if the Title contains that keyword.
The LIKE operator with % wildcard characters allows partial matching, so if a title contains any of the words in searchStringList, it will be included in the results.
queryString += String.join(conditions, ‘ AND ‘) + ‘)’;

String.join(conditions, ‘ AND ‘) combines each condition with an AND operator. This makes the query require all keywords to be present in the title for a match, creating a more restrictive search.

List tArticles = new List();
if (searchVal != null && searchVal.trim().length() > 0) {
tArticles = Database.query(queryString);
}

the Database.query() method is used to execute the dynamically constructed query. The results are stored in tArticles, a list of Knowledge__article records that match the search criteria.

Advantages of Dynamic SOQL

• Flexible Query Construction – We can build different queries based on what the condition is entered in a search box or based on specific conditions. This is mostly helpful when you don’t know the exact filter criteria ahead of time.
• Enhanced User Interactivity – Dynamic SOQL lets you create interactive experiences where users can search for specific information by typing in their search terms. It builds the query on the fly, so users get results based on what they’re looking for. This makes it easy to add advanced search, filtering, and reporting features based on what each user needs.
• Reduces Code Duplication- We don’t have to write multiple static SOQL queries for every case or condition that can be possible, we can generate queries dynamically.
• Supports Complex Query Scenarios- When working with complex query logic that is dependent on multiple variables, including user role, permissions, or certain criteria, dynamic SOQL is very useful.
• Security- When you use dynamic SOQL with bind variables , it helps keep your application safe from SOQL injection attacks.

Considerations while using Dynamic SOQL

Preventing SOQL Injection – SOQL injection is a technique by which a user causes your application to execute database methods you didn’t intend by passing SOQL statements into your code. This can occur in Apex code whenever your application relies on end-user input to construct a dynamic SOQL statement and you don’t handle the input properly.
For example-

String qry = 'SELECT Id FROM Contact WHERE ' +
    '(IsDeleted = false and Name like \'%' + customerName + '%\')';
List<Contact> queryResult = Database.query(qryString);

The problem in this query is –

The value of customerName is added directly into query without checking it. If someone enters something tricky as customerName, like Test%' OR IsDeleted = true OR customerName like '%, it will change the query to:

SELECT Id FROM Contact WHERE (IsDeleted = false AND customerName LIKE '%Test%' OR IsDeleted = true OR customerName LIKE '%')

This can break your logic and show more data than allowed.

To prevent SOQL injection, use the escapeSingleQuotes method. This method adds the escape character (\) to all single quotation marks in a string that is passed in from a user. The method ensures that all single quotation marks are treated as enclosing strings, instead of database commands.

You may also checkout other blogs and courses on our website.

You can also check out this video to learn more about SOQL.