Bug Report

False table prefix in sql select statement
Date:	04/17/2008	Severity:	Minor
Status:	Not a Bug	Reporter:	uk81
Version:	1.6.1
Keywords:	Libraries, Database Class

Description

I set up a normal MySQL Statement (using XAMPP 1.6.0a on Windows XP SP2).

It is a select with a function call DATE_FORMAT.
This function get a format string as 2nd parameter. One string get a automated table-prefix from CI.

My Table Prefix: uk_

Code Sample

$this->db->select("id, DATE_FORMAT(crdate, '".$this->news_conf['news_date']."') crdate, DATE_FORMAT(changedate, '".$this->news_conf['news_date2']."') changedate, title, news, author");
       echo "id, DATE_FORMAT(crdate, '".$this->news_conf['news_date']."') crdate, DATE_FORMAT(changedate, '".$this->news_conf['news_date2']."') changedate, title, news, author";
       
$where = array('hidden' => 0,                        'deleted' => 0);                            
$this->db->order_by("crdate", $order);
$query = $this->db->get_where($this->tbl_name, $where);  
$query = $query->result_array();

Expected Result

Expected is a normal SQL result array (no problems).

Actual Result

The actual result is a MySQL Error 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 ‘’%d.%m.%Y %H:%i’) changedate, `title`, `news`, `author` FROM (`uk_news`) WHERE `’ at line 1

SELECT `id`, DATE_FORMAT(`crdate`, ‘%a %d %b %Y %H:%i’) `crdate`, DATE_FORMAT(`changedate`, uk_’%d.%m.%Y %H:%i’) changedate, `title`, `news`, `author` FROM (`uk_news`) WHERE `hidden` = 0 AND `deleted` = 0 ORDER BY `crdate` DESC

But I do a echo in PHP with the sql-select statement from above.
And I got this output:
id, DATE_FORMAT(crdate, ‘%a %d %b %Y %H:%i’) crdate, DATE_FORMAT(changedate, ‘%d.%m.%Y %H:%i’) changedate, title, news, author

You see, that the output string is fine and there is no table prefix. It is not from a variable it must be set by CI automatically.

Comment on Bug Report

Page 1 of 1 pages

Posted by: Derek Allard on 17 April 2008 1:01pm
	Greetings uk81, I do not see a forum thread associated with this bug report, and it looks like it may not be a bug. Have you first read the bug reporting guidelines and used the Bug Report Forum to get help confirming that this is a bug before reporting? Thanks!
Posted by: uk81 on 24 April 2008 8:07am
	Hi Derek. No, I don´t have report this in the forum so I don´t got help. So I don´t knew until now that I have to open a forum discussion first, before reporting a bug. Sorry for this ... Maybe it can be, that this is no bug. But it is absolutely false, that CI itself put the table-prefix automatically to a normal table-attribute (inside the date_format-function). So if it is a bug or not, there runs something false from inside CI I think.
Posted by: uk81 on 24 April 2008 8:21am
	One more thing: I tested a little bit and find out, that the failure correspond in a way with the dots in the format string of the date_format function. When I use this select statement all works fine: $this->db->select("id, DATE_FORMAT(crdate, ‘“.$this->news_conf[’news_date’]."’) crdate, DATE_FORMAT(changedate, ‘%d %m %Y %H:%i’) changedate, title, news, author");
Posted by: Derek Allard on 24 April 2008 9:00am
	Please start a forum thread to seek help. From the userguide. $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. Until there’s been some discussion over this, I’m labelling it not a bug, but I’ll be happy to re-open if it indeed turns out to be a bug.