• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Active Record: Beware of where() calls containing OR clauses

#1
[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.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.