CodeIgniter Forums
Active Record: Beware of where() calls containing OR clauses - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Active Record: Beware of where() calls containing OR clauses (/thread-22247.html)



Active Record: Beware of where() calls containing OR clauses - El Forum - 09-02-2009

[eluser]Unknown[/eluser]
I had a rather appalling bug come up in production today - strangely it didn't manifest itself at all in development. Consider the following code in a model:

Code:
function whereNull($field)
{
  $this->db->where($field.' IS NULL OR '.$field.'=""');
}

You could call this function in a controller, along with other similar code adding some WHERE clauses based on user input, etc - but the OR statement above will actually make all of the preceding WHERE clauses void since Active Record does not automatically enclose this part of the query in parenthesis.

So, in effect:

Code:
$this->somemodel->whereSomething('foo', 'bar');
$this->somemodel->whereNull('field');
$this->somemodel->get();
... turns out as:

SELECT * FROM `table` WHERE `foo` = "bar" AND `field` IS NULL OR `field` = ""

Some punctuation later:

Code:
function whereNull($field)
{
  $this->db->where('('.$field.' IS NULL OR '.$field.'="")');
}

... which makes the previous example become this:

SELECT * FROM `table` WHERE `foo` = "bar" AND (`field` IS NULL OR `field` = "")

This works as expected.