Secure and Portable Queries in CodeIgniter: Mastering the Query Builder

Why the Query Builder Matters

When you need to build database queries that run on MySQL, PostgreSQL, SQLite, or any other supported driver, CodeIgniter’s Query Builder gives you a single, platform‑independent API. The key benefits are:

• Automatic escaping protects against SQL injection.
• Method chaining keeps code readable and maintainable.
• Table prefixing and driver abstraction let you switch back‑ends with minimal code changes.

Getting Started: Instantiating the Builder

// In a controller or model
$db = \Config\Database::connect(); // requires DB credentials in app/Config/Database.php
$builder = $db->table('users');

Running $builder->get() executes a SELECT * FROM users query and returns a CodeIgniter\Database\Result object.

Method Chaining in Action

Build a complex query in a single fluent statement:

$query = $db->table('orders')
    ->select(['orders.id', 'users.name', 'orders.total'])
    ->join('users', 'users.id = orders.user_id')
    ->where('orders.status', 'shipped')
    ->orderBy('orders.created_at', 'DESC')
    ->limit(20)
    ->get();

To inspect the generated SQL without hitting the database, use getCompiledSelect():

echo $db->table('orders')->getCompiledSelect();
// Outputs something like: SELECT orders.id, users.name, orders.total FROM orders
// JOIN users ON users.id = orders.user_id WHERE orders.status = 'shipped'
// ORDER BY orders.created_at DESC LIMIT 20

Running the query returns a Result object. Use $query->getResultArray() or $query->getResultObject() to fetch rows.

Escaping User Input Safely

Never concatenate raw strings into where() or like() calls. Use the placeholder syntax or the array form:

$builder->where(['username' => $inputUsername, 'active' => 1]);

Here, $inputUsername is automatically escaped. If you must embed a value in a raw string, use escape():

$builder->where('age > ' . $db->escape($minAge));

Failing to escape can bypass the Query Builder’s protection and open the door to injection attacks.

Table Prefixing and Driver Flexibility

In app/Config/Database.php, set $default['tablePrefix'] = 'app_';. All table names passed to the builder will automatically receive this prefix, making a migration to a different schema trivial.

The same code works across drivers:

• MySQL: SELECT * FROM app_users
• PostgreSQL: SELECT * FROM "app_users"
• SQLite: SELECT * FROM app_users

Limitations and Common Pitfalls

• Complex Joins & Subqueries: For deeply nested or highly optimised queries, the abstraction can generate suboptimal SQL. In such cases, consider writing raw SQL with query() and binding parameters manually.
• Mixing Raw and Builder Methods: If you start with builder methods and later append a raw string, the final SQL may not be escaped consistently. Stick to one style per query.
• Performance Overhead: Each builder call adds a PHP method call. For a tight loop over thousands of queries, the overhead may become noticeable.

Verification Checklist

1. Connectivity: Run $db->table('users')->get(); and ensure you receive a Result object. If not, check app/Config/Database.php for correct credentials.

2. SQL Generation: Use getCompiledSelect() to confirm the query matches your expectations.

3. Escaping: Pass a string containing quotes or semicolons to where() and verify the compiled SQL contains escaped values.

4. Prefixing: Enable tablePrefix and confirm the compiled SQL includes the prefix.

Practical Example: Secure User Search

// app/Controllers/Users.php
public function search()
{
    $db = \Config\Database::connect();
    $builder = $db->table('users');

    // User input from a GET parameter
    $keyword = $this->request->getGet('q');

    // Build query safely
    $builder->like('username', $keyword)
            ->orLike('email', $keyword)
            ->orderBy('created_at', 'DESC')
            ->limit(10);

    $result = $builder->get();
    $data['users'] = $result->getResultArray();

    return view('users/search', $data);
}

Because like() automatically escapes the $keyword, even if a user submits malicious input, the database receives a safe query. The method chaining keeps the code concise and readable.

Conclusion

CodeIgniter’s Query Builder strikes a balance between safety, portability, and developer ergonomics. Use it for the majority of CRUD operations, verify generated SQL with getCompiledSelect(), and reserve raw SQL for performance‑critical or highly custom queries.