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.
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_DeleteSugarQuery_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:
After:$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:
After:$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
When updating records with known IDs or a set of records with simple filtering criteria, then DBManager can be used:Before:After:$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:
After:$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'));
