CodeIgniter 4 Query Builder: Safe Filtering Without Writing Raw SQL

The short answer

If you are building database queries in CodeIgniter 4 by concatenating strings, stop. The Query Builder class gives you a chainable API that escapes values automatically, works across supported database drivers, and lets you inspect the exact SQL it generates. For the common case — filtered SELECTs with joins and pagination — it is both safer and more maintainable than hand-written SQL. The main thing to learn is where its escaping applies and where it silently does not.

How the mechanism works

The Query Builder does not execute anything as you call methods. Each call to where(), join(), or orderBy() stores fragments internally. Only a terminal method such as get(), insert(), or update() compiles those fragments into SQL and runs them. Values you pass through the structured methods are escaped or bound for the active driver, which is what protects you from SQL injection.

A typical setup in a controller (CodeIgniter 4.x, PHP 8.1+):

$db = \Config\Database::connect();
$builder = $db->table('orders');

$builder->select('orders.id, orders.total, users.email')
        ->join('users', 'users.id = orders.user_id', 'left')
        ->where('orders.status', $status)   // escaped automatically
        ->where('orders.total >', 100)
        ->orderBy('orders.created_at', 'DESC')
        ->limit(20);

$result = $builder->get();
$rows   = $result->getResultArray(); // or getResult() for objects

Here $status might come straight from a request — for example $this->request->getGet('status'). Because it is passed as a value to where(), the builder escapes it. Passing the string '; DROP TABLE orders; -- produces a harmless quoted literal, not broken syntax.

To see what was actually sent to the database, run this after the query:

echo $db->getLastQuery();

That returns the compiled SQL string and is the fastest way to confirm escaping and join structure during development. You can also call $builder->getCompiledSelect() before execution to preview the SQL without running it.

Bulk filtering with arrays

When a search form produces several optional filters, the associative-array form of where() keeps the code flat:

$filters = [];
if ($status !== '')  { $filters['status']   = $status; }
if ($userId !== null){ $filters['user_id'] = (int) $userId; }

if ($filters !== []) {
    $builder->where($filters); // ANDed together, all values escaped
}

This is the pattern to prefer over building a raw WHERE string by hand.

Where the escaping stops

The builder's protection applies to values, not to everything you hand it. The risky cases:

• Raw-string where clauses. where("status = '$status'") interpolates the variable yourself and bypasses escaping entirely. If you must write a custom condition, use bindings: where('status = ?', [$status]).
• Identifiers. Column and table names are not value-escaped the same way. Never let user input choose a column name in orderBy() or select() without whitelisting it against a fixed list of allowed columns first.
• set() with raw values. In inserts and updates, pass values through the array form of set() rather than embedding them in strings.

Common mistakes and limits

Calling get() too early. get() is terminal. Any where() chained after it applies to a new query, not the one you just ran. Build all conditions first, then execute.

Reusing a builder instance. After get(), the builder resets its query state by default. If you need the same base query twice (for example, a count and a page of results), call $builder->getCompiledSelect() first or get a fresh builder from $db->table().

Complex subqueries. Deeply nested subqueries, window functions, and CTEs are awkward or unsupported in the builder. For those, a raw query with bindings — $db->query('SELECT ... WHERE id = ?', [$id]) — is clearer and often produces better SQL than contorted builder chains. The builder is a tool for the 80% case, not a rule.

Driver differences. The builder abstracts quoting and LIMIT syntax across MySQL, PostgreSQL, SQLite, and SQL Server, but behavior around things like case-insensitive matching still depends on the underlying database. Verify against the driver you actually deploy.

How to verify it works

1. Enable the debug toolbar in development (app/Config/Boot/development.php and a .env with CI_ENVIRONMENT = development) and check the Database tab to see every executed query.
2. Deliberately pass a hostile value like ' OR '1'='1 as a filter and confirm via getLastQuery() that it appears as a quoted literal and returns no rows.
3. Run the same query through EXPLAIN in your database client if performance matters, especially for joins — the builder will not add indexes for you.

These checks assume CodeIgniter 4.x; method names like getLastQuery() and getCompiledSelect() are stable in the 4.x line, but confirm against your installed version's user guide before relying on edge-case behavior.