CodeIgniter's where_not_in() with a dynamic blacklist string of values only works when a single-value string

CodeIgniter's where_not_in() with a dynamic blacklist string of values only works when a single-value string

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

Stefan Bogdanescu

Founder & Senior Architect

CodeIgniter's where_not_in() and the Pitfall of Raw Comma Strings

When working with dynamic filters in CodeIgniter, specifically using the where_not_in() method, a common frustration arises when passing user input directly into the query builder without proper sanitization. It is tempting to assume that if a single value works perfectly, a comma-delimited string of values should behave similarly. However, the underlying SQL generation reveals why this assumption leads to syntax errors.

Consider a scenario where you are building a CRM module and need to exclude specific users from a survey list. You have an $ignore variable containing user IDs that must be filtered out. If you pass a raw comma-separated string like "12,13" directly into the query builder:

$ignore = "12,13";
$this->db->where_not_in('crm.user_id', $ignore);

CodeIgniter treats $ignore as a single scalar value because it is a plain string. The resulting SQL becomes:

SELECT * FROM (`crm`)
WHERE `crm`.`user_id` NOT IN ('16,13') 
AND `survey` = 1;

The database engine interprets '16,13' as one single string literal, not a list of integers. Consequently, the query fails to match any IDs that look like 12 or 13, effectively breaking the blacklist logic entirely. The parser sees the comma inside the quotes and treats it as part of the data value, not a separator for the IN clause arguments.

To fix this, you must transform the raw string into an array of individual values before passing them to the method. CodeIgniter's Query Builder expects either a native PHP array or a database expression that resolves to multiple items. Passing an array ensures that each element is quoted individually in the final SQL statement.

Here is how you should preprocess the input:

// $ignore is still "12,13" from user input
$ignore_array = explode(',', $ignore); 

// Remove whitespace and ensure they are treated as distinct values
$ignore_clean = array_map('trim', $ignore_array);

$this->db->where_not_in('crm.user_id', $ignore_clean);

By converting the string into an array, CodeIgniter generates the correct SQL structure:

SELECT * FROM (`crm`)
WHERE `crm`.`user_id` NOT IN ('16', '13') 
AND `survey` = 1;

This approach ensures that every ID is enclosed in its own pair of quotes, allowing the SQL engine to correctly interpret them as a set of distinct values.

If your input format includes quotes already, such as "'12','13'", you must strip those characters before exploding the string. Otherwise, you end up with escaped quotes inside your array elements, which results in malformed SQL like '\'16\',\'13\''. The safest practice is to assume the input is a raw list of values without surrounding quotes and handle the normalization explicitly.

This issue highlights a broader principle in dynamic query construction: never trust user-provided strings as arrays. Even if the source material suggests quoting works, it often leads to nested escaping issues that are harder to debug than simply exploding the string into clean integers or strings first.

While this specific problem is solved by preprocessing the input array, larger applications might consider implementing a dedicated filter service to handle blacklist logic consistently across different modules. This reduces the cognitive load on developers who might otherwise forget to explode a comma-separated string before passing it to an ORM method.

Our network