Database security is the cornerstone of any robust Joomla extension. When building custom components or modules, you frequently need to filter results based on user input—often using the LIKE operator in SQL. However, if handled incorrectly, these queries become a primary vector for SQL injection attacks. In this guide, we will explore the common pitfalls of using JFactory::getDbo() and setQuery() and show you how to implement bulletproof security in both legacy Joomla 3.x environments and modern Joomla 4/5 installations.

The Risks of Raw SQL in Joomla

Many developers, especially those transitioning from procedural PHP, make the mistake of concatenating user input directly into their SQL strings. While it might seem functional, this practice is extremely dangerous. Consider the following common (but vulnerable) code snippet:

$db = JFactory::getDBO();
$searchP = JRequest::getVar('key');
$sql = "SELECT name FROM people WHERE name LIKE " . "'%" . $searchP . "%'";

$db->setQuery($sql);
$fileR = $db->loadObjectList();

Why This Code Is Vulnerable

You might wonder if setQuery() would throw an error if a malicious actor passed two SQL statements in a single string. The answer depends on your database driver and configuration, but you should never rely on the database to block multi-query execution as a security feature.

If a user passes a value like a'; DROP TABLE people; # into the key parameter, the resulting query becomes:

SELECT name FROM people WHERE name LIKE '%a'; DROP TABLE people; #%'

In many environments, the database engine will execute the first statement, then immediately execute the second, effectively deleting your entire table. Even if multi-queries are disabled, an attacker can still use UNION SELECT statements to exfiltrate sensitive data from other tables, such as your #__users table.

Securing LIKE Queries in Joomla 3.x

In Joomla 3.x, the standard way to interact with the database is via the JDatabaseQuery object. To prevent injection, you must ensure that every piece of user data is properly escaped and quoted.

Special attention must be paid to LIKE clauses. These require more than just standard escaping because the % and _ characters act as wildcards. If a user searches for a literal %, they could potentially trigger a Denial of Service (DoS) attack by forcing the database to perform an intensive full-table scan.

The Correct Escaping Pattern

To secure a LIKE query in legacy Joomla versions, use the escape() and quote() methods correctly:

$db = JFactory::getDBO();
$query = $db->getQuery(true)
            ->select($db->qn('name'))
            ->from($db->qn('people'))
            ->where($db->qn('name') . ' LIKE ' . $db->q('%' . $db->escape($searchP, true) . '%', false));
$db->setQuery($query);

Breaking Down the Code:

  1. $db->qn() (or quoteName()): This wraps your table and column names in backticks (e.g., `name`). This prevents conflicts with SQL reserved words.
  2. $db->escape($searchP, true): The second parameter true is vital. It tells Joomla to escape the wildcards (% and _) within the user's input, ensuring they are treated as literal characters.
  3. $db->q() (or quote()): This wraps the final string in single quotes. The second parameter false is used here to prevent double-escaping, as we have already manually escaped the string inside the function call.

Modern Approach: Prepared Statements in Joomla 4 and 5

With the release of Joomla 4 and 5, the CMS introduced support for prepared statements. This is the gold standard for database security. Instead of manually escaping strings, you use placeholders that the database engine fills later, ensuring that user input is never interpreted as SQL logic.

In modern Joomla development, the bind() method is used to map variables to these placeholders.

// Declare the search value wrapped in wildcard characters first
$searchLike = "%" . $searchP . "%"; 

$db = 
$query = $db->getQuery(true)
            ->select($db->qn('name'))
            ->from($db->qn('people'))
            ->where($db->qn('name') . ' LIKE :search')
            ->bind(':search', $searchLike);
$db->setQuery($query);
$results = $db->loadObjectList();

Using prepared statements removes the need for manual escaping and quoting entirely. The database driver handles the sanitization internally, making your code cleaner and significantly more secure.

Common Misconceptions About Query Security

Does setLimit() Prevent Injections?

You might think that adding ->setLimit('1') to your query object would act as a safety net. Unfortunately, this is not a security feature. An injection attack occurs when the structure of the SQL query itself is modified. If an attacker successfully injects a DROP TABLE or UPDATE statement, the limit instruction is either ignored (because it's part of the first query) or bypassed entirely by the injected logic.

JFactory vs. Modern Factory

While JFactory::getDbo() still works in many versions for backward compatibility, modern Joomla (4.x and 5.x) encourages using the Dependency Injection Container or the Factory class:

use Joomla\CMS\Factory;
$db = Factory::getContainer()->get('DatabaseInterface');

Frequently Asked Questions

Why do I need to escape the % character in a LIKE query?

If you don't escape %, a user could enter just a percent sign in your search box. The database would then return every single row in the table. On a site with millions of records, this can crash your database server or cause extreme latency, leading to a Denial of Service.

Is JRequest still safe to use for getting input?

No. JRequest is deprecated and has been for a long time. You should use the modern JInput (or simply Input in modern Joomla) to retrieve user data, which provides better filtering options:

$input = Factory::getApplication()->input;
$searchP = $input->get('key', '', 'STRING');

Wrapping Up

Securing your Joomla site starts with how you handle data. When working with LIKE conditions, remember these three golden rules: 1. Never concatenate variables directly into SQL strings. 2. In Joomla 3.x, use $db->escape($var, true) combined with $db->quote() to handle wildcards safely. 3. In Joomla 4 and 5, always prefer Prepared Statements using the bind() method.

By following these patterns, you protect your users' data and ensure your Joomla extensions remain secure against modern web threats.