Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Node.js, PHP and APIs

CodeIgniter 4 where() builds a different condition than you meant

ErrorA query with where() and orWhere() returns rows that should have been filtered out

Short answer

The query builder joins every where() with AND and every orWhere() with OR, with no brackets. Mixed conditions then bind in the wrong order. Wrap the OR part in groupStart() and groupEnd(). If you pass a raw string to where(), CodeIgniter does not escape it, so escape values yourself or use whereIn() for lists.

  • Applies to CodeIgniter 4.5, PHP 8.3, MySQL 8
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Print the SQL the builder produced. Read the WHERE part and look for a missing bracket or a quote in the wrong place.

    echo $builder->getCompiledSelect();
  2. 2Match what you see, then jump to its fix

    1. 1Rows from other users or other statuses appear in the resultAND and OR are mixed without bracketsFix
    2. 2A syntax error or an empty result when the value has a quote in it, such as O'BrienA custom string contains a value that was not escapedFix
    3. 3The WHERE has IN (...) built with implode, and breaks when the list is empty or contains textA list of values was glued into the string by handFix

What does the error look like?

  • A query with where() and orWhere() returns rows that should have been filtered out
  • A SQL syntax error that appears only when a value contains a quote character

Cause 1 of 3

AND and OR are mixed without brackets

1Check

Look at the compiled WHERE. An expression such as a AND b OR c is not the same as a AND (b OR c).

$builder->where('status', 'active')->where('name', 'Joe')->orWhere('id >', 50);
echo $builder->getCompiledSelect(false);

2Fix

Open a group with groupStart() before the OR conditions and close it with groupEnd(). Each groupStart() needs its own groupEnd().

$builder->where('status', 'active')
    ->groupStart()
        ->where('name', 'Joe')
        ->orWhere('id >', 50)
    ->groupEnd();

3Verify

Print the compiled query again and confirm the OR part sits inside brackets.

echo $builder->getCompiledSelect();

Confirmed by codeigniter.com

Did this fix it?

Cause 2 of 3

A custom string contains a value that was not escaped

1Check

In a custom string CodeIgniter does not escape the values for you. The manual warns that skipping the escape step could lead to SQL injection.

grep -rn "->where(\"" app/

2Fix

Escape every user value with $builder->db->escape() before putting it into the string, or avoid the string and use the array or key-value forms of where().

$name  = $builder->db->escape($input);
$where = "name={$name} AND status='boss' OR status='active'";
$builder->where($where);

3Verify

Test with a value that has an apostrophe and read the compiled SQL.

echo $builder->getCompiledSelect();

Confirmed by codeigniter.com

Did this fix it?

Cause 3 of 3

A list of values was glued into the string by hand

1Check

Search the code for implode used inside a where string.

grep -rn "implode" app/Models app/Controllers

2Fix

Pass the array to whereIn(); it produces WHERE field IN ('a', 'b', 'c') and adds the quotes for you.

$names = ['Frank', 'Todd', 'James'];
$builder->whereIn('username', $names);

3Verify

Check the compiled SQL shows quoted values.

echo $builder->getCompiledSelect();

Confirmed by codeigniter.com

Did this fix it?

How do I stop it coming back?

  • Prefer the array and key-value forms of where(); they are escaped for you.
  • Treat every raw where string as code review material: no request data goes in without escape().
  • Print getCompiledSelect() while writing a query that mixes OR with AND.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.