Use of prepared statements in Sugar 7.9

Matthew Marum —  April 17, 2017

What are Prepared Statements?

Prepared Statements, also known as parameterized statements, is a database feature that allows the same or similar queries to be executed with more efficiency and greater security. It has also been a common Sugar platform feature request for some time.

A prepared statement looks something like this:

SELECT * FROM table WHERE id = ?

As you can see, a prepared statement is basically a SQL template that allows you to identify parameters that can be bound later. The database engine can parse, optimize, and cache this statement without executing it.

This reduces the overhead associated with parsing complex queries that are used frequently by applications like Sugar. For example, you can imagine that List View queries would benefit from prepared statements since they are often complex and executed each time a list is displayed, searched, filtered, or paginated. With prepared statements, the database will do less work each time one of these actions is repeated.

Another key strength of prepared statements is that it helps prevent SQL injection vulnerabilities. Parameters are expected to be constant values (strings, integers, etc.) and not SQL. So if an attacker managed to bind raw SQL as a parameter to a prepared statement it will not be interpreted as SQL. Attack defeated!

Database Administrators (DBAs) like prepared statements too because it tends to give them more control over how these queries are executed and cached by backend database engines. In the hands of a good DBA, prepared statements allows an application to be better tuned for high performance.

Changes in Sugar 7.9.0

Sugar 7.9.0 will be available in the next few weeks at the time of this writing. In Sugar 7.9.0, most queries that Sugar executes are now parameterized. Some new Query APIs have been added to support prepared statements as well.

The most important change is that we have adopted parts of Doctrine’s Database Abstraction Layer, especially the QueryBuilder class, for working with prepared statements.



The DBManager class will use Doctrine QueryBuilder for building INSERT and UPDATE queries.


The SugarQuery class will use Doctrine QueryBuilder for building SELECT queries.


The SugarBean class will continue to use DBManager class for saving all fields.

Things to watch out for in Sugar 7.9.0

There are a few things that Sugar Developers need to know as they prepare their code customizations for Sugar 7.9.0.

DBManager and SugarQuery API changes

As documented in the Sugar 7.7.1 Release Notes, many DBManager APIs and some SugarQuery APIs were deprecated as part of our plans to add prepared statement support to Sugar. These deprecated APIs have been removed in Sugar 7.9.0. If you haven’t already, you must migrate your custom code that uses these APIs to alternative APIs prior to Sugar 7.9.0 upgrades.

The following deprecated PHP classes and methods have been removed in this Sugar release.


The $execute parameter on DBManager::insertParams() and DBManager::updateParams() has also been removed.


SugarQuery::compileSql() was commonly used to debug the raw SQL built using SugarQuery APIs. SugarQuery::compileSql() was deprecated in Sugar 7.7.x and is no longer supported in Sugar 7.9.0. Because SugarQuery now uses prepared statements, it no longer compiles a complete SQL statement by itself. Remember that parameterized queries are assembled and executed within the DB engine. So you will need to separately fetch the parameterized SQL and the parameters. From this information, you can determine how the query will be executed.

For example,

$compiled = $query->compile(); // create compiled prepared statement
$compiled->getSQL(); // fetches parameterized SQL
$compiled->getParameters(); // fetches parameters

The $compiled->getSQL() will return SQL with placeholders instead of parameters:

SELECT * FROM users WHERE id=?

The $compiled->getParameters() will return an array of parameters:


How to use Prepared Statements in Sugar 7.9.0

If you already use SugarQuery or SugarBean then congratulations! Your code customizations will automatically benefit from prepared statements. We have made changes to both of these interfaces to ensure that they use prepared statements. The underlying behavior is transparent to custom code.

However, if you need more finesse with your queries then we will explore how to use prepared statements using new DBManager and Doctrine QueryBuilder APIs.

SELECT queries

For simple static SELECT queries, the changes are pretty straight forward.


$query = 'SELECT * FROM table WHERE id = ' . $this->db->quoted($id);


$query = 'SELECT * FROM table WHERE id = ?';
$conn = $db->getConnection();
$stmt = $conn->executeQuery($query, array($id));

In the case that query logic is variable or conditionally built then it makes sense to use Doctrine QueryBuilder directly.


