I signed up for the pre-release trial  of Winter ’09 today and am starting to explore the new features. One new or rather a fully supported feature is Dynamic SOQL (it was previously in pilot).  Dynamic SOQL lets you build and execute a query at runtime.  This feature is especially important if you are an ISV (Independent Software Vendor) or someone writing tools that has to work in multiple orgs where it is impossible to know until runtime what the query looks like.  As an example, a general purpose reporting tool would have to let the user select the objects and other criteria at runtime and build a query based on the selected criteria.  It is also useful if you want your code to be flexible and independent of the underlying data model.

Sample Code

Let us look at some code using a simple but somewhat contrived example.  We want to retrieve all Account or Contact records in a particular zip code. 

The VisualForce page

We build a VisualForce page which uses a custom controller called
SOQLController.  The page lets a user select either Account or Contact
object and type in a zipcode.  We use this information to build a query
string, execute it and return back a list of SObjects. We then display
the result set on our page.

<apex:page controller="SOQLController" >
<apex:pageBlock title="Test dynamic SOQL">
  <apex:form >
    <apex:outputText value="Select Object to Query on   "></apex:outputText>
    <apex:selectList value="{!objectName}" size="1" >
        <apex:selectOption itemValue="Account" itemLabel="Account" />
        <apex:selectOption itemValue="Contact" itemLabel="Contact" />
    </apex:selectList>
    <apex:outputText value=" Enter Zip Code    " ></apex:outputText>
    <apex:inputText value="{!zipCode}" />
    <apex:commandButton value="Query" action="{!query}" ></apex:commandButton>   
  </apex:form>
</apex:pageBlock>
<apex:pageBlock title="Retrived Ids">
    <apex:dataList value="{!queryResult}" var="r">
        <apex:outputText value ="{!r.Id}" />
    </apex:dataList>
</apex:pageBlock>
</apex:page>

The page looks as shown in Figure 1 below.

The Controller code and the query method

When the user hits the Query button, the query method in the controller is invoked. The code for the controller is shown below.  Since Account and Contact use different field names for storing the zip code we have to set that appropriately. We build the query as a string by concatenating the different pieces.  We then call the query method on the Database singleton which returns a list of SObjects.

public class SOQLController { 
   
    public String objectName { get {return objectName ;} set{ objectName = value ;} }
    public String zipCode { get { return zipCode ;} set { zipCode=value ; } }
    public List queryResult { get {return queryResult; }  set { queryResult = value ; }
   
    public PageReference query() {
        String zipFieldName ;      
        if( objectName == 'Account' ){
                zipFieldName = 'BillingPostalCode' ;
        }
        else if( objectName == 'Contact' ){
                zipFieldName = 'MailingPostalCode' ;
        }       
        // create the query string
        String qryString = 'SELECT Id FROM ' + objectName + ' WHERE ' + objectName + '.' +
            zipFieldName + '=' + ''' + zipCode + ''' ;
        // execute the query
        queryResult = Database.query(qryString) ;      
        return null;
    }
}

The result of running this with some sample data is shown in Figure 2 where we just display the Ids of the retrieved Contact records.

Some Observations

  • This example hardcodes the values of objects which a user can select – in this case to either Account or Contact and the field used as a filter – in this case the zipcode.  More typically such criteria would be built dynamically as well.  A general purpose reporting tool for example, would not know what custom objects an org may contain and the exact criteria which the user may want to use as filter.   
  • Use dynamic SOQL only if you require extra flexibility or if you can’t build a query ahead of time. Dynamic SOQL requires more coding and error checking on your part.
  • The query method on Database expects the entire query as a string. Since no compile time checking can be done, if the query is malformed or incorrect a runtime error will be thrown.  Your code should be able to handle it.
  • Letting end users control the form of your SOQL query can potentially make the code vulnerable to SOQL injection attack.  SOQL injection attack involves a malicious user making your program execute database method you didn’t intend to be executed.  For more details and how to protect yourself aganist it refer to the Apex documentation.

Figure 1

Dynamic_soql_page1_6

Figure 2

Dynamic_soql_page2

This is just one of the new features in Winter ’09 – I will be sharing my experience with other features as I explore them myself. 

Nick Simha

tagged , Bookmark the permalink. Trackbacks are closed, but you can post a comment.
  • http://developer.force.com/ Jon Mountjoy

    Nice Nick. This opens the door to all sorts of injection attacks, as you point out.
    The String class has a method to help here, escapeSingleQuotes(). From the docs: “Returns a String with the escape character () added
    before any single quotation marks in the String s.This
    method is useful when creating a dynamic SOQL
    statement, to help prevent SOQL injection.”

  • http://developer.force.com/ Jon Mountjoy

    Hi Nick
    I couldn’t get that code for SOQLExplorer to compile, until changed the 5th line to this:
    public List queryResult { get {return queryResult; } set { queryResult = value ; }}
    Regards,
    Jon

  • Nick Simha

    Thanks Jon – looks like the right parentheses had gotten chopped off

  • Raja Patnaik

    Thanks Nick…Good Job Buddy.

  • http://profile.typepad.com/6p0120a57112e2970b chellappa nataraja sait

    public List queryResult { get {return queryResult; } set { queryResult = value ; } }
    i have go the this Error: Compile Error: expecting a left angle bracket, found ‘queryResult’ at line 70 column 15

  • Shan

    I got this error:
    Error: Compile Error: expecting a left angle bracket, found ‘queryResult’ at line 5 column 12
    public List queryResult { get {return queryResult; } set { queryResult = value ; }}
    Do not understand why?

  • Nick Simha

    Shan,
    Look at the comment by Jon – looks like when you cut and paste something may have gotten chopped off. It’s been a while since I have looked at this code.

  • Nathalie

    It is because you need to specify what kind of List it is (SObject between brackets), as well as adding the final curly bracket at the end of the line.

  • Shan-MMAForce

    To correct the syntax up on top I did:
    public List queryResult { get {return queryResult; } set { queryResult = value ; }}

  • Shan-MMAForce

    This is great and it works wonderfully…
    I am now doing basically 2 inputtext boxes and 2 picklist fields for filtering.. Doing it one by one works well..
    Would you happen to know how to do it if an user picks 3 out of the 4 filters or 2 …
    My condition statement for one is :
    if(teachername!=null){
    qryString = qryString + ‘where ‘ + teacher + ‘=’ + ”’+ teachername+”’;
    }
    if(studentname!=null){
    qryString = qryString + ‘where ‘ + student + ‘=’ + ”’+ studentname+”’;
    }
    what happens if a user selects student and teacher?

  • http://profile.typepad.com/6p0115714a338f970b Gopi

    In the above query appliying escapeSingleQuotes() how can i eliminate SOQL injection attack.
    String qryString = ‘SELECT Id FROM ‘ + objectName + ‘ WHERE ‘ + objectName + ‘.’ + zipFieldName + ‘=’ + ”’ + zipCode + ”’ ; // execute the query queryResult = Database.query(qryString) ;

  • Anonymous

    Code is missing from the blog!

    The Report Doctor