CI 1.7.1: No escaping of table name - 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: CI 1.7.1: No escaping of table name (/showthread.php?tid=17319) |
CI 1.7.1: No escaping of table name - El Forum - 04-01-2009 [eluser]davidbehler[/eluser] I do the following: Code: $this->db->select('*'); Code: SELECT *, (SELECT COUNT(*) FROM user_group WHERE user_group_group_id = group_id) AS user_count FROM (group) ORDER BY group_name asc That produces an error caused by the not escaped table name 'group'. BUT as soon as I leave out the custom select part like this: Code: $this->db->select('*'); Code: SELECT * FROM (`group`) ORDER BY `group_name` asc As you can see in my second example the table name is escaped just as it should be. Am I missing something or is this a bug caused by the 'do not escape' parameter of the select method? CI 1.7.1: No escaping of table name - El Forum - 04-13-2009 [eluser]Tim Brownlaw[/eluser] I was thinking that the FALSE in the Select might be getting remembered. I've not looked into the code on this, I was just intrigued by your problem. So I tried an experiment and found that you needed to have the last select statement with 'TRUE' after any preceding FALSE settings. I've only tried this with the code provided in this post. Like this... Code: $this->db->select('(SELECT COUNT(*) FROM user_group WHERE user_group_group_id = group_id) AS user_count', FALSE); Leaving TRUE out results in the same 'bad' output already seen.So it's not switching back by default. So this produces Code: SELECT (SELECT COUNT(*) FROM user_group WHERE user_group_group_id = group_id) AS user_count, * FROM (`group`) ORDER BY `group_name` asc As you can see, group_name is now 'ticked'. So next step is this... Code: $this->db->select('(SELECT COUNT(*) FROM user_group WHERE user_group_group_id = group_id) AS user_count', FALSE); The output becomes Code: SELECT (SELECT COUNT(*) FROM user_group WHERE user_group_group_id = group_id) AS user_count, * FROM (`group`) ORDER BY group_name asc Which is what you were after. So there is a 'quirk' as you noted. Disabling backticks in the select $this->db->select turns them off for all statements afterwards and you need to force them back on in another $this->db->select but then everything after that gets backticked. I'd be curious to know if you get any further as the SQL itself has errors in it. The reason you need the backticks on the tablename group is because it is a keyword, so MySQL let's you get away with `group` so it's ignored. That's a clue as to why the SQL statement itself won't work. Cheers Tim CI 1.7.1: No escaping of table name - El Forum - 04-13-2009 [eluser]Tim Brownlaw[/eluser] I'm probably way off track here but I can't resist. Code: SELECT g.*,count(u.id) as user_count FROM `group` g JOIN user_group u ON u.group_id=g.id GROUP BY u.group_id; Using the above on the following tables... Code: CREATE TABLE `group` ( Code: CREATE TABLE `user_group` ( Code: INSERT INTO `group` (name) values ('Hockey'),('Soccer'),('IceSkating'); Results in Code: id name user_count Now I can get to bed CI 1.7.1: No escaping of table name - El Forum - 04-13-2009 [eluser]davidbehler[/eluser] Haha, yeah. That's pretty much how I solved the problem. But the reason for this post is the bug that you and I described and even though one can rewrite the query to not use the sub select anymore, the bug stays |