Weird backticks behaviour in Active Record in CodeIgniter 2.0.3

Previously my all queries were running fine in CI version 2.0 but when I upgraded to 2.0.3 some of my SELECT queries were broken.

CI is adding backticks (``) automatically, but in older version its running as it is.

CI user manual have instructed to add second parameter in




but still it's not working.

Code is as following:

class Company_model extends MY_Model


$this->db->select(' count('.$fieldname. ') as num_stations');
$this->db->select(" CONCAT_WS(',', clb_company.address1, clb_company.address2,, clb_company.state, clb_company.zipcode ) as companyAddress");
$this->db->join($this->_table_device, $fieldname1. " = ".  $fieldname2, 'LEFT');
$this->db->where($blablafield , '0');

The error is as follows:

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 
'FROM (`clb_device`) JOIN `clb_company` ON `clb_company`.`id` = `clb_device`.`com' at line 2

SELECT `clb_device`.`id` as deviceId, `clb_pricing_specifications`.`name` as pricingSpecName, `clb_company`.`name` as companyName, `clb_device`.`mac_address` as deviceMacAddress, 
`clb_device`.`reseller_model_number` as deviceModelNumber, `clb_pricing_spec_grouping`.`pricing_master_spec_id` as pricingSpecId, `clb_device`.`address` as deviceAddress, 
`clb_device`.`is_home` as deviceIsHomeCharger, CONCAT(clb_company.portal_line1, `'/'`, `clb_device`.`name)` as deviceDisplayName FROM (`clb_device`) JOIN `clb_company` 
ON `clb_company`.`id` = `clb_device`.`company_id` LEFT JOIN `clb_pricing_group_devices` ON `clb_device`.`id` = `clb_pricing_group_devices`.`device_id` and clb_pricing_group_devices.is_active = 1 
LEFT JOIN `clb_pricing_spec_grouping` ON `clb_pricing_group_devices`.`pricing_spec_id` = `clb_pricing_spec_grouping`.`pricing_master_spec_id` LEFT JOIN `clb_pricing_specifications` ON 
`clb_pricing_spec_grouping`.`pricing_spec_id` = `clb_pricing_specifications`.`id` WHERE clb_company.vendor_id is not null AND cast(substr(clb_devi
ce.software_version, 1, 3) as decimal(2,1)) > 2.0 AND clb_device.device_state > 0 GROUP BY `clb_device`.`id` ORDER BY CONCAT(trim(clb_company.portal_line1), `'/'`, trim( desc LIMIT 20

Have a look at CONCAT(trim(clb_company.portal_line1), `'/'`, trim(

Please suggest the workaround.


Use this line before your query:


This will stop adding backticks to the built query.

The solution is very simple: In the database configuration file (./application/config/database.php) add a new element to array with default settings.

$db['default']['_protect_identifiers']= FALSE;

This solution is working for me and more elegant and professional.

All other answers are really old, this one works with CI 2.1.4

// set this to false so that _protect_identifiers skips escaping:
$this->db->_protect_identifiers = FALSE;

// your order_by line:
$this -> db -> order_by('FIELD ( products.country_id, 2, 0, 1 )');

// important to set this back to TRUE or ALL of your queries from now on will be non-escaped:
$this->db->_protect_identifiers = TRUE;

class Company_model extends MY_Model


$this->db->select(" count('$fieldname') as num_stations",false);
$this->db->select(" CONCAT_WS(',', clb_company.address1, clb_company.address2,, clb_company.state, clb_company.zipcode ) as companyAddress",false);
$this->db->join($this->_table_device, $fieldname1. " = ".  $fieldname2, 'LEFT');
$this->db->where($blablafield , '0');

The false you were talking about is what is needed, can you try the code above and copy and paste to us the output of

echo $this->db->last_query();

This will show us what the DB class is creating exactly and we can see whats working / what isn't. It may be something else (you haven't given the error from that is generated sometimes sql errors can be misleading.)

From the docs:

$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.

CI will only protect your ACTIVE RECORD calls, so if you are running $this->db->query(); you will be fine, and based on the notes you should be safe with AD calls like so to disable backticks (not sure why you say they don't work, but I don't see your full code, so I can't be sure)

$this->db->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4') AS amount_paid', FALSE);
$query = $this->db->get('mytable');

make sure FALSE is without single quotes (makes it a string), and it might not validate (not tested by me).

I think you should check DB_driver.php file, there is a variable named as protect_identifier, the point is when you will check with older version of CI, you will see that there is a condition which is missing in new version,escape variable which is checked for nullability, paste that condition from older version and you will be OK

CI_DB_active_record::where() has a third param for escaping, this has worked better for me than switching on and off CI_DB_driver::_protect_identifiers

public function where($key, $value = NULL, $escape = TRUE)

Not sure what CI version this was added in.

HTH someone

I just read a simple solution for this...

I changed the value of var $_escape_char (system/database/drivers/mysql/mysql_driver.php, line 36..

It was

var $_escape_char = '`';

Changed to

var $_escape_char = ' ';

and now it works... But i am affraid if I made any security issues..


Here's a trick that worked for me. Replace this line

$this->db->join($this->_table_device, $fieldname1. " = ".  $fieldname2, 'LEFT');

with this:

$this->db->join($this->_table_device, $fieldname1. " IN(".  $fieldname2 .")", 'LEFT');

this will prevent CI from escaping your field. It's not ideal but it's better than the alternatives.

Need Your Help

What do the "Not optimized" warnings in the Chrome Profiler mean?

javascript google-chrome optimization google-chrome-devtools developer-tools

When I use the Developer Tools in Chrome to collect JavaScript CPU Profiles, I am getting two mysterious warnings on functions:

Deploying on Heroku with Capistrano?

ruby-on-rails heroku capistrano

I'm still getting used to the Rails development ecosystem. Recently I learned how to use capistrano for deploying to my servers using apache and passenger, and I love how I can put a rather complex...