Use of prepared statements in Sugar 7.9

What are Prepared Statements?

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.

DBManager

SugarQuery

SugarBean

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()

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

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);

$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);

$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

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

$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);

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