Build a String for Database.query

Question

for an exstention controller of a VF page, I need to build a query to retrieve all the lead with a Company name similar to a variable. My variable name is compagnia: Now I'm trying to build the query like this:

String query='select Id,Company from Lead where Company like'+ '%'+compagnia+'%'
List <Lead> l=Database.query(query);

But the query doesn't work, how Can I solve this problem?

Answer 1

You missed to place quotes. Here is the correction:

String query='select Id,Company from Lead where Company like\''+ '%'+compagnia+'%\''
List <Lead> l=Database.query(query);

Answer 2

The best way to avoid this problem is to use binding syntax if you need to use dynamic SOQL - it is easier to get right and cleaner to read. Importantly it also eliminates the risk of a SOQL Injection attack. Note that the value bound has to be a simple variable reference (e.g. not a dotted expression) for the dynamic SOQL case.

So:

String companyLike = '%' + compagnia + '%';
String query = 'select Id, Company from Lead where Company like :companyLike';
List<Lead> leads = Database.query(query);

But for your case static SOQL is cleaner still and gets checked by the compiler so is the better way to go:

String companyLike = '%' + compagnia + '%';
List<Lead> leads = [select Id, Company from Lead where Company like :companyLike];

Answer 3

As Keith stated, use binding would be better as it also avoids injection.

I'd recommend building such dynamic queries using a library such as Query.apex. Using this library, your statement becomes:

List<Lead> leads = new Query('Lead')
.selectFields('Company')
.addConditionLike('Company', '%compagnia%')
.run();

This would also solve injection automatically. Moreover, you would end up having less semantic and syntax errors when building the dynamic query string, since your query is more structural.