J4.x

Moving Joomla To Prepared Statements

From Joomla! Documentation

This page contains changes which are not marked for translation.
Other languages:
Deutsch • ‎English • ‎italiano • ‎português do Brasil
Joomla! 
≥ 4.0
version

In Joomla 4 you'll see the move of Joomla slowly across to using prepared statements for all our queries. This article aims to set out why and how we are doing this.

Motivation[edit]

PHP 5.3 introduced the concept of prepared statements. In Joomla 4 we're going to 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 

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

And the database itself will do the hard work of quoting your 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.

Implementing Prepared Statements through JDatabaseDriver[edit]

Implementing prepared statements in Joomla is very simple and is cross-platform. For example here's a simple Joomla 3.x query from Joomla's authentication plugin. 

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

To convert it to a prepared statement we will instead substitute the username for a named value and then bind our real data to the 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']);

As you can see whilst adding another line to the query we no longer have to quote the data - we let our mysql/postgresql database handle this for us giving us much easier to read and manage code.

Some functions in JDatabaseDriver will use prepared statements automatically. For example whereIn() and whereNotIn() will automatically use the values and add prepared statements to the query. 

$query = $this->db->getQuery(true)
	->select($this->db->quoteName(array('id, password')))
	->from($this->db->quoteName('#__users'))
	->whereIn($this->db->quoteName('id'), [ 1, 2, 3 ]);

This query will be converted to a sql prepared statement. 

SELECT 
  `id`, `password`
FROM
  `#__users`
WHERE
  `id` IN (
    :preparedArray1,
    :preparedArray2,
    :preparedArray3
  );

The placeholders :preparedArray1-3 will be substituted with 1, 2, and 3 on execution.

The following functions accept arrays to reduce function call overhead.

  • bind()
  • bindArray()
  • whereIn()
  • whereNotIn()

If possible you should use prepared statements.


Further Reading on Prepared Statements[edit]

Retrieved from "https://docs.joomla.org/index.php?title=J4.x:Moving_Joomla_To_Prepared_Statements&oldid=926730"