Wednesday, September 12, 2012

Protect table and field names with backticks - Solution - Codeignter Active Record

I found a bug with the codeigniter related to Protecting identifiers - I am not sure is it is a bug.

Protecting identifiers means:
In many databases it is advisable to protect table and field names - for example with backticks( ` ) in MySQL. Active Record queries are automatically protected.

In codeigniter it states like, it is protecting the field names and table names with backticks when using active record class. 

In my case it looks like, it is working fine until we use a select statement with the second parameter to false. 

for eg: $this->db->select('select count(*) as visit_count',FALSE);

Here the second parameter is 'FALSE' and from the next sql statements, it will avoid the escaping of field names with back ticks.

Example of sql that escaped with backticks: select `firstname`, `lastname`, `date` from `tablename`

Example of sql that nto escaped with backticks: select firstname, lastname, date from tablename

For solving this issue I have modified the 'select' function in the system/database/DB_active_rec.php

I have just added two lines to the function which is in color and now it is working with out any problems.

function select($select = '*', $escape = NULL)
{
// Set the global value if this was sepecified
$old_val = $this->_protect_identifiers;
if (is_bool($escape))
{
$this->_protect_identifiers = $escape;
}

if (is_string($select))
{
$select = explode(',', $select);
}

foreach ($select as $val)
{
$val = trim($val);

if ($val != '')
{
$this->ar_select[] = $val;

if ($this->ar_caching === TRUE)
{
$this->ar_cache_select[] = $val;
$this->ar_cache_exists[] = 'select';
}
}
}
$this->_protect_identifiers = $old_val;
return $this;
}