CodeIgniter 4 Query Builder: Safe Value Binding and Identifier Escaping

The Core Protection: Values Are Bound, Identifiers Are Not

CodeIgniter 4's Query Builder sends SQL structure with placeholders to the database driver, so values passed to where(), insert(), update(), and delete() are bound at execution time—not interpolated into the query string. This prevents SQL injection for values. However, table names, column names, ORDER BY directions, and raw SQL fragments are not automatically escaped. If those identifiers come from request input, you must handle them yourself.

Worked Example: Parameterized Filtering and Insertion

The following controller method demonstrates safe value binding for a search endpoint that filters orders by user ID and status, then logs the search. Run this in a controller with $this->db = \Config\Database::connect();.

public function searchOrders()
{
    $userId = $this->request->getGet('user_id');
    $status = $this->request->getGet('status');
    $term   = $this->request->getGet('term'); // free-text search

    $builder = $this->db->table('orders');

    // Values are bound as placeholders
    if ($userId !== null) {
        $builder->where('user_id', $userId);
    }
    if ($status !== null) {
        $builder->where('status', $status);
    }

    // like() does NOT escape % or _ — sanitize first
    if ($term !== null) {
        $safeTerm = $this->db->escapeLikeString($term);
        $builder->like('description', $safeTerm);
    }

    $orders = $builder->get()->getResultArray();

    // Logging the search — values bound, table name hard-coded
    $this->db->table('search_logs')->insert([
        'user_id'    => $userId,
        'query_term' => $term,
        'results'    => count($orders),
        'created_at' => date('Y-m-d H:i:s'),
    ]);

    return $this->response->setJSON($orders);
}

With $this->db->debug = true; (or the Debug Toolbar), the executed SQL shows ? placeholders for user_id, status, and the sanitized description term—never the raw values.

Where Query Builder Stops Protecting You

1. Dynamic Table or Column Names

If a request parameter chooses the table or column, concatenation is unsafe. Instead, validate against an allow-list:

$allowedTables = ['orders', 'invoices', 'shipments'];
$table = in_array($inputTable, $allowedTables, true) ? $inputTable : 'orders';
$builder = $this->db->table($table); // safe: validated, not escaped

For column names in select() or orderBy(), use the same allow-list approach. If you must accept arbitrary identifiers, enable $this->db->escapeIdentifiers = true; and wrap identifiers in backticks in raw SQL, but verify the generated SQL per driver—MySQL uses backticks, PostgreSQL uses double quotes, SQL Server uses brackets.

2. Wildcards in like()

like('col', $userInput) sends the string directly. A user entering 100% matches everything starting with "100". Always call escapeLikeString() first:

$safe = $this->db->escapeLikeString($userInput);
$builder->like('col', $safe); // % and _ become \% and \_

If you need literal percent signs in the pattern, escape them yourself before passing to like().

3. insert_batch() Limits

insert_batch() builds a single multi-row INSERT with bound values for every row. Drivers impose limits:

• MySQL max_allowed_packet (default 64 MB)
• PostgreSQL parameter limit (65,535 per statement)
• Memory for building the query array

Chunk large datasets:

$rows = [...] // thousands of rows
$chunks = array_chunk($rows, 500);
foreach ($chunks as $chunk) {
    $this->db->table('logs')->insertBatch($chunk);
}

4. Raw Expressions and where() Callbacks

Passing a closure to where() or using whereRaw() lets you write SQL fragments. Values inside those fragments are not bound unless you use setBinding() manually. Prefer the fluent methods; reserve raw SQL for cases the builder cannot express.

Verification Checklist

1. Enable debug: $this->db->debug = true; in development. Confirm every value appears as ? in the logged SQL.
2. Test with a value containing a single quote (e.g., O'Brien). Insert it, then select it back—no syntax error, the quote is stored literally.
3. Inspect system/Database/QueryBuilder.php and your driver (system/Database/MySQLi/, Postgre/, etc.) to confirm placeholder style (? vs :name) and identifier quoting for your CodeIgniter 4 version.
4. Run a query with escapeIdentifiers = true and verify the generated SQL uses the correct quote character for your database.

Common Mistakes to Avoid

Mistake	Why It Fails	Fix
->where('col', $input) where $input is an array	Array is cast to string "Array"	Use whereIn('col', $array)
->orderBy($userInput)	Direction or column injected	Validate against ['asc','desc'] and allow-listed columns
->like('col', $input) without escaping	Wildcards match unintended rows	Call escapeLikeString($input) first
Enabling escapeIdentifiers globally	Changes quoting across all queries; may break existing raw SQL	Enable per-connection or per-query; test thoroughly
Single massive insertBatch()	Packet/parameter limits exceeded	Chunk to 500–1,000 rows per batch

Limitations Summary

• Query Builder binds values only. Identifiers, SQL keywords, and structural fragments are your responsibility.
• escapeIdentifiers adds parsing overhead and behaves differently per driver; it is not a substitute for allow-list validation.
• escapeLikeString() handles %, _, and the escape character itself, but does not protect against other injection vectors—use it only for like() arguments.
• Batch inserts are convenient but not a streaming solution; monitor packet size and memory.

Stick to the fluent methods for values, validate every identifier against a known set, and verify the generated SQL in your target database. That combination keeps the query structure static and the data safely bound.