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();