CodeIgniter active record like() is escaping manually added wildcards (%)

CodeIgniter active record like() is escaping manually added wildcards (%)

  1. Blog
  2. 15 November 2026
  3. 5 min read

Stefan Bogdanescu

Founder & Senior Architect

CodeIgniter Active Record Like() is Escaping Manually Added Wildcards (%)

When working with the like() method in CodeIgniter's Active Record, a common stumbling block arises when trying to use wildcards that you have manually appended to your search string. If you write $this->db->like('film.title', "%$query%") and then attempt to escape the user input using $this->db->escape_like_str($query), the query often fails to return results. The core issue lies in a fundamental misunderstanding of how the like() method processes its parameters and when escaping actually occurs within the framework's internal logic.

The like() function in CodeIgniter is designed to handle both the column name and the search pattern. Crucially, it expects the second argument (the pattern) to be a raw string that contains your wildcards, but it also needs to know which characters within that string are special regex-like symbols that require escaping. When you pass "%$query%" directly into the method, CodeIgniter treats the entire string as the pattern.

The problem manifests when you attempt to manually escape only the $query variable before passing it in. The framework's internal logic typically wraps the provided pattern in wildcards and then applies its own escaping mechanism based on the database driver's configuration (often using LIKE or % specific rules). If you have already wrapped your input in % signs, and the framework attempts to escape those % signs because it thinks they are part of the user-provided special characters, you end up with a malformed SQL statement. Depending on the specific version of CodeIgniter and the database driver (MySQL, PostgreSQL, etc.), this often results in the wildcards being double-escaped or stripped entirely, turning '%value%' into '%%value%%' or simply 'value', breaking your intended partial match logic.

The Correct Approach for Manual Wildcards

To fix this, you must ensure that the % signs are treated as literal parts of the pattern rather than user input that needs escaping. There are two primary ways to achieve this safely without injecting SQL injection vulnerabilities.

Option 1: Escape Only the User Input, Keep Wildcards Raw
If you are constructing the pattern manually by wrapping user input, you should escape the $query variable before adding the wildcards, but ensure the framework doesn't re-escape the outer % signs. However, a more robust pattern in CodeIgniter is to let the like() method handle the surrounding logic if possible. If you insist on manual wrapping, ensure you are not passing the escaped string as the second argument in a way that triggers double-escaping.

The most reliable pattern often involves separating the user input from the wildcard structure:

// Do not escape here if you want to keep % signs intact for the like() method
$userInput = $this->db->escape_like_str($query); 

// However, CodeIgniter's like() expects the second param to be the full pattern.
// If you pass "%$userInput%" and $userInput is already escaped by escape_like_str(),
// you might encounter issues depending on how the driver handles the outer %.

// A safer approach for strict manual wildcard control:
$searchPattern = "%{$this->db->escape_like_str($query)}%";
$this->db->like('film.title', $searchPattern);

Wait, there is a nuance here. In many versions of CodeIgniter, escape_like_str() specifically targets backslashes and double quotes depending on the driver's like_escape setting. If your database uses % as the escape character (common in MySQL if LIKE_ESCAPE is set), you need to be careful.

The most robust solution that avoids ambiguity is to pass the unescaped user input to escape_like_str() inside the pattern construction, but ensure you aren't double-escaping the wildcards themselves. However, the cleanest way to avoid this headache entirely in CodeIgniter is often to use the built-in escaping logic provided by the method itself when constructing the query dynamically, or explicitly set the escape character.

Handling Specific Database Drivers

Different database drivers handle like patterns differently. MySQL, for instance, allows you to change the escape character via configuration. If your application relies on % as a wildcard and you are manually adding them, you must ensure that escape_like_str() does not interpret those outer % signs as characters needing escaping.

In CodeIgniter 3.x, if you find that manual wildcards are being mangled, check your database configuration array in database/config/database.php. Look for the like_escape option. If you set this to a specific character (e.g., \) instead of the default %, you can prevent the framework from escaping your manually added percent signs.

'database' => [
    // ...
    'DB_' . $this->db->db_prefix . 'like_escape' => '\\', // Custom escape char
],

By changing the escape character to a backslash, escape_like_str() will only escape backslashes and double quotes, leaving your manually added % signs untouched. This allows you to safely construct patterns like "%$query%" without fear of the wildcards being converted into escaped sequences that the SQL engine cannot interpret correctly as wildcards.

Why This Matters for Security and Logic

It is critical not to confuse "escaping user input" with "handling special characters in patterns." Escaping is a security measure to prevent SQL injection, but it must be applied consistently. If you escape the % sign because the framework thinks it's part of the user input, you break the SQL syntax. By understanding that like() expects a pattern string and applying escaping only to the dynamic portion (the user input), or by configuring the driver to use a different escape character, you maintain both security and functionality.

When debugging these issues, always inspect the generated SQL query. If you see %%value%% in your logs when you expected %value%, you know double-escaping is occurring. Adjusting your pattern construction or the database escape configuration resolves this immediately.

Our network