Using CodeIgniter Query Builder for Safe, Paginated Blog Posts

Problem: raw SQL in controllers creates risk and vendor lock‑in

When you write SQL strings directly in a CodeIgniter controller, two issues appear quickly. First, any user‑supplied value that is concatenated into the string opens the door to SQL injection unless you remember to escape every piece. Second, the query becomes tied to the specific dialect of the database you are testing against (MySQL, PostgreSQL, SQLite, etc.), making it harder to switch engines later.

Thesis: Query Builder gives a consistent, secure abstraction

CodeIgniter’s Query Builder layer automatically prepares statements, binds parameters, and generates SQL that works across all supported drivers. By letting the library handle escaping and clause assembly, you keep controllers thin, reduce injection risk, and retain the ability to change the underlying database with minimal code changes.

Section 1: core methods build parameterized queries

The Query Builder exposes fluent methods such as select(), where(), join(), limit() and order_by(). Each call adds a clause to an internal query object; the actual SQL is not sent to the database until you call get(), insert(), update() or delete(). Because values are passed as separate arguments, the library treats them as bound parameters, not as raw text.

// Example: inside a controller method
$this->db->select('id, title, created_at');
$this->db->from('posts');
if (!empty($search)) {
    $this->db->like('title', $search); // automatically escaped
}
$this->db->order_by('created_at', 'DESC');
$query = $this->db->get();

The resulting $query object contains the executed statement and can be iterated with result() or row().

Section 2: feeding the result to the pagination library

CodeIgniter’s pagination library expects a total row count and a per‑page limit. Rather than writing a separate COUNT(*) query by hand, you can reuse the same Query Builder instance: clone the builder, remove ordering/limit clauses, and run a count query. The pagination library then generates the correct LIMIT and OFFSET values for you.

// Continue from the previous example
// 1. Get total rows (clone to avoid affecting the original query)
$this->db->reset_query(); // clears select/from/where/etc. but keeps db connection
$this->db->from('posts');
if (!empty($search)) {
    $this->db->like('title', $search);
}
$totalRows = $this->db->count_all_results();

// 2. Configure pagination
$this->load->library('pagination');
$config = [
    'base_url'    => base_url('blog/search'),
    'total_rows'  => $totalRows,
    'per_page'    => 10,
    'uri_segment' => 3,
    'use_page_numbers' => TRUE,
];
$this->pagination->initialize($config);

// 3. Apply limit/offset to the original query
$this->db->reset_query();
$this->db->select('id, title, created_at');
$this->db->from('posts');
if (!empty($search)) {
    $this->db->like('title', $search);
}
$this->db->order_by('created_at', 'DESC');
$this->db->limit($config['per_page'], ($this->uri->segment(3) - 1) * $config['per_page']);
$posts = $this->db->get()->result();

The view can then echo $this->pagination->create_links() to show navigation controls, all without manually crafting LIMIT or OFFSET clauses.

Worked example: search‑able, paginated blog list

Putting the pieces together, a typical controller method might look like this:

public function search()
{
    $search = $this->input->get('title');
    
    // ---- total rows ----
    $this->db->reset_query();
    $this->db->from('posts');
    if (!empty($search)) {
        $this->db->like('title', $search);
    }
    $totalRows = $this->db->count_all_results();
    
    // ---- pagination config ----
    $this->load->library('pagination');
    $config = [
        'base_url'    => base_url('blog/search'),
        'total_rows'  => $totalRows,
        'per_page'    => 10,
        'uri_segment' => 3,
        'use_page_numbers' => TRUE,
    ];
    $this->pagination->initialize($config);
    
    // ---- fetch page data ----
    $this->db->reset_query();
    $this->db->select('id, title, created_at');
    $this->db->from('posts');
    if (!empty($search)) {
        $this->db->like('title', $search);
    }
    $this->db->order_by('created_at', 'DESC');
    $this->db->limit($config['per_page'], ($this->uri->segment(3) - 1) * $config['per_page']);
    $data['posts'] = $this->db->get()->result();
    $data['pagination'] = $this->pagination->create_links();
    
    $this->load->view('blog/search', $data);
}

In the view (blog/search.php) you simply output:

<h1>Blog posts</h1>
<?php if (!empty($posts)): ?>
    <ul>
    <?php foreach ($posts as $p): ?>
        <li><a href="id) ?>><?= esc($p->title) ?></a> <small>(<?= $p->created_at ?>)</small></li>
    <?php endforeach; ?>
    </ul>
    <?= $pagination ?>
<?php else: ?>
    <p>No posts found.</p>
<?php endif; ?>

All user input ($search) is safely escaped by the Query Builder’s like() method, and the pagination links are generated from the validated total count.

Trade‑off and limitation

The Query Builder adds a thin layer of overhead because it builds an internal representation of the query before sending it to the driver. For most CRUD‑style operations this cost is negligible. However, certain vendor‑specific features—such as recursive CTEs, table hints, or complex window functions—cannot be expressed through the builder’s API. In those cases you must fall back to $this->db->query() with a raw string, remembering to bind parameters manually or use the query builder’s bind() method.

Actionable closing

To start using this pattern in your own project:

1. Ensure the database library is loaded ($this->load->database();) in your controller’s constructor or via autoload.
2. Enable query logging temporarily to verify the generated SQL: $this->db->save_queries = TRUE;.
3. After a request, inspect $this->db->queries to see the exact statement and bound values.
4. Run the same controller against both MySQL and SQLite (or PostgreSQL) to confirm that the pagination links and result sets behave identically.
5. When you encounter a feature the builder cannot express, isolate that query in a separate method and document why raw SQL is required.

By relying on CodeIgniter’s Query Builder for everyday selects, inserts, updates and deletes, you gain safer, more portable code while keeping the option to drop down to raw SQL when the situation truly demands it.