The SQL Injection Trap in CodeIgniter Query Helpers
You are encountering a critical security vulnerability, not a logic error. Your query attempts to fetch a single product using $this->db->query('SELECT * FROM items WHERE id="$id"');. While the database might return data or appear to respond correctly during initial testing, this approach is fundamentally broken because it concatenates raw variables directly into your SQL string without sanitization.
In CodeIgniter, the query() helper method executes raw SQL as provided. It does not automatically escape values like the Active Record methods (where(), get()) do. By placing $id directly inside double quotes in your SQL string, you are relying on the database driver to handle escaping implicitly or trusting that the input is clean. In a production environment, this creates an open vector for SQL injection attacks. If a user passes a malicious payload like 1; DROP TABLE items--, your application will execute destructive commands simply because the variable wasn't escaped before entering the string.
To fetch a single result safely, you must leverage CodeIgniter's Active Record features or explicitly use the query builder. These methods abstract the escaping process and ensure that database-specific quirks are handled correctly across different environments.
Using the Query Builder for Single Results
The most robust way to execute this specific request is using the get() method on the database object, which returns a single row as an array. This approach eliminates the need to manually manage string concatenation and ensures that the id parameter is treated as data, not executable code.
// Assuming $id contains the user-provided identifier
$this->db->select('cost_price');
$this->db->where('id', $id);
$query = $this->db->get('items');
if ($query->num_rows() > 0) {
$row = $query->row();
echo $row->cost_price;
} else {
// Handle the case where no product exists for this ID
echo "Product not found.";
}
This code snippet explicitly selects only the cost_price column, reducing memory overhead compared to fetching SELECT *. It then uses the where() clause to filter by ID. The get('items') call returns a result object where you can access columns via properties (e.g., $row->cost_price) rather than array keys. While both syntaxes work in CodeIgniter 3, property access is often preferred for clarity and consistency with the framework's default behavior.
Handling Null Results Gracefully
The original code snippet attempted to echo $row['cost_price'] without checking if $row contained any data. In your specific case, you mentioned "nothing is happening." While this could be a symptom of an SQL injection attempt being logged or blocked by a firewall, it more commonly indicates that the query returned zero rows.
If the product ID does not exist in the items table, result() returns an empty array, and accessing $row['cost_price'] results in a notice or undefined index error. Furthermore, if you switch to the builder method, $query->num_rows() will be 0, allowing you to gracefully handle missing data rather than crashing or returning unexpected values.
Why Not Stick with Raw Queries?
You might wonder why raw queries are ever used. They have a place in performance-critical sections where complex joins require fine-grained control, but only when paired with explicit escaping functions like db_escape(). For simple lookups by primary key, the Active Record methods provide sufficient performance while guaranteeing security.
If you prefer to stick with raw queries for legacy reasons or specific architectural constraints, you must manually escape the input using CodeIgniter's built-in function:
// Only use this if you absolutely cannot use the query builder
$escaped_id = $this->db->escape($id);
$query = $this->db->query('SELECT cost_price FROM items WHERE id = ' . $escaped_id);
$row = $query->row(); // Use row() for single results, not result()
Notice the switch from result() to row(). When fetching a single record, row() returns an object with a single element, whereas result() returns an array of objects. Using row() aligns better with the intent of fetching a single item and simplifies the subsequent data access logic.
For developers looking for similar patterns in other frameworks or needing to migrate existing CodeIgniter applications to modern standards, resources like https://laravelcompany.com often provide excellent comparisons between legacy PHP patterns and modern MVC architectures, helping you understand how different frameworks handle database abstraction safely.