Weird backticks behaviour in Active Record in CodeIgniter 2.0.3

Question

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.

I would have thought the backticks would make no real difference to the query as they'll just encapsulate the table/field names in your query to allow for special characters if you use `echo $this->db->last_query();` and post the code that might allow us to shed more light on it. — simnom, Sep 20 '11 at 09:37
@simnom - it is creating problem, e.g. CONCAT(trim(table1.field1), '/', trim(table2.field2)) is being parsed as CONCAT(trim(table1.field1), `'/'`, trim(table2.field2)) — Jatin Dhoot, Sep 20 '11 at 10:15
@Louis --- have included the code sample in question itself, sorry for the delays — Jatin Dhoot, Sep 28 '11 at 10:57

score 32 · Answer 1 · edited Sep 12 '13 at 20:25

32

Use this line before your query:

$this->db->_protect_identifiers=false;

This will stop adding backticks to the built query.

edited Sep 12 '13 at 20:25

doitlikejustin

6,293
2
40
68

answered Jan 09 '13 at 09:48

Anup_Tripathi

2,817
3
26
37

score 15 · Answer 2 · edited Jun 08 '12 at 12:20

15

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.

edited Jun 08 '12 at 12:20

Bart

19,692
7
68
77

answered Jun 07 '12 at 22:04

Kameliya Ivanova Tincheva

151
1
3

5

This has any impact on security ? – Happy Coder Dec 10 '13 at 07:17

score 7 · Answer 3 · answered Sep 11 '13 at 19:00

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;

score 3 · Answer 4 · answered Sep 28 '11 at 19:35

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.

Louis, I have included the query and error,Please have a look. — Jatin Dhoot, Sep 29 '11 at 08:31
This might be a long shot but have you put the false param on the company info but not on the query that gives the error? (as in do you use concat more than one in this file? - the sample code and error don't match, may just be you using sample code or may not be I'm not sure.) — lsl, Sep 29 '11 at 08:55

score 2 · Answer 5 · answered Sep 20 '11 at 13:59

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

Thanks for he explanation Jakub, real problem occurs when you use some built in functions with comma as separators like I gave the example of CONCAT(field1, '-', field2) and you come up with concat(`field1`, `'-'`, `field2`) — Jatin Dhoot, Sep 21 '11 at 06:39

score 1 · Answer 6 · answered Jun 11 '15 at 14:22

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

score 1 · Answer 7 · answered Sep 29 '11 at 15:14

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

score 0 · Answer 8 · answered Dec 16 '11 at 14:46

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.

score -1 · Answer 9 · answered Oct 01 '11 at 06:13

-1

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

answered Oct 01 '11 at 06:13

Roopa

159
4
11

1

Definitely Roopa, you have opened the door for INJECTIONS, it is not recommended, revert the code to original. – Jatin Dhoot Oct 03 '11 at 05:49

Weird backticks behaviour in Active Record in CodeIgniter 2.0.3

9 Answers9

Linked

Related