Only variable references are allowed in dynamic SOQL/SOSL

Question

I'm trying to re-request an Asset from the database:

asset = Database.query('SELECT ID, Price, Asset_MRR__c FROM Asset WHERE id =: ' + asset.id + ' limit 1');

But I get an error:

Only variable references are allowed in dynamic SOQL/SOSL

What am I doing wrong?

Answer 1

If you use : in your query, Apex expects a variable. In case you are querying with the string, you should not use the :, but escape single quotes(\'). So your query should look like this:

asset = Database.query('SELECT ID, Price, Asset_MRR__c FROM Asset WHERE id =\'' + asset.id + '\' limit 1');

You could also use :, but then just put asset.id in your query string:

asset = Database.query('SELECT ID, Price, Asset_MRR__c FROM Asset WHERE id =:asset.id limit 1');

EDIT

Just checked the second example in developer console with this code:

String testName = 'test';
List<Account> accList = Database.query('SELECT Id, Name FROM Account WHERE Name = :testName');
System.debug('accList: ' + accList);

The output was:

09:09:32:100 USER_DEBUG [3]|DEBUG|accList: (Account:{Id=0018E000005L8D3QAK, Name=test, RecordTypeId=012w0000000QSfUAAW}, Account:{Id=0018E000007eRInQAM, Name=test, RecordTypeId=012w0000000QMrxAAG})

Also, using LIMIT 1 when querying with WHERE Id = ... is not necessary, ids are unique anyway, so you will get 0 or 1 results anyway.

Answer 2

The dynamic SOQL documentation explains that simple bind variables (introduced with a :) can be used, that is a reference to a variable (but not a dotted expression or method call). So the pattern to use here is this:

Id assetId = asset.Id;
asset = Database.query('SELECT ID, Price, Asset_MRR__c FROM Asset WHERE id =:assetId limit 1');

where you assign the more complex expression to a variable and bind the simple variable into the dynamic SOQL.

Advantages of this approach over string concatenation are:

• Cleanest way to avoid SOQL injection risk
• Makes the code easier to read (and write) because it avoids extra quotes and extra string concatenation

(In other database environments binding values also allows caching of query plans to work better because the query text itself does not vary; I have not seen any mention of that for SOQL though.)

Answer 3

try this out .

String query='Select ' + fieldName + ' FROM ' + objectName + ' WHERE Id IN';

query += ':ids';

sobj= Database.query(query); 

or

'Select ' + fieldName + ' FROM ' + objectName + ' WHERE Id = \'' + com.Account_Lookup__c + '\''+' Limit 1'

Answer 4

Try to use this query:

'SELECT ID, Price, Asset_MRR__c FROM Asset WHERE id = \' '+ asset.id + '\' limit 1'

Also, not nessessary code limit when you try to get record by id