Securing Database Interactions with the CodeIgniter 4 Query Builder

The Risk of Manual SQL Construction

Writing raw SQL strings often leads to security vulnerabilities, specifically SQL injection, where malicious user input alters the query logic. While raw queries are sometimes necessary for complex reporting, they require manual binding to remain secure. The CodeIgniter 4 Query Builder solves this by providing an abstraction layer that automatically escapes values and generates database-agnostic SQL.

Prerequisites

• A running installation of CodeIgniter 4.x.
• A configured database connection in your .env file (e.g., database.default.hostname, database.default.database, database.default.username, and database.default.password).
• A database table to query (e.g., a users table with id, username, and email columns).

Implementing Secure Data Retrieval

The Query Builder uses method chaining to build a query object. This approach separates the query logic from the data, ensuring that the database driver handles the escaping of values before the query is executed.

To retrieve data based on user input, avoid concatenating variables into a string. Instead, use the where() method:

// Run this within a Controller or Model method
$db = \Config\Database::connect();
$builder = $db->table('users');

// User-provided input (potentially unsafe)
$userInput = $this->request->getGet('username');

// The Query Builder automatically escapes $userInput
$query = $builder->where('username', $userInput)
                 ->where('status', 'active')
                 ->get();

$results = $query->getResult();

Handling Data Modifications

For INSERT and UPDATE operations, the Query Builder accepts associative arrays. This removes the need to write INSERT INTO... statements manually and ensures that every value is treated as data, not executable code.

Example: Updating a User Profile

$db = \Config\Database::connect();
$builder = $db->table('users');

$data = [
    'email' => '[contact removed]',
    'updated_at' => date('Y-m-d H:i:s')
];

// Update the record where the ID matches
$builder->where('id', 123);
$builder->update($data);

Diagnostic Verification

Because the Query Builder abstracts the SQL, you cannot see the final query in your application code. To verify that the generated SQL is efficient and correctly escaped, use the getLastQuery() method.

1. Execute your Query Builder operation.
2. Immediately call the database connection's last query method.
3. Print the output to a log or screen for inspection.

$db = \Config\Database::connect();
// ... execute builder operations ...

echo $db->getLastQuery();

Check: Ensure that values provided by users are wrapped in quotes (escaped) and that no raw user input is appearing directly in the SQL keywords.

Comparison: Query Builder vs. Raw Queries

Feature	Query Builder	Raw Query ($db->query())
Escaping	Automatic	Manual (via bindings)
Portability	High (Driver-agnostic)	Low (Dialect-specific)
Readability	Fluent/Chained	String-based
Complexity	Best for CRUD	Best for complex JOINs/Reports

Limitations and Risks

• Performance: In extremely complex reporting scenarios with multiple nested subqueries, the abstraction layer may produce less efficient SQL than a hand-tuned query.
• Driver Gaps: While the Query Builder is portable, some advanced database-specific features (like certain JSON operators in PostgreSQL) may not be supported by the abstraction layer and will require raw queries.
• Raw Bypassing: If you use $builder->where("username = '$userInput'"), you are passing a string that the builder treats as raw SQL, bypassing the automatic escaping and re-introducing SQL injection risks. Always use the ('column', 'value') syntax.