MySQL Matches INT 0 to all for VarChar

I found a strange bug/feature in MySQL which means that if you do a select statement using a INT against a VARCHAR column in matches all of the values in the column which do not start with a number. MySQL claim this is a normal.

So if I had a table like:

CREATE TABLE tasks (
  id int(11) unsigned NOT NULL auto_increment,
  whom varchar(100) default NULL,
  task varchar(100) default NULL
  created_date timestamp NULL default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

And then had a query:

SELECT id FROM tasks WHERE whom="Dale" AND task=0

You would get a match.

So you are saying right now, well you should escape your query, you are right though consider this:

CodeIgniter active db select:

$this->db->select('id');
$this->db->from('tasks');
$this->db->where(array('whom'=>$this->uri->segment(2), 'task'=>$this->uri->segment(3)));
$query=$this->db->get();
echo $this->db->last_query();

And there is the hole in CodeIgniter exists if the segment in the URI does not exist, as it returns 0 as an INT if say someone puts in the URL http://example.com/tasks/dale

The solution is to explicitly convert the URI segments into strings:

$whom = (string) $this->uri->segment(2);
$task = (string) $this->uri->segment(3);
$this->db->select('id');
$this->db->from('tasks');
$this->db->where(array('whom'=>$whom, 'task'=>$whom));
$query=$this->db->get();
echo $this->db->last_query();