HOWTO: Avoiding subqueries with our Web Services

bsoremsugar —  March 19, 2012 — 16 Comments

A recent change to our Web Services has caused quite a stir amongst our third party developer community recently, which is that we removed the ability to do subqueries in the various calls that allow you to pass in straight SQL as parameters to the calls. From the feedback we got, things tended to be a problem in one of two areas:

  • Doing search for a record based on an email.
  • Doing lookups of records related to the current record.

For the first area, we’ve added a small reprieve for those who have depended on using subqueries in such a way. Here’s a code example of how to do this:

$parameters = array(
    'session' => $sessionId,
    'module_name' => 'Accounts',
    'query' => "accounts.id in ( select bean_id from email_addr_bean_rel inner join email_addresses where email_addr_bean_rel.email_address_id = email_addresses.id and email_addr_bean_rel.deleted = 0 and email_addresses.deleted = 0 and bean_module = 'Accounts' and email_addresses.email_address = 'myemailaddress@address.com' )",
    'order_by' => 'name',
    'offset' => '',
    'select_fields' => array('name','email1'),
    );

$json = json_encode($parameters);
$postArgs = array(
                'method' => 'get_entry_list',
                'input_type' => 'JSON',
                'response_type' => 'JSON',
                'rest_data' => $json
                );
curl_setopt($curl, CURLOPT_POSTFIELDS, $postArgs);

// Make the REST call, returning the result
$response = curl_exec($curl);

For the second call, you can instead use the get_relationships call to do the work here instead of doing the subquery in the ‘query’ parameter of the get_entry_list call like we did above. See this example:

$parameters = array(
    'session' => $sessionId,
    'module_name' => 'Accounts',
    'module_id' => '119268fb-e21a-b536-6c88-4f640959c3ca',
    'link_field_name' => 'contacts',
    'related_module_query' => '',
    'related_fields' => array('id','first_name','last_name'),
    'related_module_link_name_to_fields_array' => array(),
    'deleted' => false,
    'order_by' => 'last_name',
    );

$json = json_encode($parameters);
$postArgs = array(
                'method' => 'get_relationships',
                'input_type' => 'JSON',
                'response_type' => 'JSON',
                'rest_data' => $json,
                );
curl_setopt($curl, CURLOPT_POSTFIELDS, $postArgs);

// Make the REST call, returning the result
$response = curl_exec($curl);

Now the feedback we’ve lost is that doing it this way makes it difficult to deal with large datasets coming back from this call, since up till now get_relationships did not have support for pagination. We’ve added this in the latest 6.4.2, 6.3.2, 6.2.5, and 6.1.8 releases with the new v4_1 API added. You can augment the above call with ‘limit’ and ‘offset’ parameters to the call as shown below:

$parameters = array(
    'session' => $sessionId,
    'module_name' => 'Accounts',
    'module_id' => '119268fb-e21a-b536-6c88-4f640959c3ca',
    'link_field_name' => 'contacts',
    'related_module_query' => '',
    'related_fields' => array('id','first_name','last_name'),
    'related_module_link_name_to_fields_array' => array(),
    'deleted' => false,
    'order_by' => 'last_name',
    <strong>'offset' =&gt; 1, 'limit' =&gt; 3,</strong>
    );

$json = json_encode($parameters);
$postArgs = array(
                'method' =&gt; 'get_relationships',
                'input_type' =&gt; 'JSON',
                'response_type' =&gt; 'JSON',
                'rest_data' =&gt; $json,
                );
curl_setopt($curl, CURLOPT_POSTFIELDS, $postArgs);

// Make the REST call, returning the result
$response = curl_exec($curl);

Hopefully these two code examples can serve as a guide to fix any web services integrations you have done with a Sugar instance. Feel free to sound off in the comments or contact us on Twitter with any questions or feedback you have.

