Secure Database Queries with CodeIgniter 4 Query Builder: A Practical Guide

Desired outcome

Use CodeIgniter 4’s Query Builder to write database‑agnostic CRUD statements that automatically escape values, preventing SQL injection while keeping code readable.

Prerequisites

• CodeIgniter 4.4+ installed and running.
• A configured database connection in app/Config/Database.php (driver, hostname, username, password, database name).
• Access to a controller, model, or service where the database service can be instantiated ($db = \Config\Database::connect();).
• Basic familiarity with PHP namespaces and the MVC pattern used by CodeIgniter.

Procedure

1. Initialize the database service

   $db = \Config\Database::connect();

   Place this line in the constructor of a controller or at the top of a model method. The call returns a \CodeIgniter\Database\BaseConnection instance that already has the Query Builder loaded.

2. Insert a record with automatic escaping

   $data = [
       'email'    => $request->getPost('email'),
       'username' => $request->getPost('username'),
       'password' => password_hash($request->getPost('password'), PASSWORD_DEFAULT),
   ];
   
   $db->table('users')->insert($data);
   if ($db->affectedRows() === 1) {
       $newId = $db->insertID();
       // success handling
   }

   The insert() method builds a parameterised INSERT statement; every value in $data is escaped according to the active driver.

3. Read data using the fluent interface

   $builder = $db->table('users');
   $builder->select('id, email, username')
           ->where('status', 'active')
           ->orderBy('created_at', 'DESC')
           ->limit(10);
   
   $query = $builder->get();
   $users = $query->getResultArray(); // returns an array of associative arrays

   Method chaining keeps the query readable and guarantees that the where clause values are escaped.

4. Update with conditional logic

   $db->table('users')
      ->where('id', $userId)
      ->set('last_login', date('Y-m-d H:i:s'))
      ->update();
   
   if ($db->affectedRows() > 0) {
       // record updated
   }

5. Delete safely

   $db->table('users')
      ->where('id', $userId)
      ->delete();
   
   if ($db->affectedRows() === 1) {
       // deletion confirmed
   }

6. Complex query example – join with sub‑query

   $sub = $db->table('orders')
               ->select('user_id, COUNT(*) as order_count')
               ->groupBy('user_id');
   
   $builder = $db->table('users u')
       ->select('u.id, u.email, o.order_count')
       ->join("({$sub->getCompiledSelect()}) o", 'o.user_id = u.id', 'left')
       ->where('u.status', 'active');
   
   $results = $builder->get()->getResult();

   Using getCompiledSelect() on a sub‑query builder lets you embed it without writing raw SQL, preserving escaping for the outer query.

Expected checks

• Connection verification – after \Config\Database::connect() call $db->initialize() (implicit) and confirm no exception is thrown.
• Insert persistence – call $db->insertID() immediately after insert(); a non‑zero integer confirms the row was written.
• Escaping test – submit a value containing a single quote (e.g., O'Reilly) through a form, run the insert, then inspect the generated query via $db->getLastQuery(). The quote should appear escaped (e.g., O\'Reilly) and not break the statement.
• Row count validation – after update() or delete(), compare $db->affectedRows() with the expected number of rows.

Recovery options

• If insert() throws a DatabaseException, catch it, log $e->getMessage(), and present a user‑friendly error. No database state change occurs, so no rollback is required.
• For a failed update() or delete() where affectedRows() is zero, treat it as a “not found” condition rather than an error; optionally log the missing identifier for audit purposes.
• When a raw query is unavoidable, use $db->query($sql, $binds) with positional or named bind parameters. This restores parameterised execution and avoids manual string concatenation.

Limitations & practical verification

The Query Builder abstracts most SQL, but driver‑specific functions (e.g., MySQL’s JSON_EXTRACT or PostgreSQL’s ILIKE) must be written as raw expressions inside select() or where(). Those fragments are not escaped, so validate any user input that reaches them.

To verify that escaping works in your environment, run a quick script:

$db = \Config\Database::connect();
$test = "test' OR '1'='1";
$db->table('tmp_test')->insert(['col' => $test]);
echo $db->getLastQuery();

Inspect the output; the single quote should be escaped. Delete the test row afterwards.

Summary checklist

Step	Command / Code	Success indicator
Connect	$db = \Config\Database::connect();	No exception
Insert	$db->table('users')->insert($data);	$db->insertID() > 0
Select	$builder->get()->getResultArray();	Array length matches expectation
Update	$db->table('users')->where('id',$id)->set(...)->update();	$db->affectedRows() === 1
Delete	$db->table('users')->where('id',$id)->delete();	$db->affectedRows() === 1