Preventing SQL Injection in CodeIgniter with Query Builder

The Risk of String Concatenation

Many developers fall into the trap of building database queries using string concatenation, especially when handling dynamic user input. When you manually build a string like "WHERE username = '" . $user_input . "'", you open your application to SQL injection. An attacker can provide a crafted string that closes your quote and appends a malicious command, potentially leaking your entire user table or deleting data.

The most effective way to eliminate this risk in CodeIgniter is to stop writing raw SQL strings and instead use the Query Builder. This tool abstracts the SQL generation and, more importantly, automatically escapes all values passed into its methods.

How Query Builder Handles Security

Query Builder uses a fluent interface—meaning you chain methods together—to define the structure of your query. When you pass a value to a method like where() or insert(), CodeIgniter does not simply append the text to the query. It applies database-specific escaping to ensure the value is treated as data, not as executable code.

For example, if a user submits ' OR '1'='1 as a username, Query Builder ensures the database searches for that literal string rather than interpreting the OR '1'='1 as a logic gate to bypass authentication.

Implementation Example: Secure Data Retrieval

Below is a practical implementation of a search function. This example assumes you are working within a Model class in CodeIgniter 3 or 4, where the database object is available via $this->db.

// In your Model class
public function get_user_by_email($email, $status = 'active')
{
    // The Query Builder automatically escapes $email and $status
    $this->db->select('id, username, email');
    $this->db->from('users');
    $this->db->where('email', $email);
    $this->db->where('status', $status);
    
    $query = $this->db->get();
    
    return $query->result();
}

Verification and Diagnostics

To verify that the Query Builder is producing the expected SQL and escaping your values correctly, you can use the built-in debugging tools. Run the following command immediately after your get() call to see the final compiled query:

echo $this->db->last_query();

Risk Check: If you see values in the output that are not wrapped in quotes or escaped (e.g., WHERE email = [contact removed] instead of WHERE email = '[contact removed]'), it indicates a configuration error or that you have bypassed the builder using a raw query.

When Query Builder Isn't Enough

While Query Builder is powerful, it cannot express every possible SQL feature. You may encounter scenarios involving complex subqueries, specific window functions, or highly optimized JOINs that the builder cannot generate.

In these cases, you must use $this->db->query(). However, doing so disables automatic escaping. To maintain security when using raw queries, you must manually escape your variables using the escape() method:

$sql = "SELECT * FROM users WHERE username = " . $this->db->escape($username);
$this->db->query($sql);

Trade-offs and Performance

Using an abstraction layer introduces a small amount of overhead because the framework must compile the method chain into a SQL string before sending it to the database. For the vast majority of web applications, this latency is negligible. However, in high-throughput environments processing thousands of queries per second, hand-optimized raw SQL may be necessary for performance.

Comparison: Raw vs. Builder

Feature	Query Builder	Raw SQL (query())
Security	Automatic Escaping	Manual Escaping Required
Portability	Database Agnostic	Dialect Specific
Complexity	Easy for Standard CRUD	Better for Complex Logic

Actionable Summary

To secure your CodeIgniter application, adopt a "Builder-First" policy. Use where(), insert(), and update() for all standard operations. Only drop down to $this->db->query() when the builder cannot support the required SQL logic, and always wrap those variables in $this->db->escape() to prevent injection attacks.