CodeIgniter 4 Data Access: Using Models and Query Builder to Enforce SQL Injection and Mass-Assignment Boundaries

A recurring CodeIgniter 4 security problem is not a missing framework feature but a blurred boundary: user payloads reach the database either through string-concatenated SQL or through model fields that were never intended to be writable. The useful takeaway is that the framework already provides the right default—Query Builder parameter binding plus a Model $allowedFields whitelist—and the engineering decision is to route all persistence through that path rather than around it.

Requirements

Before choosing a data-access pattern, state the constraints explicitly:

• User-supplied values must never be interpolated into SQL strings.
• Request payloads must not be able to write to sensitive database columns such as role, is_admin, or internal status flags.
• Generated SQL should remain inspectable in staging so that binding behavior can be verified.
• The pattern must work with CodeIgniter 4's PDO layer without database-specific escape hacks.

These requirements assume CodeIgniter 4.x. CodeIgniter 3 uses a different Active Record syntax and does not offer the same Model $allowedFields enforcement, so the patterns below should not be ported without checking the major version.

The smallest suitable design

The smallest architecture that satisfies the requirements is a set of Model classes that own persistence, with controllers passing request data to model methods instead of building SQL.

Model with an explicit column whitelist

Declare $allowedFields on every model. This array acts as a mass-assignment guard: when you call save() or insert() with an array, only the listed columns are written.

namespace App\Models;

use CodeIgniter\Model;

class PostModel extends Model
{
    protected $table      = 'posts';
    protected $primaryKey = 'id';

    // Only these columns may be written from request data.
    protected $allowedFields = ['title', 'body', 'status', 'author_id'];
}

If a column is absent from $allowedFields, the builder silently ignores it rather than writing to the database. That silence is intentional: it prevents an attacker from adding is_admin=1 to a form payload and having it persist.

Controller usage

In the controller, pass the validated request array directly to the model. The Query Builder methods where(), insert(), and update() bind values through PDO prepared statements, so the values are escaped automatically.

$data = $this->request->getPost();

// Mass-assignment is limited to $allowedFields.
$model->save($data);

No string concatenation is required for values. The builder generates placeholders and sends the user data separately from the SQL statement.

Trust and data boundaries

The central distinction is between values and identifiers.

Values: safe as bound parameters

User input that supplies a value—a title, a search term, a numeric ID—is safe when passed as a method argument. The builder treats it as data, not as SQL syntax.

$posts = $model->where('status', $this->request->getGet('status'))
               ->findAll();

Even if the query string contains ' OR '1'='1, the database receives it as a literal string to compare against the status column, not as executable SQL.

Identifiers: whitelist only

Table and column names are identifiers. The builder escapes them, but it does not validate their intent. If user input controls an identifier, an attacker may be able to reference unintended columns or tables, or cause confusing runtime errors.

// Unsafe: user controls the column list.
$columns = $this->request->getGet('columns');
$model->select($columns);

Instead, map user choices to a server-side whitelist:

$allowedSorts = ['title' => 'title', 'created_at' => 'created_at'];
$sort = $this->request->getGet('sort');
$column = $allowedSorts[$sort] ?? 'created_at';

$model->orderBy($column, 'desc');

The same rule applies to table names if they are ever dynamic: they must come from application logic, not from a request.

Operational checks

Binding behavior is easy to claim and easy to break during refactoring. Verify it in staging. Run these checks in a staging controller or CLI tinker session, never against production data.

Inspect generated SQL

Enable query logging in your staging database configuration and use the Debug Toolbar, or call $db->getLastQuery() after a builder operation, to confirm that user-supplied metacharacters appear as bound placeholders rather than inlined literals.

$model->where('title', "O'Reilly")->findAll();
echo $this->db->getLastQuery();

The practical check is to submit test inputs containing SQL metacharacters and confirm the generated SQL contains placeholders such as ? instead of the raw input string.

Audit allowedFields against the schema

Compare each model's $allowedFields list against the actual database schema. Any column present in the table but absent from the whitelist is safe from mass-assignment; any column present in the whitelist but sensitive in the schema is a risk.

Failure modes

Pattern	Risk
$model->save($data) with $allowedFields	Low: mass-assignment blocked, values bound
$db->query("... '" . $input . "'")	High: direct SQL injection
$model->select($userInput)	Medium: identifier injection or silent typos

Bypassing the builder

The most common failure is calling $db->query() with string concatenation. This defeats prepared statements and reintroduces injection risk. If a raw query is unavoidable, pass user values as a bound parameter array:

$this->db->query('SELECT * FROM posts WHERE title = ?', [$title]);

User-controlled identifiers

Passing request data into select(), order_by(), or join() without whitelisting can expose unintended columns or generate database errors that are hard to diagnose.

Silent schema mismatches

Automatic identifier escaping can hide column-name typos until runtime. A misspelled column in $allowedFields or in a builder call may surface only as a generic database error, so schema audits belong in the release checklist.

Conditions that would change the design

Revisit this architecture when:

• Queries require database-specific features such as recursive CTEs or advanced JSON operators that the Query Builder does not express cleanly. In that case, isolate the hand-written SQL behind a repository or model method and continue using bound parameter arrays.
• Profiling shows the builder emits inefficient SQL for a hot path. Replace only that path with a prepared statement, and re-run the binding verification afterward.
• Authorization logic becomes complex. The builder prevents SQL injection, but it does not enforce who may read or write a row. Access-control checks remain a separate concern in the service or controller layer.

Limitations and practical verification

Query Builder parameter binding and $allowedFields reduce injection and mass-assignment risk, but they are not a complete security boundary. Authorization must be checked explicitly, and identifier whitelisting must be maintained by hand.

A practical way to check the result is to run a staging smoke test: submit a form payload containing ' OR '1'='1 in a text field and an extra key such as is_admin=1, then inspect $db->getLastQuery() and the affected row. The metacharacters should appear as bound placeholders, and the sensitive column should remain unchanged.