Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 37 ยท Development and Security

The Database API and Input Filtering

Write safe Joomla code: the query builder with bound parameters, table prefixes, quoteName, getInt and getCmd input filters, and output escaping, with PHP stand-ins.

Most serious vulnerabilities in web software are not exotic. They come from two ordinary mistakes: putting untrusted input straight into a database query (SQL injection) and printing untrusted data straight into a page (cross-site scripting, XSS). Joomla gives you tools that make the safe way the easy way: a query builder with bound parameters, an input filter and output escaping. This lesson shows how to use them.

Joomla cannot run on this site. The Joomla snippets are read-only, and the TryIt blocks are PHP stand-ins: a tiny query builder using PDO with SQLite, and filters that behave like the real ones.

The database object

In Joomla 4 and 5 you obtain the database through the dependency container:

PHP
use Joomla\CMS\Factory;
use Joomla\Database\DatabaseInterface;
use Joomla\Database\ParameterType;

$db = Factory::getContainer()->get(DatabaseInterface::class);

Inside a model that extends BaseDatabaseModel the same object is available as $this->getDatabase(). (Older code uses Factory::getDbo(), which is deprecated.)

Building a query

The query builder assembles SQL from parts so that identifiers and values are quoted correctly for your database engine (MySQL, MariaDB or PostgreSQL):

PHP
$catid = 7;

$query = $db->getQuery(true)
    ->select($db->quoteName(['id', 'title', 'created']))
    ->from($db->quoteName('#__content'))
    ->where($db->quoteName('state') . ' = 1')
    ->where($db->quoteName('catid') . ' = :catid')
    ->bind(':catid', $catid, ParameterType::INTEGER)
    ->order($db->quoteName('created') . ' DESC')
    ->setLimit(10);

$db->setQuery($query);
$rows = $db->loadObjectList();

Four things to notice:

  1. #__ is the table prefix placeholder. Joomla replaces it with your configured prefix (such as abc12_). Always write #__content, never the literal name.
  2. quoteName() quotes column and table names (backticks in MySQL). Use it for every identifier.
  3. :catid plus bind() is a bound parameter. The database receives the SQL and the value separately, so the value can never change the structure of the query. Pass the type (ParameterType::INTEGER, STRING, BOOLEAN, NULL...).
  4. Result methods: loadObjectList() (all rows as objects), loadObject() (first row), loadResult() (first column of the first row), loadColumn() (one column as an array), loadAssocList().

For writes use insert(), update() and delete() builders, then $db->execute(). After an insert, $db->insertid() returns the new key. Wrap several related writes in a transaction ($db->transactionStart(), transactionCommit(), transactionRollback()).

The classic mistake

PHP
// DO NOT DO THIS
$query->where('title = ' . $db->quote($_GET['t']));    // better, but still avoid raw superglobals
$query->where("catid = " . $_GET['catid']);            // injectable: ?catid=1 OR 1=1

quote() escapes a value, so the first line is not directly injectable, but bound parameters are cleaner, work for every type and are what Joomla's core uses. The second line is a vulnerability: any string the visitor sends becomes part of the SQL.

PHP Injection versus a bound parameter

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The unsafe query returns the secret row as well, because the input became part of the SQL. The bound version treats the value as data. (Joomla's getInt() would turn the input into an integer even before binding; using both is good practice.)

Reading input safely

User input arrives through the Input object, never directly from $_GET or $_POST:

PHP
$app   = Factory::getApplication();
$input = $app->getInput();

$id    = $input->getInt('id', 0);           // integer, default 0
$limit = $input->getUint('limit', 20);      // unsigned integer
$name  = $input->getString('name', '');     // cleaned string
$task  = $input->getCmd('task', 'display'); // letters, digits, dot, dash, underscore
$word  = $input->getWord('view', '');       // letters and underscore only
$flag  = $input->getBool('active', false);

Each getter applies a filter. Frequently used ones: INT, UINT, FLOAT, BOOL, WORD, ALNUM, CMD (command names), STRING, HTML (allows safe markup), PATH, USERNAME, and RAW (no filtering at all: use only when you filter by yourself). Choose the narrowest filter: an ID is getInt, a view name getCmd or getWord, never a plain string.

For forms, $input->post->get(...) and $input->get->get(...) read specific superglobal sources, and $input->get('jform', [], 'array') reads form arrays. Filtering is about shape; it does not replace validation (is this email valid? does the category exist? may the user do this?) and authorization.

PHP Filters like getInt, getCmd and getWord

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

These simplified filters illustrate what the real ones do: remove everything that is not allowed. Joomla's implementations are more complete; always use the real ones in extensions.

Escaping output

Filtering input is not enough: data may be stored by another path and shown later. Whatever you print into HTML must be escaped for HTML:

PHP
<h1><?php echo $this->escape($this->item->title); ?></h1>
<a href="<?php echo $this->escape($url); ?>">...</a>

In a layout, $this->escape() uses htmlspecialchars with the right flags. Outside views use htmlspecialchars($value, ENT_QUOTES, 'UTF-8'). For values inside JavaScript or URLs, use the encoders for those contexts (json_encode with flags for JS, rawurlencode for URL parts). If you really must print HTML from a user (for example a rich-text field), run it through the HTML filter (InputFilter) or rely on the editor's filtering by user group, which Joomla configures in Content > Options > Text Filters.

PHP Escaping stops stored XSS

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The escaped version is displayed as literal text by a browser instead of running the handler.

The three-step habit

  1. Filter on the way in (the narrowest getter).
  2. Bind on the way to the database (never concatenate).
  3. Escape on the way out (every printed value).

Add a fourth for actions that change data: check the token and the permission (see Component Structure and MVC and Permissions and Inheritance).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why use bound parameters in a Joomla query?

    Bound values are data, not SQL, which prevents injection.
  2. 2. What does #__ stand for in a table name?

    Joomla replaces #__ with the prefix chosen at install time.
  3. 3. Which getter fits a numeric article ID best?

    Choose the narrowest filter: an ID is an integer.
  4. 4. Where should you escape a value?

    Escaping belongs to the output context: HTML, JS, URL and so on.

Summary

  • Get the database from the container; build queries with the query builder, quoteName and the #__ prefix.
  • Bind every variable with bind() and a parameter type; never concatenate input into SQL.
  • Read input through the Input object with the narrowest filter (getInt, getCmd, getWord).
  • Escape output with $this->escape() or htmlspecialchars.
  • Filter, bind, escape, and add token and permission checks for changes.

Next: Web Services API and CLI