Skip navigation
All Places > Developer > Blog > 2017 > April
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.

 

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

 

 

SugarCRM recently released versions 4.1.0 and 5.0.0 of SugarCRM Mobile and version 1.2.0 of the Mobile Application Configurator Service (MACS). These releases represent a significant technology evolution for our Mobile products that is worthy of a summary of the benefits to the Sugar Developer community.

 

Even more exciting, we are also getting tantalizingly close to general availability for the SugarCRM Mobile SDK!

 

Enhanced Offline mode in SugarCRM Mobile 4.1.0

 

Who likes waiting? Nobody! Well our previous offline storage would make the user wait after login while the app would download records so they were available offline. We did not want users waiting too long so we limited the offline storage to a modest 100 records per module. This allowed the Mobile app to be available to users quickly but not instantly.

 

This changed with SugarCRM Mobile 4.1.0 because we improved how offline record storage works CRM records are now (asynchronously) downloaded into offline storage behind the scenes whenever the Mobile app is in use. This allows the user to be productive with the Mobile app immediately after login even while offline storage is being loaded up. We also increased the offline storage limit to 1000+ records per module because the time taken to download thousands of records over a slow cellular network was no longer a concern.

 

Since we drastically increased the download size, we also added an option to download records over Wifi only. That should help anyone worried about blowing up their data plans!

 

New SugarCRM Mobile UX in 5.0.0!



 

As part of the SugarCRM Mobile 5.0.0 release, we introduced a redesigned user experience. We reduced the number of clicks needed to reach information, for example, by redesigning the record details view to show more information on the initial screen in a Details tab. We also improved navigation and re-ordered tabs and elements in the user interface based upon customer feedback.

 

From administrative perspective, we have added AppConfig standard which makes it easier to manage the app using Enterprise Mobile Management (EMM) solutions. This included the ability to preset Sugar instance URLs to allow for easier deployment of the Mobile app to end users.

 

Mobile 5.0.0 also simplified the login page by moving configuration settings into a separate screen and added support for Apple Touch ID among other changes.

 

Updates to Mobile Application Configuration Service (MACS)

MACS allows Sugar Partners and Customers to create re-branded binaries of the SugarCRM Mobile app that gives them full control over how they distribute and deploy the mobile application.

 

Every time SugarCRM releases a new version of our Mobile app, it is also added to MACS. We also recently added the ability to cancel a build as well as delete old ones. This will help keep your list of built Apps pristine as newer versions of Mobile app are added to MACS.

 

More details on Mobile SDK!

 

We have talked about the Mobile SDK before and I am excited to say that we are making great progress. The SugarCRM Mobile SDK is currently in a limited beta with a few key Sugar Partners and customers and we look forward to making it generally available very soon.

 

The first generally available release of the Mobile SDK will be based on SugarCRM Mobile 5.0.0.

 



 

Some resources you can expect when the Mobile SDK is released is a Getting Started Guide, detailed Mobile API documentation, a Mobile developer community, and eventually a Mobile Styleguide. You will be able to reach all these resources from the same Mobile Tools portal that you use for Mobile Application Configuration Service (MACS).

 

The Mobile Tools portal is available to Sugar Partners and Sugar Enterprise customers. So if that means YOU then watch that space! This will be where you will get your hands on the SDK.