Weird backticks behaviour in Active Record in Code

问题:

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

db->select

as

FALSE

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.city, clb_company.state, clb_company.zipcode ) as companyAddress");
$this->db->from($this->_table);
$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(clb_device.name)) desc LIMIT 20

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

Please suggest the workaround.

Gracias

回答1:

Use this line before your query:

$this->db->_protect_identifiers=false;

This will stop adding backticks to the built query.

回答2:

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.

回答3:

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;

回答4:

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.city, clb_company.state, clb_company.zipcode ) as companyAddress",false);
$this->db->from($this->_table);
$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.

回答5:

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).

回答6:

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

回答7:

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

回答8:

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..

Thanks

回答9:

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.