CodeIgniter 4 Query Builder: Architectural Trade-offs and Safety Patterns

Requirements and the Smallest Suitable Design

Most applications need a data-access layer that is safe, testable, and easy to change. CodeIgniter 4's BaseBuilder provides a fluent interface for SELECT, INSERT, UPDATE, and DELETE with automatic escaping of values passed to where(), set(), insert(), and update(). However, identifiers (table and column names) are not automatically escaped unless protectIdentifiers=true is set and the driver supports it.

The smallest suitable design wraps BaseBuilder in repository classes that expose only domain-specific methods — for example, findActiveUsersByRole(int $roleId) — and never leak the builder instance to callers. This prevents callers from appending unvalidated where() clauses or raw SQL fragments.

Trust Boundaries: Where Validation Belongs

Any value originating from an HTTP request (query string, body, headers) must be validated before it reaches the builder. The builder guarantees escaping of bound values, but it does not guarantee structural safety of identifiers or raw expressions passed to select(), join(), where(), having(), or orderBy().

A practical trust boundary looks like this:

• Controller receives request → validates type, range, allow-list.
• Controller calls repository method with validated primitives.
• Repository uses the builder with only those validated values.

Never pass user-supplied strings directly to orderBy() or where() without an allow-list.

Operational Checks: Logging and Static Analysis

Enable SQL logging in staging

Set CI_DEBUG_SQL=true in your .env (or Config\Database) for non-production environments. This logs the generated SQL with bound parameters. Run the application through realistic scenarios and inspect the logs.

# .env (staging only)
CI_DEBUG_SQL=true

Risk: Logs may contain sensitive data; restrict access and purge regularly.

Static analysis

Add PHPStan with the CodeIgniter extension to your CI pipeline:

composer require --dev phpstan/phpstan codeigniter4/phpstan-codeigniter
./vendor/bin/phpstan analyse src --level=5

Configure a rule (or custom regex) that flags direct use of raw SQL methods (query(), escapeString()) outside repository classes.

Pipeline grep for unsafe patterns

Add a step that searches for string concatenation inside where() calls:

grep -r "->where(" src/ | grep -v "where(\"\"\"" | grep -v "where(\$"

This catches cases where a variable is interpolated into the condition string instead of using parameter binding.

Failure Modes and How to Detect Them

1. SQL injection via unescaped identifiers — when protectIdentifiers=false (default in some drivers) and table/column names come from user input.
   • Detection: run php spark db:table <table> to confirm the builder's protectIdentifiers behavior matches your driver (MySQLi, Postgre, SQLSRV).
2. Logic errors from missing grouping — chained where() calls produce unintended AND/OR precedence because groupStart()/groupEnd() were omitted.
   • Detection: review CI_DEBUG_SQL output for unexpected parentheses.
3. Performance regression — builder fetches all columns via select('*') on wide tables under high concurrency.
   • Detection: load-test a critical query with k6 or wrk comparing builder vs raw SQL; ensure builder overhead (<2 ms per query) is acceptable for your throughput target.

Conditions That Change the Design

• Adopting an ORM (e.g., Doctrine) — switch the repository layer to EntityManager; keep the same domain method signatures.
• Multi-tenant row-level security — move tenant filtering to a global scope via BaseBuilder::addGlobalScope() or a database view, so repositories don't repeat tenant checks.
• Complex CTEs or window functions — bypass the builder for those queries and use raw SQL in a dedicated read-model repository.

Concrete Example: Safe Sorting in a Repository

Suppose an API endpoint allows clients to sort a user list by a column name. The controller validates the column against an allow-list before calling the repository.

// app/Controllers/Users.php
public function index()
{
    $allowed = ['id', 'email', 'created_at', 'last_login'];
    $sort = $this->request->getGet('sort') ?? 'created_at';
    $direction = strtoupper($this->request->getGet('direction') ?? 'DESC');
    
    if (!in_array($sort, $allowed, true)) {
        $sort = 'created_at';
    }
    if (!in_array($direction, ['ASC', 'DESC'], true)) {
        $direction = 'DESC';
    }
    
    $users = $this->userRepository->findActiveSorted($sort, $direction);
    return $this->respond($users);
}

// app/Repositories/UserRepository.php
class UserRepository
{
    protected $db;
    public function __construct() {
        $this->db = \Config\Database::connect();
    }
    
    public function findActiveSorted(string $column, string $direction): array
    {
        $builder = $this->db->table('users');
        // Ensure identifiers are quoted for the current driver
        $builder->protectIdentifiers = true;
        
        return $builder->select('id, email, created_at, last_login')
            ->where('status', 'active')
            ->orderBy($column, $direction)
            ->get()
            ->getResultArray();
    }
}

Key points:

• Controller validates $sort against an allow-list.
• Repository sets protectIdentifiers = true (driver-dependent).
• Only the validated column name reaches orderBy().

Verification Checklist

1. Create a test repository method that accepts a user-controlled sort column; verify that protectIdentifiers=true and an allow-list are enforced before calling orderBy().
2. Run php spark db:table <table> to confirm the builder's protectIdentifiers behavior matches the driver in your environment.
3. Add a PHPStan rule (or custom regex) that flags any call to $builder->where($rawString) where $rawString is not a literal or a constant.
4. Load-test a critical query with k6 or wrk comparing builder vs raw SQL; ensure the builder overhead (<2 ms per query) is acceptable for your throughput target.
5. Review the CI_DEBUG_SQL log for any occurrence of unparameterized literals (e.g., WHERE status = "active" instead of WHERE status = ?) — these indicate missed bindings.

Limitations and Practical Verification

The builder's getCompiledSelect() returns SQL with ? placeholders; the actual binding happens in the driver. Logging the compiled SQL alone does not reveal final values unless you also log the bindings array. Additionally, set() with an associative array for UPDATE/INSERT escapes values but does not validate column existence; a typo in a column name silently becomes a new column in some drivers (MySQL strict mode off).

To verify safety in your environment, combine the static analysis rules with a staging deployment that has CI_DEBUG_SQL=true and run your automated test suite. Inspect the logs for any unparameterized literals and confirm that all user-supplied identifiers pass through an allow-list.