Only variable references are allowed in dynamic SOQL/SOSL

  • 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?

  • 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.

    Your 2nd query is wrong , if SOQL is between ' ' , then it will be considered as a String, So there won't be any actual 15 digit asset id available in the query.

    @SfdcBat no, you are wrong. Just checked it in dev console and it works

    I apologies, I thought there is no way to get the Id in such a query

  • 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.)

  • 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

    Not a good habit to get into: if instead of `asset.id` you were using a string value that came from the UI you would be leaving your application open to SOQL injection risk.

  • 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'
    

    Hi Shyamesh, welcome to SFSE! After looking at the other answers, this one isn't substantially different than Ruslan Kurchenko's. It's also missing some elements of a good answer. To me, a good answer will explain several things: - What is the problem? - Why is it a problem? - How can we fix the problem? - Why does the fix work? I like to keep in mind that other people will be finding this question, and trying to apply it to their own, similar (but different) problems. A good answer will help other people as well, even if it's only let them know that their problem is not solved by this answer.

License under CC-BY-SA with attribution


Content dated before 7/24/2021 11:53 AM