Database class not escaping reserved words in action queries

Database class not escaping reserved words in action queries - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Database class not escaping reserved words in action queries (/showthread.php?tid=13835)

Database class not escaping reserved words in action queries - El Forum - 12-08-2008

[eluser]mattalexx[/eluser]
The database class isn't escaping reserved words in insert or update statements.

To recreate, run this SQL in MySQL:

Code:
CREATE TABLE test (

`key` CHAR(3)

);

Then run this from a controller:

Code:
$this->db->insert('test', array('key' => 'foo'));

You should get this error:

Code:
A Database Error Occurred

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 'key) VALUES ('foo')' at line 1

INSERT INTO test (key) VALUES ('foo')

It's the same with UPDATE:

Code:
$this->db->insert('test', array('key' => 'foo'));

Code:
A Database Error Occurred

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 'key = 'foo'' at line 1

UPDATE test SET key = 'foo'

Database class not escaping reserved words in action queries - El Forum - 12-08-2008

[eluser]xwero[/eluser]
That is strange because the $_reserved_identifiers array in the DB_driver only contains the value * and mysql_driver doesn't contain additional reserved_identifiers.

I tested it and i was able to insert and update. Do you have an $escape boolean that is global? that is the only way i can think of how this bug could occur.

Database class not escaping reserved words in action queries - El Forum - 12-09-2008

[eluser]mattalexx[/eluser]
[quote author="xwero" date="1228754614"]That is strange because the $_reserved_identifiers array in the DB_driver only contains the value * and mysql_driver doesn't contain additional reserved_identifiers.

I tested it and i was able to insert and update. Do you have an $escape boolean that is global? that is the only way i can think of how this bug could occur.[/quote]

As it turns out, this post never belonged under "Bug Reports". In a query that happens earlier in the script, I was calling CI_DB_active_record: Confused

elect() with the second parameter ($escape) set to true. This sets CI_DB_driver::_protect_identifiers to true, which was screwing up all later queries. To fix, I changed this:

Code:
$values = array();

$values['key'] = $this->key;

$this->db->insert('table', $values);

... to this:

Code:
$values = array();

$values['key'] = $this->key;

$this->db->_protect_identifiers = TRUE;

$this->db->insert('table', $values);

Not the nicest solution, I will admit, but I'm onto the next thing.