All identifiers (table names, column names) are now quoted with backticks

2 files changed, 47 insertions, 37 deletions

diff --git a/idiorm.php b/idiorm.php
index 28d0703..dd92ef5 100644
--- a/idiorm.php
+++ b/idiorm.php
@@ -464,9 +464,10 @@
          */
         protected function _build_select_start() {
             if ($this->_find_type === self::COUNT) {
-                return 'SELECT COUNT(*) AS count FROM ' . $this->_table_name;
+                $count_column = $this->_quote_identifier('count');
+                return "SELECT COUNT(*) AS $count_column FROM " . $this->_quote_identifier($this->_table_name);
             } else {
-                return 'SELECT * FROM ' . $this->_table_name;
+                return 'SELECT * FROM ' . $this->_quote_identifier($this->_table_name);
             }
         }
 
@@ -490,7 +491,7 @@
             $where_clauses = array();
             while($where = array_shift($this->_where)) {
                 $where_clauses[] = join(" ", array(
-                    $where[self::WHERE_COLUMN_NAME],
+                    $this->_quote_identifier($where[self::WHERE_COLUMN_NAME]),
                     $where[self::WHERE_OPERATOR],
                     '?'
                 ));
@@ -508,7 +509,7 @@
             }
             $order_by = array();
             foreach ($this->_order_by as $order) {
-                $order_by[] = $order[self::ORDER_BY_COLUMN_NAME] . " " . $order[self::ORDER_BY_ORDERING];
+                $order_by[] = $this->_quote_identifier($order[self::ORDER_BY_COLUMN_NAME]) . " " . $order[self::ORDER_BY_ORDERING];
             }
             return "ORDER BY " . join(", ", $order_by);
         }
@@ -551,6 +552,14 @@
         }
 
         /**
+         * Quote a string that is used as an identifier
+         * (table names, column names etc).
+         */
+        protected function _quote_identifier($identifier) {
+            return "`$identifier`";
+        }
+
+        /**
          * Execute the SELECT query that has been built up by chaining methods
          * on this class. Return the executed PDOStatement object.
          */
@@ -635,15 +644,15 @@
          */
         protected function _build_update() {
             $query = array();
-            $query[] = "UPDATE {$this->_table_name} SET";
+            $query[] = "UPDATE {$this->_quote_identifier($this->_table_name)} SET";
 
             $field_list = array();
             foreach ($this->_dirty_fields as $key => $value) {
-                $field_list[] = "$key = ?";
+                $field_list[] = "{$this->_quote_identifier($key)} = ?";
             }
             $query[] = join(", ", $field_list);
             $query[] = "WHERE";
-            $query[] = $this->_get_id_column_name();
+            $query[] = $this->_quote_identifier($this->_get_id_column_name());
             $query[] = "= ?";
             return join(" ", $query);
         }
