Use of prepared statements in Sugar 7.9

Matthew Marum —  April 17, 2017 — 4 Comments

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.

db

DBManager

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

SugarQuery

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

SugarBean

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.

SugarQuery_Builder_Delete
SugarQuery_Builder_Insert
SugarQuery_Builder_Update
SugarQuery_Builder_Literal
SugarQuery_Compiler
SugarQuery::joinRaw()
SugarQuery::compileSql()
DBManager::delete()
DBManager::retrieve()
DBManager::insertSQL()
DBManager::updateSQL()
DBManager::deleteSQL()
DBManager::retrieveSQL()
DBManager::preparedQuery()
DBManager::pQuery()
DBManager::prepareQuery()
DBManager::prepareTypeData()
DBManager::prepareStatement()

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

SugarQuery::compileSql()

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:

['ec2f4abb-b6b9-3d49-0382-5730e67c116c']

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.

Before:

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

After:

$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.

Before:

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

After:

$builder = $db->getConnection()->createQueryBuilder();
$builder->select('*')->from('table');
if ($status !== null) {
    $builder->where(
        'status = ' . $builder->createPositionalParameter($status))
    );
}
$builder->execute();

INSERT queries

INSERT queries can be easily performed using DBManager class.

Before:

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

After:

$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:

Before:

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

After:

$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.

Before:

$query = 'UPDATE table SET foo = "bar" WHERE foo = "foo" OR foo IS NULL';
$db->execute($query);

After:

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

Matthew Marum

Posts

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.

4 responses to Use of prepared statements in Sugar 7.9

  1. 

    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.

  2. 

    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.

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