can we consider this fatal DB bug?

[eluser]Fierymind[/eluser]
Hi,

Not sure how this happen, but be careful, this will delete the entire table:

Code:

$this->db->where('id', FALSE);
$this->db->delete('table');

of course this is very simplified version of a fatal bug on my code that lead FALSE in 'where' clause, this wipe out entire table!


so I wonder, is this expected behavior or a bug?

[eluser]WanWizard[/eluser]
If I try that here (CI 2.0), the query produced is

Code:

DELETE FROM `table` WHERE `id` = 0

which doesn't delete anything, unless you have an id = 0...

Can you check $this->db->last_query() after executing that code (on a copy of a table, just in case)?

[eluser]Fierymind[/eluser]
I'm using CI 1.72 .. last query result is same like yours:

Code:

DELETE FROM `orders_infox` WHERE `oi_invoice_id` = 0

however, this still wipe out the table! in my table 'oi_invoice_id' is varchar(255)

[eluser]Dennis Rasmussen[/eluser]
PHP and MySQL version?
If using MySQL that is...

[eluser]Fierymind[/eluser]
well, running the query from command line wipe the table so its not CI issue.

but as Active Record is smart then it may put the 0 within commas if I'm comparing to varchar field to avoid such catastrophic result or at least result in error.

anyways, I need to double check every WHERE function I have to be sure it get proper variables ...

I'm using MySQL 5 and PHP 5.30

[eluser]WanWizard[/eluser]
Your query evaluates to

Code:

SELECT * FROM `table` WHERE name IS FALSE

which is (by accident?) equal to the AR statement you used.

The optimizer converts this to

Code:

SELECT * FROM `table`

Hence the selection of all records. Likewise, 'IS TRUE' will never return a single record (unless the field queried is a boolean).

[eluser]Fierymind[/eluser]
I have only couple of DELETE functions across the application, but I have zillion UPDATEs, which have same issue.

right now, if I pass FALSE or empty variable to WHERE function for any bug in code, I get cross table modification!!

I'll try to edit code in _where() function in DB_active_rec.php file to fail if $value == FALSE, I hope this will be enough

[eluser]WanWizard[/eluser]
Why would you want to pass FALSE? MySQL doesn't support a boolean column type anyway. Use a tinyint or enum with values 0 and 1 instead.

[eluser]Fierymind[/eluser]
I do not want to pass FALSE .. due bugs in input, It happen that FALSE is passed to Active Record update or delete statement .. and when this happen, entire table wiped out or get modified.

I mean, all of us have code like this

Code:

$this->db->where('field_name', $var);
$this->db->delete('table_name');

or

Code:

$this->db->where('field_name', $var);
$this->db->update('table_name',$update);

if $var (due bug in code) is FALSE or equal to 0, entire table get wiped!! .. you want to select a row or set of rows .. instead you get all rows selected .. I prefer to get some error rather than get data lose!

am I the only person feel this dangerous ?

[eluser]WanWizard[/eluser]
Bugs in input? You are passing input fields to your database queries without validation?

At the very least cast your variables;

Code:

// make sure $var is passed as a string
$this->db->where('field_name', (string) $var);
$this->db->delete('table_name');

If you validate your data, and test your code properly, these kinds of errors can't happen. And if they do, it happens in development, where you simply restore a table when it gets modified (accident or not).
I think most developers would soon be out of business if code with this quality reaches their customers. "Oops, sorry, bug. No worries, you only lost all your data..."