CodeIgniter 4 Query Builder: Safe SQL Without the ORM Weight

Every PHP developer eventually faces the same tension: raw SQL strings are fast to write but easy to get wrong, while a full ORM can feel like overkill for a CRUD-heavy admin panel. CodeIgniter 4's Query Builder sits deliberately in the middle. It gives you a fluent, chainable API that escapes values automatically and translates your clauses into driver-specific SQL — so the same code runs against MySQL, PostgreSQL, or SQLite without rewriting.

The thesis of this piece is simple: for most application queries, the Query Builder is the right default in CodeIgniter 4, and raw SQL should be the exception you reach for deliberately, not the habit you fall into.

What the Builder Actually Protects You From

The headline feature is automatic escaping. When you pass a value through where() or set(), the builder binds it as a parameter rather than interpolating it into the SQL string. That means a search box submitting '; DROP TABLE users; -- becomes harmless bound data, not an injection vector.

There is an important caveat, though: escaping only applies to values passed through builder methods. If you concatenate a raw string into select() or build a where() clause by hand, you have stepped outside the guardrails. The builder also does not validate column or table names against your schema — a typo like where('emial', $x) compiles fine and only fails at runtime as a database error. Treat the builder as protection for values, not a spell-checker for identifiers.

A Worked Example: Filtered, Paginated Results

Here is a realistic controller method for an admin user list, filtering by an active flag and a search term from the query string. This assumes CodeIgniter 4.x installed via composer create-project codeigniter4/appstarter, with the database configured in .env:

<?php

namespace App\Controllers;

class Users extends BaseController
{
    public function index()
    {
        $db = \Config\Database::connect();
        $builder = $db->table('users');

        $builder->select('id, email, created_at')
                ->where('active', 1);

        $search = $this->request->getGet('q');
        if ($search !== null && $search !== '') {
            // The value is bound automatically — no manual escaping.
            $builder->like('email', $search);
        }

        $data = [
            'users' => $builder->paginate(10),
            'pager' => $builder->pager,
        ];

        return view('users/index', $data);
    }
}

Two things are worth noticing. First, paginate(10) handles the offset/limit math and hands you a pager object that renders view-ready links with $pager->links() — one call replaces the usual manual COUNT-plus-LIMIT dance. Second, the result implements Countable and IteratorAggregate, so you can loop it in a view or pass it straight to json_encode() for an API response.

To verify what actually ran, add echo $db->getLastQuery(); temporarily after the call (in development only). You should see the search term as a bound placeholder, not interpolated text. If you see your raw input sitting inside the SQL string, something bypassed the builder — find it and fix it.

Mixing in Raw SQL and Transactions Safely

Sometimes you need a subquery or a vendor-specific expression. The builder accommodates this without forcing an all-or-nothing choice: selectSubquery() embeds a builder-generated subquery, and $db->escape() quotes individual values when you must write a fragment by hand.

Transactions live at the connection level, not the builder level, which is genuinely useful. Wrapping $db->transStart() and $db->transComplete() around a mix of builder calls and raw $db->query() statements keeps everything atomic — the builder doesn't force you to pick one style per transaction.

The Trade-Offs Worth Knowing

The builder is not free. Three limitations matter in practice:

• Readability degrades with complexity. A query with five joins and conditional grouping often reads better as well-formatted raw SQL than as fifteen chained method calls. Past a certain complexity threshold, switch.
• Pagination counts can be slow. paginate() runs a COUNT(*) under the hood. On a large table filtering by an unindexed column, that count can dominate response time. Add an index on filtered columns, or benchmark against a manual COUNT + LIMIT approach on realistic data volume.
• CI3 migration is mechanical, not automatic. CodeIgniter 3's API differs — get_where() becomes where()->get(), and result methods changed names. Every call site needs a method-by-method update.

Make It Your Default, Then Verify

Reach for the Query Builder first, drop to raw SQL when the chain becomes unreadable or you need driver-specific features, and never concatenate user input into either. Your practical check: enable the debug toolbar or inspect getLastQuery() on a page that accepts user input, and confirm every dynamic value appears as a bound parameter. That five-minute audit tells you more about your injection exposure than any amount of code reading.