Prepared Statements in Joomla 4

Joomla 4 will move towards using prepared statements for all database queries. PHP 5.3 introduced the concept of prepared statements. Joomla 4 will start taking advantage of them. The main advantage of prepared statements is to reduce the exposure of a code base to SQL Injection attacks by sending the query and the data separately.

You can imagine PHP Sending the query like the following:

Query: SELECT foobar FROM bar WHERE foo = ?
Data: [? = 'bar']

The database itself will do the hard work of quoting the statement. Because the database is responsible for the quoting, it reduces the complexity of Joomla's code base and also reduces the chance of coding errors introducing any bugs.

How to Implement Prepared Statements

Implementing prepared statements in Joomla is simple and cross-platform. For example,

Original Query

$query = $this->db->getQuery(true)
 ->select($this->db->quoteName(array('id', 'password')))
 ->from($this->db->quoteName('#__users'))
 ->where($this->db->quoteName('username') . '=' . $this->db->quote($credentials['username']));

Prepared Statement Query

$query = $this->db->getQuery(true)
 ->select($this->db->quoteName(array('id', 'password')))
 ->from($this->db->quoteName('#__users'))
 ->where($this->db->quoteName('username') . ' = :username')
 ->bind(':username', $credentials['username']);

While adding another line to the query, you no longer have to quote the data.

Query

Query chaining refers to a method of connecting a number of methods, one after the other, with each method returning an object that can support the next method, improving readability and simplifying code.

To obtain a new instance of the JDatabaseQuery class we use the JDatabaseDriver getQuery method:

$db = JFactory::getDbo();

$query = $db->getQuery(true);

Inserting Record

The JDatabaseQuery class provides a number of methods for building insert queries, the most common being insert, columns and values.

// Get a db connection.
$db = JFactory::getDbo();

// Create a new query object.
$query = $db->getQuery(true);

// Insert columns.
$columns = array('user_id', 'profile_key', 'profile_value', 'ordering');

// Prepare the insert query.
$query
 ->insert($db->quoteName('#__user_profiles'))
 ->columns($db->quoteName($columns))
 ->values(':user_id, :profile_key, :profile_value, :ordering');

// Bind values
$query
 ->bind(':user_id', 1001, Joomla\Database\ParameterType::INTEGER)
 ->bind(':profile_key', 'custom.message')
 ->bind(':profile_value', 'Inserting a record using insert()')
 ->bind(':ordering', 1, Joomla\Database\ParameterType::INTEGER);

// Set the query using our newly populated query object and execute it.
$db->setQuery($query);

$db->execute();

Using an Object

The JDatabaseDriver class provides a convenient method for saving an object directly to the database to add a record to a table without writing a single line of SQL.

// Create and populate an object.
$profile = new stdClass();
$profile->user_id = 1001;
$profile->profile_key='custom.message';
$profile->profile_value='Inserting a record using insertObject()';
$profile->ordering=1;

// Insert the object into the user profile table.
$result = JFactory::getDbo()->insertObject('#__user_profiles', $profile);

Here, you do not need to escape the table name. The insertObject method does this.

If you are simply inserting the next row in your table (i.e. the database generates a primary key value), you can specify the primary key column-name as the third parameter of the insertObject() method and the method will update the object with the newly generated primary key value.

For example,

$result = $dbconnect->insertObject('#__my_table', $object, 'primary_key');

After execution, $object->primary_key will be updated with the newly inserted row's primary key value.

Updating Record

The JDatabaseQuery class also provides methods for building update queries.

$db = JFactory::getDbo();

$query = $db->getQuery(true);

// Fields to update.
$fields = array(
 $db->quoteName('profile_value') . ' = :profile_value',
 $db->quoteName('ordering') . ' = :ordering'
);

// Conditions for which records should be updated.
$conditions = array(
 $db->quoteName('user_id') . ' = :user_id', 
 $db->quoteName('profile_key') . ' = :profile_key'
);

$query->update($db->quoteName('#__user_profiles'))->set($fields)->where($conditions);

$query
 ->bind(':profile_value', 'Updating custom message for user 1001.')
 ->bind(':ordering', 2, Joomla\Database\ParameterType::INTEGER)
 ->bind(':user_id', 42, Joomla\Database\ParameterType::INTEGER) 
 ->bind(':profile_key', 'custom.message');

$db->setQuery($query);

$result = $db->execute();

Using an Object

Like insertObject, the JDatabaseDriver class provides a convenience method for updating an object.

// Create an object for the record we are going to update.
$object = new stdClass();

// Must be a valid primary key value.
$object->id = 1;
$object->title = 'My Custom Record';
$object->description = 'A custom record being updated in the database.';

// Update their details in the users table using id as the primary key.
$result = JFactory::getDbo()->updateObject('#__custom_table', $object, 'id');

Just like insertObject, updateObject takes care of escaping table names. You need to ensure that the record already exists before attempting to update it, so you would probably add some kind of record check before executing the updateObject method.

Deleting Record

Finally, there is also a delete method to remove records from the database.

$db = JFactory::getDbo();

$query = $db->getQuery(true);

// delete all custom keys for user 1001.
$conditions = array(
 $db->quoteName('user_id') . ' = :user_id', 
 $db->quoteName('profile_key') . ' = :profile_key'
);

$query->delete($db->quoteName('#__user_profiles'));
$query->where($conditions);

$query
 ->bind(':user_id', 1001, Joomla\Database\ParameterType::INTEGER)
 ->bind(':profile_key', 'custom.%');

$db->setQuery($query);

$result = $db->execute();