In this blog post, we’ll discuss the pros and cons of using direct SQL queries in CodeIgniter and explore alternatives that enhance security, readability, and maintainability.

What is Direct SQL?

Direct SQL means writing raw SQL queries directly in your code. It provides full control over the SQL, which can be appealing for quick queries or when you need a specific behavior that the framework’s ORM doesn’t easily support. However, this approach has its drawbacks.

Example of Direct SQL Query:

Here’s a basic example of direct SQL within a function in CodeIgniter to fetch ratings from a database:

public function get_ratings($ratable_type = "", $ratable_id = "", $is_sum = false) {
    if ($is_sum) {
        return $this->db->query("SELECT SUM(rating) AS rating FROM rating WHERE ratable_type = '$ratable_type' AND ratable_id = '$ratable_id'");
    } else {
        return $this->db->query("SELECT * FROM rating WHERE ratable_type = '$ratable_type' AND ratable_id = '$ratable_id'");
    }
}

Pros of Direct SQL Queries:

• Control: You have complete control over the SQL, which can be useful in complex queries or when ORM might be too restrictive.
• Flexibility: You can write any SQL query that the database engine supports without worrying about the limitations of an ORM.
• Quick Solutions: For small, non-critical queries, this might be faster to implement.

Cons of Direct SQL Queries:

1. Security Risks (SQL Injection): Direct SQL queries expose your application to SQL injection attacks if not handled properly. For example, passing user inputs directly into the query, as shown above, can lead to exploitation.
2. Maintainability: SQL code mixed within PHP can become difficult to read and maintain, especially in larger projects. Raw SQL queries might also become harder to adjust if you switch databases.
3. Readability: When SQL is embedded directly in PHP, it reduces the readability of your codebase and can make debugging more complex.
4. Reusability: ORM-based queries are more reusable and portable across different projects and databases.

A Safer Approach with CodeIgniter’s Query Builder

CodeIgniter provides an ORM-like query builder that helps improve security by preventing SQL injection and enhances readability by abstracting SQL queries.

Let’s refactor the previous example using CodeIgniter’s query builder:

public function get_ratings($ratable_type = "", $ratable_id = "", $is_sum = false) {
    if (empty($ratable_type) || empty($ratable_id)) {
        // Return a default rating value if inputs are missing
        return $this->db->query("SELECT 5 AS rating");
    }

    if ($is_sum) {
        // Use CodeIgniter's query builder for SUM
        $this->db->select_sum('rating');
        $this->db->where('ratable_type', $ratable_type);
        $this->db->where('ratable_id', $ratable_id);
        $query = $this->db->get('rating');
    } else {
        // Get all ratings using query builder
        $this->db->from('rating');
        $this->db->where('ratable_type', $ratable_type);
        $this->db->where('ratable_id', $ratable_id);
        $query = $this->db->get();
    }

    if ($query->num_rows() > 0) {
        return $query;
    } else {
        // Return default rating when no results found
        return $this->db->query("SELECT 5 AS rating");
    }
}

Why is This Better?

1. SQL Injection Prevention: CodeIgniter’s query builder automatically escapes inputs to protect against SQL injection. Using where() and other query builder methods ensures your queries are secure without needing manual sanitization.
2. Maintainability: Query builder methods are easier to read and modify. For example, you can quickly adjust or extend conditions without rewriting SQL.
3. Portability: If you switch to another database, CodeIgniter’s query builder makes it easier to adapt since it abstracts away many SQL dialect-specific features.
4. Improved Readability: Breaking down queries into smaller, more readable functions can make debugging and future development simpler.

Avoiding SQL Injection with Direct Queries

If you absolutely need to write direct SQL queries for complex operations, ensure that you use parameterized queries to prevent SQL injection. Here’s an example of how to do this safely:

public function get_ratings($ratable_type = "", $ratable_id = "", $is_sum = false) {
    $sql = "SELECT SUM(rating) AS rating FROM rating WHERE ratable_type = ? AND ratable_id = ?";
    $query = $this->db->query($sql, array($ratable_type, $ratable_id));
    return $query;
}

By using placeholders (?) and passing the parameters separately, CodeIgniter automatically escapes the inputs, ensuring the query is secure.

Conclusion

While direct SQL queries provide flexibility and control, using CodeIgniter’s query builder is generally safer, more maintainable, and readable. If you need to use raw SQL, always ensure that you’re protecting your application against SQL injection by using parameterized queries.