Dynamic Query (SOQL)

Dynamic Query (SOQL) refers to the creation of a SOQL string at runtime with Apex code. Dynamic Query enables you to create more flexible applications. For example, you can create a search based on input from an end user, or update records with varying field names. To create dynamic query, use the database query method (e.g Database.query('string_soql')) in one of the following examples:
1. Return single sObject when the query method is executed
sObject obj = Database.query('string_limit_1');

Account acc =Database.query('select id, Name, billingCity from Account  LIMIT 1');

2. Return list of sObjects method when the query method is executed
List<sObject> objList = Database.query('string');

List<Account> accList = Database.query('select id, Name, billingCity from Account');

3. Query method in for loops

for(Account acc : Database.query('select id, Name from Account'))
{
System.debug(acc.Name);
}

The database query method can be used wherever an inline SOQL query can be used, such as in regular assignment statements and for loops. The results are processed in much the same way as static SOQL queries are processed.

Bind Variable in Dynamic Query
You can use simple bind variables in dynamic query strings. The following is allowed:

String myTestString = 'TestName';
List<sObject>
sobjList = Database.query('SELECT Id FROM CustomObject__c WHERE Name =:myTestString');
//Variable as collection
Set<String> names= new Set<String>{‘Test1’,’Test2’};
List<sObject> sobjList = Database.query('SELECT Id FROM CustomObject__c WHERE Name IN :names');

Note:
SOQL injection can occur in Apex code whenever your application relies on end user input to construct a dynamic SOQL statement and you do not handle the input properly
To prevent SOQL injection, use the String.escapeSingleQuotes(stringToEscape) method. This method adds the escape character (\) to all single quotation marks in a string that is passed in from a user.
Dynamic query can’t use bind variable fields in the query string. The following example isn’t supported and results in a Variable does not exist error:

CustomObject__c cObj= new CustomObject__c(field1__c ='TestField');
List<sObject>
sobjList = Database.query('SELECT Id FROM CustomObject__c WHERE field1__c =
:cObj.field1__c');

The following is how to construct a dynamic SOQL statement

//inputName's data contains single quote
String inputName='Test\'s Acme';
DateTime dt = System.now();

String soql='select id,Name FROM Account';

//:dt binding variable
String whereClause=' WHERE lastmodifiedDate=:dt ';
if(String.isNotBlank(inputName)){
    //escape single quote
    whereClause+='AND Name =\''+  String.escapeSingleQuotes(inputName)    +'\'';
}

soql+=whereClause;

List<Account> accounts = Database.query(soql);