@@ -653,8 +662,9 @@
          */
         protected function _build_insert() {
             $query[] = "INSERT INTO";
-            $query[] = $this->_table_name;
-            $query[] = "(" . join(", ", array_keys($this->_dirty_fields)) . ")";
+            $query[] = $this->_quote_identifier($this->_table_name);
+            $field_list = array_map(array($this, '_quote_identifier'), array_keys($this->_dirty_fields));
+            $query[] = "(" . join(", ", $field_list) . ")";
             $query[] = "VALUES";
 
             $placeholders = array();
@@ -673,9 +683,9 @@
         public function delete() {
             $query = join(" ", array(
                 "DELETE FROM",
-                $this->_table_name,
+                $this->_quote_identifier($this->_table_name),
                 "WHERE",
-                $this->_get_id_column_name(),
+                $this->_quote_identifier($this->_get_id_column_name()),
                 "= ?",
             ));
             self::_setup_db();
diff --git a/test/test_queries.php b/test/test_queries.php
index 8a281ee..6bd22b9 100644
--- a/test/test_queries.php
+++ b/test/test_queries.php
@@ -15,95 +15,95 @@
     Tester::set_db($db);
 
     ORM::for_table('widget')->find_many();
-    $expected = 'SELECT * FROM widget';
+    $expected = 'SELECT * FROM `widget`';
     Tester::check_equal("Basic unfiltered query", $expected);
 
     ORM::for_table('widget')->find_one(5);
-    $expected = 'SELECT * FROM widget WHERE id = "5"';
+    $expected = 'SELECT * FROM `widget` WHERE `id` = "5"';
     Tester::check_equal("Filtering on ID", $expected);
 
     ORM::for_table('widget')->count();
-    $expected = 'SELECT COUNT(*) AS count FROM widget';
+    $expected = 'SELECT COUNT(*) AS `count` FROM `widget`';
     Tester::check_equal("COUNT query", $expected);
 
     ORM::for_table('widget')->where('name', 'Fred')->find_one();
-    $expected = 'SELECT * FROM widget WHERE name = "Fred"';
+    $expected = 'SELECT * FROM `widget` WHERE `name` = "Fred"';
     Tester::check_equal("Single where clause", $expected);
 
     ORM::for_table('widget')->where('name', 'Fred')->where('age', 10)->find_one();
-    $expected = 'SELECT * FROM widget WHERE name = "Fred" AND age = "10"';
+    $expected = 'SELECT * FROM `widget` WHERE `name` = "Fred" AND `age` = "10"';
     Tester::check_equal("Multiple WHERE clauses", $expected);
 
     ORM::for_table('widget')->where_like('name', '%Fred%')->find_one();
-    $expected = 'SELECT * FROM widget WHERE name LIKE "%Fred%"';
+    $expected = 'SELECT * FROM `widget` WHERE `name` LIKE "%Fred%"';
     Tester::check_equal("where_like method", $expected);
 
     ORM::for_table('widget')->where_not_like('name', '%Fred%')->find_one();
-    $expected = 'SELECT * FROM widget WHERE name NOT LIKE "%Fred%"';
+    $expected = 'SELECT * FROM `widget` WHERE `name` NOT LIKE "%Fred%"';
     Tester::check_equal("where_not_like method", $expected);
 
     ORM::for_table('widget')->limit(5)->find_one();
-    $expected = 'SELECT * FROM widget LIMIT 5';
+    $expected = 'SELECT * FROM `widget` LIMIT 5';
     Tester::check_equal("LIMIT clause", $expected);
 
     ORM::for_table('widget')->limit(5)->offset(5)->find_one();
-    $expected = 'SELECT * FROM widget LIMIT 5 OFFSET 5';
+    $expected = 'SELECT * FROM `widget` LIMIT 5 OFFSET 5';
     Tester::check_equal("LIMIT and OFFSET clause", $expected);
 
     ORM::for_table('widget')->order_by_desc('name')->find_one();
-    $expected = 'SELECT * FROM widget ORDER BY name DESC';
+    $expected = 'SELECT * FROM `widget` ORDER BY `name` DESC';
     Tester::check_equal("ORDER BY DESC", $expected);
 
     ORM::for_table('widget')->order_by_asc('name')->find_one();
-    $expected = 'SELECT * FROM widget ORDER BY name ASC';
+    $expected = 'SELECT * FROM `widget` ORDER BY `name` ASC';
     Tester::check_equal("ORDER BY ASC", $expected);
 
     ORM::for_table('widget')->order_by_asc('name')->order_by_desc('age')->find_one();
-    $expected = 'SELECT * FROM widget ORDER BY name ASC, age DESC';
+    $expected = 'SELECT * FROM `widget` ORDER BY `name` ASC, `age` DESC';
     Tester::check_equal("Multiple ORDER BY", $expected);
 
     ORM::for_table('widget')->where('name', 'Fred')->limit(5)->offset(5)->order_by_asc('name')->find_many();
-    $expected = 'SELECT * FROM widget WHERE name = "Fred" ORDER BY name ASC LIMIT 5 OFFSET 5';
+    $expected = 'SELECT * FROM `widget` WHERE `name` = "Fred" ORDER BY `name` ASC LIMIT 5 OFFSET 5';
     Tester::check_equal("Complex query", $expected);
 
     ORM::for_table('widget')->where_lt('age', 10)->where_gt('age', 5)->find_many();
-    $expected = 'SELECT * FROM widget WHERE age < "10" AND age > "5"';
+    $expected = 'SELECT * FROM `widget` WHERE `age` < "10" AND `age` > "5"';
     Tester::check_equal("Less than and greater than", $expected);
 
     ORM::for_table('widget')->where_lte('age', 10)->where_gte('age', 5)->find_many();
-    $expected = 'SELECT * FROM widget WHERE age <= "10" AND age >= "5"';
+    $expected = 'SELECT * FROM `widget` WHERE `age` <= "10" AND `age` >= "5"';
     Tester::check_equal("Less than or equal and greater than or equal", $expected);
 
-    ORM::for_table('widget')->where_raw('name = ? AND (age = ? OR age = ?)', array('Fred', 5, 10))->find_many();
-    $expected = 'SELECT * FROM widget WHERE name = "Fred" AND (age = "5" OR age = "10")';
+    ORM::for_table('widget')->where_raw('`name` = ? AND (`age` = ? OR `age` = ?)', array('Fred', 5, 10))->find_many();
+    $expected = 'SELECT * FROM `widget` WHERE `name` = "Fred" AND (`age` = "5" OR `age` = "10")';
     Tester::check_equal("Raw WHERE clause", $expected);
 
-    ORM::for_table('widget')->raw_query('SELECT w.* FROM widget w WHERE name = ? AND age = ?', array('Fred', 5))->find_many();
-    $expected = 'SELECT w.* FROM widget w WHERE name = "Fred" AND age = "5"';
+    ORM::for_table('widget')->raw_query('SELECT `w`.* FROM `widget` w WHERE `name` = ? AND `age` = ?', array('Fred', 5))->find_many();
+    $expected = 'SELECT `w`.* FROM `widget` w WHERE `name` = "Fred" AND `age` = "5"';
     Tester::check_equal("Raw query", $expected);
 
     $widget = ORM::for_table('widget')->create();
     $widget->name = "Fred";
     $widget->age = 10;
     $widget->save();
-    $expected = 'INSERT INTO widget (name, age) VALUES ("Fred", "10")';
+    $expected = 'INSERT INTO `widget` (`name`, `age`) VALUES ("Fred", "10")';
     Tester::check_equal("Insert data", $expected);
 
     $widget = ORM::for_table('widget')->find_one(1);
     $widget->name = "Fred";
     $widget->age = 10;
     $widget->save();
-    $expected = 'UPDATE widget SET name = "Fred", age = "10" WHERE id = "1"';
+    $expected = 'UPDATE `widget` SET `name` = "Fred", `age` = "10" WHERE `id` = "1"';
     Tester::check_equal("Update data", $expected);
 
     $widget = ORM::for_table('widget')->find_one(1);
     $widget->delete();
-    $expected = 'DELETE FROM widget WHERE id = "1"';
+    $expected = 'DELETE FROM `widget` WHERE `id` = "1"';
     Tester::check_equal("Delete data", $expected);
 
     ORM::configure('id_column', 'primary_key');
     ORM::for_table('widget')->find_one(5);
-    $expected = 'SELECT * FROM widget WHERE primary_key = "5"';
+    $expected = 'SELECT * FROM `widget` WHERE `primary_key` = "5"';
     Tester::check_equal("Setting: id_column", $expected);
 
     ORM::configure('id_column_overrides', array(
@@ -112,15 +112,15 @@
     ));
 
     ORM::for_table('widget')->find_one(5);
-    $expected = 'SELECT * FROM widget WHERE widget_id = "5"';
+    $expected = 'SELECT * FROM `widget` WHERE `widget_id` = "5"';
     Tester::check_equal("Setting: id_column_overrides, first test", $expected);
 
     ORM::for_table('widget_handle')->find_one(5);
-    $expected = 'SELECT * FROM widget_handle WHERE widget_handle_id = "5"';
+    $expected = 'SELECT * FROM `widget_handle` WHERE `widget_handle_id` = "5"';
     Tester::check_equal("Setting: id_column_overrides, second test", $expected);
 
     ORM::for_table('widget_nozzle')->find_one(5);
-    $expected = 'SELECT * FROM widget_nozzle WHERE primary_key = "5"';
+    $expected = 'SELECT * FROM `widget_nozzle` WHERE `primary_key` = "5"';
     Tester::check_equal("Setting: id_column_overrides, third test", $expected);
 
     Tester::report();