CodeIgniter 4 Query Builder

When to use

• The user wants to build a SELECT, INSERT, UPDATE, or DELETE query in CodeIgniter 4 using the Query Builder.
• The user needs to fetch and iterate CodeIgniter query results as arrays, objects, or entity instances.
• The user needs to filter query results by user-supplied values without risking SQL injection.

When NOT to use

• Do not force complex window functions or vendor-specific analytical SQL through the builder; use a parameterized raw query.
• Do not use this skill for schema changes (CREATE TABLE, ALTER); use Forge and migrations instead.

Core guidance

• Allow-list dynamic column and table names — When a column, table, or sort direction comes from input, validate it against a fixed allow-list before passing it to the builder; escaping does not cover identifiers.
• Pass values as arguments so the builder escapes them — Pass user values as the second argument to where(), like(), or insert() data arrays so the builder binds and escapes them; never concatenate them into the column or condition string.

Quick recipe

1. Get a fresh builder with $builder = $db->table('users') and project columns via $builder->select('id, email, role'). (verify: The compiled SQL names only the requested columns)
2. Filter with $builder->where('role', $role) passing the untrusted value as an argument so the builder binds and escapes it. (verify: The value appears as a bound parameter, not inlined text)
3. Run $rows = $builder->limit(50)->get()->getResultArray() and handle the empty case before using the rows. (verify: An unmatched filter returns an empty array, not an error)

Best practices

• Know that terminal calls like get() reset the builder, while getCompiledSelect() keeps state; start a fresh $db->table('t') per logical query to avoid leaked clauses
• Use getResult() for objects, getResultArray() for arrays, and getRow() / getRowArray() for a single row; call getResult(Entity::class) to hydrate entities
• Name the columns in select('id, name') instead of relying on SELECT *, and add limit() when the caller only needs a page of rows

Anti-patterns

See references/anti-patterns.md.

Common mistakes

1. getRow returns null on no match — getRow() returns null when no row matches, not an empty object. Fix: Check the result for null and return a 404 or domain error before dereferencing.
2. Querying inside a loop causes N+1 round trips — Running a builder query for each row of a parent result issues one query per iteration. Fix: Fetch related rows in one query with whereIn() or a join, then group in PHP.
3. Concatenating input into a where string — Passing a fully-built string like where("email = '$email'") skips escaping. Fix: Use where('email', $email) so the builder binds and escapes the value.

Version notes

• CI4 Query Builder replaces CI3 Active Record — CodeIgniter 4 renamed the CI3 "Active Record" class to the Query Builder, accessed via $db->table() and the connection service.

Security

• Bind values and allow-list identifiers at the boundary — Treat every request-sourced value as untrusted: pass values as bound builder arguments (never string-concatenated), and validate any dynamic column, table, or sort identifier against a fixed allow-list.

Testing

• Activation and decision behavior are verified in eval/scenarios/codeigniter/query-builder.yaml.

Additional resources

• references/anti-patterns.md
• references/core.md
• references/examples.md
• references/pitfalls.md
• references/recipes.md
• references/version-differences.md

Knowledge graph

Prerequisites: codeigniter/controllers
Related: security/sql-injection-prevention