How can I troubleshoot and resolve the issue of “only variable references are allowed in dynamic Soql/sosl?”

540 Asked by Bhaanumatishukla in Salesforce , Asked on Jan 30, 2024

I am currently working on a specific task that is related to working on a dynamic SOQL query. While working on it I was getting an issue message that was showing “Only variable references are allowed in dynamic Soql/sosl”. How can I troubleshoot and resolve this particular issue?

Answered by Daniel Cameron

In the context of Salesforce, you can resolve and troubleshoot the issue of “only variable references are allowed in dynamic Soql/sosl” by using the string concatenation or even template strings for building your dynamic Query which can resolve the issue. Here is the example given in Apex:-

String searchTerm = ‘Example’;

String dynamicQuery = ‘SELECT Id, Name FROM CustomObject__c WHERE Name LIKE ’%’ + searchTerm + ‘%’’;

List results = Database.query(dynamicQuery);

In this above example the “searchTerm” is a variable that would help in creating a flexible search condition.

Your Answer

Answer (1)

Ranjana

The error "Only variable references are allowed in dynamic SOQL/SOSL" in Salesforce occurs when you attempt to use dynamic SOQL (or SOSL) queries with direct literals or expressions that are not variables. Salesforce restricts dynamic queries to prevent potential security vulnerabilities such as SQL injection attacks. Here’s how you can troubleshoot and resolve this issue:

Understanding Dynamic SOQL/SOSL

Dynamic SOQL and SOSL allow you to build queries dynamically at runtime based on variables or user input. This is useful when you need to create queries where the criteria or structure is not known until runtime.

Common Causes of the Error

Using Literal Values: Attempting to concatenate or include literal values directly in the dynamic query string.

  String searchKey = 'Acme';String dynamicQuery = 'SELECT Id, Name FROM Account WHERE Name = '' + searchKey + ''';

Salesforce prohibits this because it can potentially lead to SQL injection vulnerabilities.

Incorrect String Formatting: Improperly formatting the dynamic query string can also cause this error.

  String searchKey = 'Acme';// Incorrect formatString dynamicQuery = 'SELECT Id, Name FROM Account WHERE Name = ' + searchKey;

Resolving the Issue

To resolve the "Only variable references are allowed in dynamic SOQL/SOSL" error, follow these best practices for constructing dynamic queries in Salesforce:

Use Binding Variables: Use binding variables (:) to reference Apex variables directly in your dynamic queries. This ensures that Salesforce can validate and sanitize the input.

  String searchKey = 'Acme';List accounts = Database.query('SELECT Id, Name FROM Account WHERE Name = earchKey');

Note: Ensure that searchKey is a variable in your Apex code.

Use String Interpolation: Use string interpolation with Apex variables to construct dynamic query strings safely.

  String searchKey = 'Acme';String dynamicQuery = 'SELECT Id, Name FROM Account WHERE Name = earchKey';List accounts = Database.query(dynamicQuery);

Avoid Concatenating Values: Avoid concatenating literal values directly into the query string. Always use binding variables or string interpolation instead.

Test and Validate: Test your dynamic queries thoroughly to ensure they produce the expected results and to verify that they do not trigger the "Only variable references are allowed" error.

Example of Correct Usage

Here’s an example demonstrating the correct usage of dynamic SOQL with binding variables in Salesforce Apex:

  String searchKey = 'Acme';String dynamicQuery = 'SELECT Id, Name FROM Account WHERE Name = earchKey';List accounts = Database.query(dynamicQuery);

Conclusion

By using binding variables or string interpolation with Apex variables, you can safely construct and execute dynamic queries in Salesforce without encountering the "Only variable references are allowed in dynamic SOQL/SOSL" error. This approach ensures that your queries are secure and comply with Salesforce security best practices. Always validate user inputs and sanitize data to prevent security vulnerabilities in your Salesforce applications.

4 Months