$query = 'SELECT * FROM table';
if ($status !== null) {
    $query .= ' WHERE status = ' . $this->db->quoted($status);


$builder = $db->getConnection()->createQueryBuilder();
if ($status !== null) {
        'status = ' . $builder->createPositionalParameter($status))

INSERT queries

INSERT queries can be easily performed using DBManager class.


$query = 'INSERT INTO table (foo, bar) VALUES ("foo", "bar")';


$fieldDefs = $GLOBALS['dictionary']['table']['fields'];
$db->insertParams('table', $fieldDefs, array(
    'foo' => 'foo',
    'bar' => 'bar',

UPDATE queries

When updating records with known IDs or a set of records with simple filtering criteria, then DBManager can be used:


$query = 'UPDATE table SET foo = "bar" WHERE id = ' . $db->quoted($id);


$fieldDefs = $GLOBALS['dictionary']['table']['fields'];
$db->updateParams('table', $fieldDefs, array(
 'foo' => 'bar',
), array(
 'id' => $id,
), );

For more complex criteria or when column values contain expressions or references to other fields in the table then Doctrine QueryBuilder can be used.


$query = 'UPDATE table SET foo = "bar" WHERE foo = "foo" OR foo IS NULL';


$query = 'UPDATE table SET foo = ? WHERE foo = ? OR foo IS NULL';
$conn = $db->getConnection();
$stmt = $conn->executeQuery($query, array('bar', 'foo'));

Matthew Marum


Matt is the Director of Developer Advocacy for SugarCRM. Previously he was an Engineer on Sugar 7 and a Solutions Architect for the OEM program. He is also an avid trail runner, Boston Marathon qualifier and a karaoke aficionado.

12 responses to Use of prepared statements in Sugar 7.9


    Hi Matt,

    I’ve seen that will be removed from the “joinRaw” in SugarQuery, could you provide an example of how to use LEFT JOIN without it? I want to anticipate myself because I use it a lot.

    Thank you.


    hello! please guide us all about the delete queries too


      Hi Albert –
      Actually, I wouldn’t recommend using DELETE queries though those would certainly work too with Doctrine QueryBuilder.

      In general, Sugar uses a “soft delete” method where records are marked as deleted so they don’t appear in the user interface. This is accomplished by making sure the SELECT queries we use use filter those out. This means these records are easily recoverable if someone accidentally deletes something. Otherwise, you are entirely relying on your last DB backup.

      From PHP, you can use SugarBean#mark_deleted() to soft delete individual records. From SQL, you can use UPDATE queries to set ‘deleted’ column to ‘1’ on records.

      There is a ‘Prune Database’ scheduler job that you can run via Sugar Administration panel to permanently remove soft deleted records from your Database later. It does not run by default.


    thank you. This was a much-awaited feature in the out-of-the-box product.

    1. Would Sugar now generate only prepared Statements out of the box for all SQLs?
    2. Can this be configurable ?
    3. For Debug purposes, Is there a way to log the Actual bind params passed in ?

      Matthew Marum May 6, 2017 at 11:26 am

      The majority of executed queries will use prepared statements but not all. We tried to update the code in key areas that would maximize the usage of prepared statements at runtime.

      We haven’t added any configuration settings to control this behavior. What sorts of config options would you want to see?

      I’d have to go check what the gets logged at DEBUG or INFO level in the sugarcrm.log for Sugar 7.9.


        thank you, Matt.

        1. okay. Is there a property (or) field on SugarQuery that would tell if it is “prepared” or “not”.

        2 & 3. Sometimes when we need to debug Queries generated, we either need the raw SQL (or) need the bind params that were passed in.

        Hence, either a configuration to turn off prepared Statements generation to print the raw SQL (or) a method to print the bind param values in Log would help.

          Matthew Marum May 8, 2017 at 3:46 pm

          There is no property on SugarQuery because all SQL queries generated using SugarQuery will used prepared statements.

          If you enable INFO or DEBUG in log, you’ll see prepared statement and parameters in the log. That should be enough to recreate raw statement.

          For example,

          on May 8 15:32:11 2017 [43527][1][INFO] Query: SELECT ea.email_address, ea.email_address_caps, ea.invalid_email, ea.opt_out, ea.date_created, ea.date_modified,
, ear.email_address_id, ear.bean_id, ear.bean_module, ear.primary_address, ear.reply_to_address, ear.deleted
          FROM email_addresses ea LEFT JOIN email_addr_bean_rel ear ON = ear.email_address_id
          WHERE ear.bean_module = ?
          AND ear.bean_id = ?
          AND ear.deleted = 0
          ORDER BY ear.reply_to_address, ear.primary_address DESC
          Params: [“Contacts”,”33f5360a-2385-11e7-b50a-a0999b0867bb”]

    Ivan Tabarino June 27, 2017 at 5:16 am

    I would like to know which one is the recommended form to create new custom queries on Sugar 7.9. Would be better to call directly Doctrine/QueryBuilder or SugarQuery?

    Thank you.

Trackbacks and Pingbacks:

  1. Developer resources for the newly released Sugar 7.9 « Sugar Developer Blog – SugarCRM - May 16, 2017

    […] You may have seen that that Sugar 7.9 has now been released! We have moved the Quotes module and Reports list view to Sidecar framework. We’ve also made plenty of platform enhancements such as adding prepared statements support. […]