Dynamic SOQL and SOSL

Dynamic SOQL and SOSL

Dynamic SOQL and SOSL:

Dynamic SOQL/SOSL is nothing but the creation of a SOQL/ SOSL string at runtime with an Apex script. Dynamic SOQL enables you to create more flexible applications means developer can pass parameters dynamically. For example, you can create a search based on input from an end user, or update records with varying field names on different objects.

Following are the steps to create a dynamic SOQL query at runtime, use the database query method, in one of the following ways:

1. Return a single salesforce Object when the query returns a single record:

Code: sObject S = Database.query (string_limit_1);

Database query methode return number of sObjects.

2. Return a list of sObjects when the query returns more than a single record of the object:

Code: List ListofObject= Database.query(string);

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

Live example Dynamic SOQL Code:

public String getSearchQuery(){

companyName = inputFieldValue.Company+'%';

cityName = '%'+inputFieldValue.City__c+'%';

countryName = inputFieldValue.Address_Country__c;

String Operator='='; 

dist = 'Distributor';

partnerStatus ='Active';

locationType = 'Headquarters';

String Query;

if(countryName !=null && countryName !=''){

Query='Select Id,Name,City__c,Country_list__c , State_LIst__c, Location_Type__c, BP_Link_Id__c from Account where Name like: distributorName and Partner_Type_New__c = :dist and Location_Type__c = :locationType and Status__c = :partnerStatus and Country_list__c= :countryName'; 

}else{

Query='Select Id,Name,City__c,Country_list__c , State_LIst__c, Location_Type__c, BP_Link_Id__c from Account where Name like: distributorName and Partner_Type_New__c = :dist and Location_Type__c = :locationType and Status__c = :partnerStatus'; 

}

return Query;

}

Description:

Above code snippet method return Dynamic SOQL where we are passing some fields value at run time and also written some condition on if Country name is blank.

SOSL Statements

SOSL Statements

SOSL (Salesforce Object Search Language) statements estimate to the list of lists of salesforce Objects, where each list contains the search results for a particular salesforce Object type.

The result lists are always returned in the same order as they were specified in the SOSL (Salesforce Object Search Language) query.

• SOSL queries are only supported to the Apex classes and the anonymous blocks.
• You cannot use a SOSL query in the trigger.
• If SOSL query does not return any records for a specified sObject type, and the search results include an empty list for that salesforce Object.

For example

You can return a list of accounts, contacts, opportunities, and leads that begin with phrase map utile:

SOSL:

List> searchList = [FIND 'map*' IN ALL FIELDS RETURNING Account (id, name),Contact, Opportunity, Lead];

Description:

The syntax of the FIND clause in Apex differs from the syntax of the FIND clause in the Web services API:

• In Apex, the value of the FIND clause is demarcated with single quotes.

For example:

FIND 'map*' IN ALL FIELDS RETURNING Account (id, name), Contact, Opportunity, Lead.

• In the Force.com API, the value of the FIND clause is demarcated with braces.

Example of SOSL:

FIND {map*} IN ALL FIELDS RETURNING Account (id, name, address), Contact, Opportunity, Lead From search List, you can create arrays for each object returned as given below:

Account [] accounts = ((List) search List [0]); //Account object

Contact [] contacts = ((List) search List [1]); //Contact object

Opportunity [] opportunities = ((List) search List [2]); //Oppty

Lead [] leads = ((List) search List [3]); //Lead object

SOQL and SOSL statements in Apex can reference Apex code variables and the expressions if they are preceded by a colon (:).

This use of a local code variable within a SOQL or SOSL statement is called a bind (concatenate). The Apex parser first evaluates the local variable in code context before executing the SOQL or SOSL statement. Bind expressions can be used as given below:

• Search string in FIND clauses
• Filter literals in WHERE clauses
• Numeric value in LIMIT clauses
• IN or NOT IN operator in WHERE clauses, allowing filtering on a dynamic set of values. Note that this is of particular use with a list of IDs or Strings, though it works with lists of any type.
• Division names in WITH DIVISION clauses

Dynamic SOSL

Dynamic SOSL (Sales force Object Search Language) refers to the creation of a SOSL string at runtime with an Apex script. Dynamic SOSL enables you to create more flexible applications which are most beneficial in sales force.

For example, you can create a search based on input from an end user, or update records with varying field names. To create a dynamic SOSL query at runtime, use the search query method.

Dynamic SOSL e.g:

List> mySOSLQuery = search. query (SOSL_search_string);

The following example exercises a simple SOSL query string.

String searchqueryString='FIND\'Edge*\'IN ALL FIELDS RETURNING

Account (id, name, address), Contact, Lead';

List>searchList=search.query(searchqueryString);

Description:

Dynamic SOSL statements evaluate to a list of lists of salesforce Objects, where each list contains the search results for a particular sObject types.

The result lists are always returned in the same order as they were specified in the dynamic SOSL query. From the example above, the results from Account are first, then Contact, then Lead.

SOSL Injection

SOSL (Sales force Object Search Language) injection is the technique by which a user causes your application to execute database methods you did not intend by passing SOSL statements into your script. This can occur in an Apex script whenever your application relies on end user input to

construct a dynamic SOSL statement and you do not handle the input properly.

To prevent SOSL injection, use the escapeSingleQuotes method. This method adds the escape character (\) to all single quotation marks in a string that is passed in from a user. The method ensures that all single quotation marks are treated as

enclosing strings, instead of database commands.

Code:

public Account[] getAccountInfo() {

    String userInput = Apexpages.currentPage().getParameters().get('nameofAccount');

    Account[] accs = database.query('SELECT name,address,city FROM Account WHERE name = \'' + userInput + '\'');

    return accs;

}

Description:

Above code explain it self user enters Account name and Dynamic SOSL used this name and returns the information about Account.

However if there is hacker user enter Account name like ‘Accoun1’ or ‘xxxxx’ so he can get your secure Account information. We can prevent this write the Class as “with sharing”

"IN" query Behaviour With SOSL

Find something interesting and would like to share with you all.

I was firing SOSL query in my organisation to get an account (using it as keyword search) :

List<List<SObject>> lst = [Find 'test*' IN ALL FIELDS Returning Account] ;

There are more than 1000 records of account in my organisation, as SOSL query result limit I was returned with 200 records only. Record which I was expecting is not here in those 200 records. So I put a condition in INQUERY returning account :

List<List<SObject>> lst = [Find 'test*' IN ALL FIELDS Returning Account(id where id = '001A000000K1QS4')] ;

Now I was expecting the record to be returned as I put the Id in the INQUERY but still the record is not returned. Actual behaviour is first SOSL get its first 200 records then the INQUERY Id is searched from those 200 records.