16 responses to HOWTO: Avoiding subqueries with our Web Services

  1. 
    Enrico Simonetti March 19, 2012 at 10:06 pm

    Hi John,
    Do you think there was a possible way to sanitise the subquery to prevent SQL injection and still leave the current functionality backwards compatible?

    As you may imagine, it is really painful to not have backward compatibility, for someone that deployed few portals/websites that interact with SugarCRM.

    What about integrations with third party products?
    Do you have any suggestion in this case, apart from re-factoring most of the integration’s code?

    I’m looking forward to your suggestions.

    Enrico

    • 

      Great question! The reason we went will allow emails and teams subqueries is that the current API doesn’t have a way to do the same thing via get_relationships ( i.e. doing queries by email address or team name ). The other tables can do basically the same query as before leveraging get_relationships instead. But again, if you have a use-case we aren’t considering, please let us know.

      And yes, you can alter the SugarSQLValidate::$subquery_allowed_tables property itself, but currently this isn’t upgrade-safe as you know. We rather not expose the ability to alter this via a config setting since this can potentially open a security hole in your instance.

      We recognize that there are many developers needing to do code changes in order to support this change, and we apologize for having to do this. Unfortunately, we had no other choice but to make the change to prevent malicious access to instance’s data.

      Thanks for your feedback!

  2. 

    Hi John,

    We’ve noticed that the new v4_1 is not available on SugarCRM 6.5 beta. Are SugarCRM happy to confirm that this extension to the web service will be in place once 6.5 (and higher) is released?

    Thanks,

    Jon

    • 

      Yes, this will be added in the next beta of 6.5, coming later today. It missed the merge cutoff for the initial beta code freeze since it was just added in 6.4.1.

  3. 
    Enrico Simonetti July 5, 2012 at 10:08 pm

    Hey Jon,
    I just found a “bug” with the email query used above:

    accounts.id in ( select bean_id from email_addr_bean_rel inner join email_addresses whereemail_addr_bean_rel.email_address_id = email_addresses.id and bean_module = ‘Accounts’ andemail_addresses.email_address = ‘myemailaddress@address.com’ )”,

    If you notice, it is not filtering for deleted records on the email_addr_bean_rel and on the email_addresses.
    I noticed this problem because my integration was showing accounts that are no longer related to a specific email address.

    For everyone’s reference, the query should look something like this:

    accounts.id in ( select bean_id from email_addr_bean_rel inner join email_addresses on email_addr_bean_rel.email_address_id = email_addresses.id where email_addr_bean_rel.deleted=0 and email_addresses.deleted=0 and bean_module = ‘Accounts’ and email_addresses.email_address = ‘myemailaddress@address.com’ );

    I hope this helps… everyone that was going crazy like me :)

    Cheers

  4. 

    hi Jon,
    i am using ce6.5.0 and rest v4_1 and following code in JavaScript.
    I am getting all related records but i want to get only one record
    i cant figure out the issue ,please help me

    rest_data={
    session : session_id,
    module_name : module,
    module_id : recordId,
    link_field_name : linkModule, //in lower case
    related_module_query : ”,
    related_fields : relatedFields,
    related_module_link_name_to_fields_array : [],
    deleted : false,
    offset : ”,
    limit : 1
    }

    • 

      I don’t think you can choose to have only one related record returned. You’ll need to handle that in the processing of the response on your side.

      • 

        Thanks jon for quick response .
        Yes i can handle in processing but actually i want to avoid bulk of records so that there should not delay in response due to huge records.
        thanks again

  5. 

    So how would one query based on the value of a custom field given that subqueries are not allowed?

  6. 

    I know this is an old post, but…

    I am trying to get Teams information from Account records. Where do I find the correct ‘module_id’ for my Accounts? Also, is the ‘link_field_name’ supposed to be ‘teams’ ?

    Thanks,
    Chuck

Trackbacks and Pingbacks:

  1. Illimité - April 26, 2012

    Illimité…

    [...]SugarCRM Developer Blog » Blog Archive » HOWTO: Avoiding subqueries with our Web Services[...]…

  2. SOAP API Subquery Access Denied - July 11, 2012

    [...] [...]

  3. how to link email address to contact in sql query in synolia - July 25, 2012

    [...] [...]

  4. SOAP - Access denied - get_entry_list - relations with subquery - August 30, 2012

    [...] [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s