Dynamic SOQL Query not accepting custom object id in where clause

Question

I have a structure question today regarding a dynamic SOQL query that I seem to be entering the wrong custom object id value for in the WHERE clause.

I have retrieved the custom object id from my SelectOption list object on a button submission. I then want to add this in to my SOQL query to filter further my search.

• My list of SelectOption instances are formed of id and name values respectively for value and label formed by retrieving and looping through a custom object named Project.
• Project__c is a lookup field.

Dynamic SOQL Apex function:

public String selectedProjectId { get; set; }

public String returnDynamicSOQL(string customObjectName)
{
    string dynamicSOQL = 'SELECT Id, Name, ' + customObjectName + '_Name__c, Date_Registered__c, Status__c, Originator__c, Owner__c, ' + customObjectName + '_Type__c, Project__c FROM ' + customObjectName + '__c ';
    if (projectIsSelected)
    {
        dynamicSOQL += ('WHERE Project__c.Id = ' + selectedProjectId);
    }

    return dynamicSOQL;
}

I am receiving this error:

Visualforce Error

System.QueryException: expecting a colon, found 'a4S24000000aPDfEAM' Class.RAIDController.getRAIDObjects: line 59, column 1 Class.RAIDController.getRisks: line 71, column 1

So I have the id for the project custom object selected at least, am I wrong in thinking this is what I need to retrieve the exact project by id? Does the WHERE clause need me to specify Project__c.Id or can I just use Project__c?

Any help much appreciated!

Answer 1

This sort of code can be open to SOQL Injection attack. Using the bind syntax avoids this in a clean way so prefer that:

 dynamicSOQL += ' WHERE Project__c.Id = :selectedProjectId';

Always better to be as specific as possible on data types (and in this case would also be defensive) so I suggest you also make this change:

public Id selectedProjectId { get; set; }

PS

Based on Matt's comment, the expression should really be:

dynamicSOQL += ' WHERE Project__c = :selectedProjectId';

(as Project__c is the ID of the Project object as it is a lookup field) or could also be:

dynamicSOQL += ' WHERE Project__r.Id = :selectedProjectId';

(as Project__r is a reference to the Project object that itself has an ID). The first pattern is what is normally used.

Answer 2

try this

dynamicSOQL += (' WHERE Project__r.Id = \'' + selectedProjectId+'\'');

or

dynamicSOQL += (' WHERE Project__c = \'' + selectedProjectId+'\'');

Answer 3

Change following line

dynamicSOQL += ('WHERE Project__c.Id = ' + selectedProjectId);

into

dynamicSOQL += (' WHERE Project__c.Id = \'' + selectedProjectId+'\'');

or

 dynamicSOQL += (' WHERE Project__c.Id =: selectedProjectId');

Also Project__c.Id either should be Projects__r or keep it only Project__c