A bug in Active Record class

[eluser]Unknown[/eluser]
There is a bug in the class CI_DB_active_rec.

For the SQL produced with select_max() / select_min() / select_avg() method, the field (column) name is unquoted.
While this is fine for most column names we use in our database, when I have a column name with keywords like "order", the query will not run, and produce error like that :

Code:

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 'order FROM (`table_name`)' at line 1

SELECT MAX(`order`) AS order FROM (`table_name`)

But I expect the query to be

Code:

SELECT MAX(`order`) AS `order` FROM (`table_name`)

I am using the latest version of CodeIgniter, version 2.1.3.


To solve this problem, I have to modify /system/database/DB_active_rec.php, line 199, and change this :

Code:

$sql = $type.'('.$this->_protect_identifiers(trim($select)).') AS '.$alias;

to this

Code:

$sql = $type.'('.$this->_protect_identifiers(trim($select)).') AS `'.$alias.'`';

[eluser]pickupman[/eluser]
This has been fixed in the 3.0 release which is supposed to released soon. A number of the bugs in 2.1.3 have been updated in the 3.0dev branch. The current changeset has the column alias also use the protect_identifiers wrapping the $alias rather than hard coding them as you have shown.

You may find it helpful to go ahead and update to the latest version available on [url="https://github.com/EllisLab/CodeIgniter"]Github[/url].

[eluser]CroNiX[/eluser]
Its not a big deal if you don't use mysql reserved words, such as "order" for column names....which is bad practice...

[eluser]GrahamDj28[/eluser]
It's bad practice if you use reserved words for column and table names?
I think it's bad practice to give a column or table a name that is unrelated to what it represents.

Lets say you have a catalog with products and orders can be placed, then I will name my table 'order'. I would not name it 'orders' simply because I believe that each record represents a single order, thus the table is named order. Another reason is that my model class name is always identical to my table name. So saving a new record a call new Order. It keeps my code nice and clean and you see exactly what is going on. Not quoting you queries is bad practice if you ask me. And different databases do not always use the same reserved words. But this is only my point of view.