Stopping SQL Injection in CodeIgniter 4 with Query Builder

The Danger of Raw Strings

Many developers fall into the trap of concatenating user input directly into SQL strings. Even with basic filtering, this approach leaves an application vulnerable to SQL injection—where a malicious actor injects database commands into a form field to bypass authentication or leak data. The immediate solution is to stop writing raw SQL and start using the CodeIgniter 4 Query Builder.

Programmatic Query Construction

Unlike static SQL strings, the Query Builder uses method chaining. This allows you to build a query dynamically based on application logic. For example, if a user provides an optional search filter, you can conditionally add a where() clause without manually managing AND or OR strings.

The get() method finalizes a SELECT query, while insert() and update() handle data modification. These methods accept associative arrays where the keys map directly to your database column names, reducing the boilerplate code required to map form inputs to table fields.

Implementation Example: Secure User Search

The following example demonstrates how to handle a search request where the user can filter by username and status. This code should be placed within a Model or Controller. It assumes you have a database connection configured in app/Config/Database.php.

// Required permissions: Database user must have SELECT privileges on the 'users' table.
$db = \Config\Database::connect();
$builder = $db->table('users');

// User-supplied data from a request
$username = $this->request->getGet('username');
$status = $this->request->getGet('status');

// Start building the query
$builder->select('id, username, email');

if (!empty($username)) {
    // The Query Builder automatically escapes $username
    $builder->where('username', $username);
}

if (!empty($status)) {
    $builder->where('status', $status);
}

$query = $builder->get();
$results = $query->getResultArray();

Verifying the Output

To ensure the Query Builder is producing the SQL you expect, you can use the getLastQuery() method. This is critical during development to verify that joins and where clauses are logically sound.

// Run this immediately after the get() call to see the compiled SQL
echo (string)$db->getLastQuery();

Trade-offs: Builder vs. Raw SQL

While the Query Builder is the gold standard for security and portability, it has limitations. For highly complex reports involving multiple nested subqueries or database-specific optimization hints, the Builder syntax can become verbose and difficult to read.

In these rare cases, you may need to use $db->query() for raw SQL. However, you must never pass variables directly into a raw query. Instead, use Query Bindings to maintain security:

// SECURE way to run raw SQL using bindings
$sql = "SELECT * FROM users WHERE id = ? AND status = ?";
$db->query($sql, [$userId, $userStatus]);

Final Checklist for Implementation

• Avoid $db->query("...") with concatenated variables.
• Use table() to initialize the builder for a specific target.
• Verify the generated SQL using getLastQuery() during the testing phase.
• Check that your database user has the minimum necessary permissions (e.g., SELECT only for read-only views).