Controlling SQL Injection Risk in CodeIgniter with the Query Builder

When a controller builds SQL by gluing strings together, a single stray quote from a form field can turn a harmless SELECT into an injection payload. Escaping every variable by hand is tedious and easy to forget. The practical takeaway: CodeIgniter's Query Builder gives you a chainable, database-agnostic API that escapes values by default, but it is not a force field. Raw fragments and version differences still matter.

What the Query Builder Actually Does

The Query Builder is not an ORM. It does not map tables to objects or manage relationships. It is a fluent interface that collects method calls—select(), where(), join(), limit()—and compiles them into SQL for the configured driver. Because values passed to methods like where() and insert() are escaped automatically, the generated statement treats user input as data, not executable code.

Method chaining keeps the query definition in one readable block. You can build the same logical query for MySQL, PostgreSQL, or SQLite without rewriting dialect-specific syntax.

Worked Example: Filtering and Paginating Orders

Assume you need a report of recent orders, joined with the customer's email, filtered by status and date, and paginated. The example below uses CodeIgniter 4 conventions. In CodeIgniter 3, the builder is reached through $this->db directly rather than $this->db->table().

// app/Controllers/Reports.php (CodeIgniter 4)
// Requires a configured database connection in app/Config/Database.php

public function orders(string $status, string $startDate, int $perPage = 25, int $offset = 0)
{
    $builder = $this->db->table('orders');

    $builder->select('orders.id, orders.total, users.email')
        ->join('users', 'users.id = orders.user_id', 'left')
        ->where('orders.status', $status)
        ->where('orders.created_at >=', $startDate)
        ->limit($perPage, $offset);

    // Structural verification: preview SQL without executing
    $previewSql = $builder->getCompiledSelect();

    if (ENVIRONMENT === 'development') {
        echo $previewSql; // Inspect manually, then remove
    }

    // Execute only after the structure looks correct
    $results = $builder->get();

    return $this->response->setJSON($results->getResult());
}

Here, $status and $startDate arrive from request parameters. The Query Builder escapes them when it compiles the statement. The join() call specifies a left join so orders without a matching user row still appear. The limit() and offset() pair handles pagination.

Preview Before You Execute

getCompiledSelect() returns the SQL string without sending it to the database. Use it as a diagnostic: confirm that user-supplied values appear as escaped, quoted literals rather than raw text. If you see the string ' OR 1=1 injected directly into the SQL, something bypassed the escaping layer.

Where the Abstraction Leaks

The Query Builder protects values passed as parameters. It does not protect raw strings. Writing ->where("orders.status = '$status'") embeds the variable literally because the method receives a complete SQL fragment, not a value to escape. The same applies to orderBy(), like(), and groupBy() when given unvalidated strings.

Version drift adds friction. CodeIgniter 3 and CodeIgniter 4 differ in method naming, return types, and how the builder is instantiated. Code that chains fluently in CI4 may need adjustment in CI3. Check the version-specific documentation before porting queries.

Performance is the final trade-off. The abstraction occasionally produces less efficient SQL than a hand-optimized native statement, especially when database-specific window functions, common table expressions, or complex subqueries are involved. If a generated query runs slowly, compare it against a native query using $this->db->query() and measure the difference.

A Practical Verification Habit

Before shipping a new query, call getCompiledSelect() inside a development controller. Inspect the output for two things: user values are escaped, and the join and limit clauses match your intent. Once verified, remove the preview or guard it behind an environment check so it never prints in production.

Keep raw fragments out of where() unless you have validated and escaped them yourself. Treat the Query Builder as a safety net for values, not a substitute for input validation.