Bug Report

MySQL Driver - problems with numeric fields (backticks)
Date:	05/14/2007	Severity:	Critical
Status:	Resolved	Reporter:	Thadeus
Version:	1.5.3
Keywords:	Libraries, Database Class
Forum Thread:	http://codeigniter.com/forums/viewthread/47927/

Description

many active record functions (if not all active record functions) don’t escape the fieldnames properly, which often produces sql errors when using numeric fieldnames, which have to be escaped (when iterating/computing its generally a good idea to use numeric field names, so this should be supported by codeigniter, too)

this bug applies to the query helper functions (http://codeigniter.com/user_guide/database/helpers.html), too and is somewhat connected with this bug http://codeigniter.com/bug_tracker/bug/1818/ but still no duplicate, as 1818 is more about “empty values” than escaping numeric fieldnames.

Code Sample

$somearray:
(
    [id] => 1
    [0] => 1
    [1] => 2
    [2] => 3
    [3] => 
    [4] => 
    [5] => 
)

for example inserting via active record with:
$this->db->insert('links', $somearray); 

Expected Result

INSERT INTO `chains` (`id`, `0`, `1`, `2`, `3`, `4`, `5`) VALUES (‘1’, ‘1’, ‘2’, 3, NULL, NULL, NULL)

Actual Result

INSERT INTO chains (id, 0, 1, 2, 3, 4, 5) VALUES (‘1’, ‘1’, ‘2’, 3, NULL, NULL, NULL)

Comment on Bug Report

Page 1 of 1 pages

Posted by: coolfactor on 14 May 2007 5:21pm
	It’s not necessary (or helpful) to file the same bug report more than once. Please refrain.
Posted by: Thadeus on 14 May 2007 5:42pm
	this is NOT the same bug as http://codeigniter.com/bug_tracker/bug/1818/ 1818 is concerned with NULL value translation this one is concerned with escaping.
Posted by: coolfactor on 14 May 2007 5:46pm
	Gotcha. Sorry for jumping the gun. Numeric field names are strange beasts, indeed.
Posted by: Thadeus on 14 May 2007 6:29pm
	but infact this bug could be simply resolved by generally esaping field names. this wouldnt be harmfull for alphanumeric fieldnames. phpmyadmin for example escapes everything. (thats why it “just works (TM)”
Posted by: Thadeus on 15 May 2007 4:08am
	in the forum thread mentioned in this bug report ppl have the feeling, that the reason for this bug could be in the mysql driver.
Posted by: Scott Severance on 4 August 2007 11:02pm
	Another area where this problem crops up is when the field name is a reserved word. For example, I have a configuration table with the fields `key` and `value`. Key is a reserved word, so if it isn’t escaped you get really-hard-to-debug errors. I’ve written a mostly complete fix for this. Basically, my fix runs each field name in the active record methods through a method that escapes it if necessary. Since $this->db->join() is more complex than the others, and since I haven’t needed the fix to apply to that method, I haven’t tried fixing it. Also, for some reason, in some ORDER BY clauses, some names are escaped while others aren’t. I don’t know why. I’d upload my fix, but this bug tracker doesn’t seem to have any way to submit fixes. So, if you want it, send me an e-mail: http://codeigniter.com/forums/member/41333/
Posted by: dieter on 2 January 2008 9:04am
	What about the fix in http://codeigniter.com/forums/viewreply/230956/ ?
Posted by: Scott Severance on 2 January 2008 9:02pm
	I have a patch which is more complete than the one dieter referenced above. As observed above by Thadeus, phpMyAdmin always escapes column names, so clearly there is no harm to that. I wrote this patch a long time ago, so I don’t remember if it affects drivers other than MySQL. See more details in my previous post. I’d attach my patch, but there’s no way to attach files. I’d post it inline, but comments are limited to 6,000 characters. If the devs want to see my patch, they’ll have to make a way for me to post it. (Keep in mind that I’m not planning on keeping my copy of the patch around for too long.)