backticks in SQL not accepted by PostgreSQL

[gmaruzz]

Hello,

postgresql schema in many branches contains one or more backtick (`) in SQL code

at least psql/pgsql 9.4 do not accept backtick in SQL (maybe later pgsql versions do accept?)

pgsql 9.4 may be relevant because is the last pgsql version with opensource BDR master-master replication

eg, in ocp 9.3.3 branch:

diff --git a/config/db_schema.pgsql b/config/db_schema.pgsql
index b7a556bd..16114f2f 100644
--- a/config/db_schema.pgsql
+++ b/config/db_schema.pgsql
@@ -115,7 +115,7 @@ CREATE TABLE ocp_dashboard (
   positions text DEFAULT NULL
 );
 
-INSERT INTO `ocp_dashboard` VALUES (1,'Default','{\"panel_20_widget_1\":\"{\\\"widget_title\\\":\\\"Users\\\",\\\"widget_box\\\":\\\"1\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"registered_users_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_20_widget_1\\\"}\",\"panel_20_widget_2\":\"{\\\"widget_name\\\":\\\"CDR\\\",\\\"widget_refresh\\\":\\\"30\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_
type\\\":\\\"cdr_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_20_widget_2\\\"}\",\"panel_20_widget_3\":\"{\\\"widget_title\\\":\\\"PKG Usage\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_warning\\\":\\\"50\\\",\\\"widget_critical\\\":\\\"75\\\",\\\"widget_refresh\\\":\\\"\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"pkg_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_20_widg
et_3\\\"}\",\"panel_20_widget_4\":\"{\\\"widget_title\\\":\\\"Shared memory\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_warning\\\":\\\"50\\\",\\\"widget_critical\\\":\\\"75\\\",\\\"widget_refresh\\\":\\\"60\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"shmem_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_20_widget_4\\\"}\",\"panel_20_widget_6\":\"{\\\"widget_name\\\":\\\"Dispatching\\\",\\\"wi
dget_box\\\":\\\"1\\\",\\\"widget_partition\\\":\\\"default\\\",\\\"widget_set\\\":\\\"\\\",\\\"widget_refresh\\\":\\\"30\\\",\\\"editwidget\\\":\\\"Edit\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_type\\\":\\\"dispatching_widget\\\",\\\"widget_id\\\":\\\"panel_20_widget_6\\\"}\",\"panel_20_widget_7\":\"{\\\"widget_name\\\":\\\"Dynamic Routing\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_partition\\\":\\\"\\\",\\\"widget_refres
h\\\":\\\"30\\\",\\\"editwidget\\\":\\\"Edit\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_type\\\":\\\"gateways_widget\\\",\\\"widget_id\\\":\\\"panel_20_widget_7\\\"}\",\"panel_20_widget_11\":\"{\\\"widget_name\\\":\\\"RTPProxy\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_set\\\":\\\"0\\\",\\\"widget_refresh\\\":\\\"60\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"rtpproxy_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"
widget_id\\\":\\\"panel_20_widget_11\\\"}\",\"panel_23_widget_15\":\"{\\\"widget_name\\\":\\\"RTPEngine\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_set\\\":\\\"0\\\",\\\"widget_refresh\\\":\\\"60\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"rtpengine_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_23_widget_15\\\"}\"}',1,'[{\"id\":\"panel_20_widget_1\",\"col\":6,\"row\":1,\"size_x\":2,\"size_y\
":3},{\"id\":\"panel_20_widget_2\",\"col\":8,\"row\":1,\"size_x\":2,\"size_y\":3},{\"id\":\"panel_20_widget_3\",\"col\":4,\"row\":1,\"size_x\":2,\"size_y\":3},{\"id\":\"panel_20_widget_4\",\"col\":2,\"row\":1,\"size_x\":2,\"size_y\":3},{\"id\":\"panel_20_widget_6\",\"col\":2,\"row\":6,\"size_x\":2,\"size_y\":2},{\"id\":\"panel_20_widget_7\",\"col\":4,\"row\":6,\"size_x\":2,\"size_y\":2},{\"id\":\"panel_20_widget_11\",\"col\":
6,\"row\":6,\"size_x\":2,\"size_y\":2},{\"id\":\"panel_23_widget_15\",\"col\":8,\"row\":6,\"size_x\":2,\"size_y\":2}]');
+INSERT INTO ocp_dashboard VALUES (1,'Default','{\"panel_20_widget_1\":\"{\\\"widget_title\\\":\\\"Users\\\",\\\"widget_box\\\":\\\"1\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"registered_users_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_20_widget_1\\\"}\",\"panel_20_widget_2\":\"{\\\"widget_name\\\":\\\"CDR\\\",\\\"widget_refresh\\\":\\\"30\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_ty
pe\\\":\\\"cdr_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_20_widget_2\\\"}\",\"panel_20_widget_3\":\"{\\\"widget_title\\\":\\\"PKG Usage\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_warning\\\":\\\"50\\\",\\\"widget_critical\\\":\\\"75\\\",\\\"widget_refresh\\\":\\\"\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"pkg_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_20_widget
_3\\\"}\",\"panel_20_widget_4\":\"{\\\"widget_title\\\":\\\"Shared memory\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_warning\\\":\\\"50\\\",\\\"widget_critical\\\":\\\"75\\\",\\\"widget_refresh\\\":\\\"60\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"shmem_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_20_widget_4\\\"}\",\"panel_20_widget_6\":\"{\\\"widget_name\\\":\\\"Dispatching\\\",\\\"widg
et_box\\\":\\\"1\\\",\\\"widget_partition\\\":\\\"default\\\",\\\"widget_set\\\":\\\"\\\",\\\"widget_refresh\\\":\\\"30\\\",\\\"editwidget\\\":\\\"Edit\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_type\\\":\\\"dispatching_widget\\\",\\\"widget_id\\\":\\\"panel_20_widget_6\\\"}\",\"panel_20_widget_7\":\"{\\\"widget_name\\\":\\\"Dynamic Routing\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_partition\\\":\\\"\\\",\\\"widget_refresh\
\\":\\\"30\\\",\\\"editwidget\\\":\\\"Edit\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_type\\\":\\\"gateways_widget\\\",\\\"widget_id\\\":\\\"panel_20_widget_7\\\"}\",\"panel_20_widget_11\":\"{\\\"widget_name\\\":\\\"RTPProxy\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_set\\\":\\\"0\\\",\\\"widget_refresh\\\":\\\"60\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"rtpproxy_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"wi
dget_id\\\":\\\"panel_20_widget_11\\\"}\",\"panel_23_widget_15\":\"{\\\"widget_name\\\":\\\"RTPEngine\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_set\\\":\\\"0\\\",\\\"widget_refresh\\\":\\\"60\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"rtpengine_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_23_widget_15\\\"}\"}',1,'[{\"id\":\"panel_20_widget_1\",\"col\":6,\"row\":1,\"size_x\":2,\"size_y\":
3},{\"id\":\"panel_20_widget_2\",\"col\":8,\"row\":1,\"size_x\":2,\"size_y\":3},{\"id\":\"panel_20_widget_3\",\"col\":4,\"row\":1,\"size_x\":2,\"size_y\":3},{\"id\":\"panel_20_widget_4\",\"col\":2,\"row\":1,\"size_x\":2,\"size_y\":3},{\"id\":\"panel_20_widget_6\",\"col\":2,\"row\":6,\"size_x\":2,\"size_y\":2},{\"id\":\"panel_20_widget_7\",\"col\":4,\"row\":6,\"size_x\":2,\"size_y\":2},{\"id\":\"panel_20_widget_11\",\"col\":6,
\"row\":6,\"size_x\":2,\"size_y\":2},{\"id\":\"panel_23_widget_15\",\"col\":8,\"row\":6,\"size_x\":2,\"size_y\":2}]');
 
 -- --------------------------------------------------------

backticks in comments are OK

[liviuchircu]

Hey @gmaruzz ! It seems there is already some work going on in this area, with PR #293 and a proposed alternate fix in branch (bugfix/fix-dashboard-postgres). The idea is to use " instead of backtick for identifier quoting on some really "special" (stupid?) named columns, like "name", in order to stay compatible with Postgres. But this also requires a sql_mode switch to ANSI_QUOTES, at least in MySQL. So I wanted a confirmation of the fix before I merge it.

Now, your report seems to be only on the schema file config/db_schema.pgsql -- perhaps we could make use of " there as well, and merge together with the fix-dashboard-postgres branch.

[gmaruzz]

ciao @liviuchircu , as I was progressing in playing with ocp, I was now about to report about the use of backticks in SQL code inside of PHP code :)
yep, using quotes (") instead of backticks (`) works

in some place in PHP code, quotes (") needs to be escaped (\") when inside strings delimited by.... quotes :))

[gmaruzz]

I believe the problematic column is "order", as per SQL keyword ORDER BY
not sure about "name" column, in 9.4 seems to be accepted
anyway, just playing around :)

[gmaruzz]

I will prepare a more complete patch, because there are other errors (even PHP errors) at least in 9.3.3 branch

[gmaruzz]

9.3.3:
there was one only closing parenthesis lacking, in tviewer

patch as follow:

diff --git a/config/db_schema.pgsql b/config/db_schema.pgsql
index b7a556bd..3dd37179 100644
--- a/config/db_schema.pgsql
+++ b/config/db_schema.pgsql
@@ -115,7 +115,7 @@ CREATE TABLE ocp_dashboard (
   positions text DEFAULT NULL
 );

-INSERT INTO `ocp_dashboard` VALUES (1,'Default','{\"panel_20_widget_1\":\"{\\\"widget_title\\\":\\\"Users\\\",\\\"widget_box\\\":\\\"1\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"registered_users_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_20_widget_1\\\"}\",\"panel_20_widget_2\":\"{\\\"widget_name\\\":\\\"CDR\\\",\\\"widget_refresh\\\":\\\"30\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"cdr_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_20_widget_2\\\"}\",\"panel_20_widget_3\":\"{\\\"widget_title\\\":\\\"PKG Usage\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_warning\\\":\\\"50\\\",\\\"widget_critical\\\":\\\"75\\\",\\\"widget_refresh\\\":\\\"\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"pkg_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_20_widget_3\\\"}\",\"panel_20_widget_4\":\"{\\\"widget_title\\\":\\\"Shared memory\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_warning\\\":\\\"50\\\",\\\"widget_critical\\\":\\\"75\\\",\\\"widget_refresh\\\":\\\"60\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"shmem_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_20_widget_4\\\"}\",\"panel_20_widget_6\":\"{\\\"widget_name\\\":\\\"Dispatching\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_partition\\\":\\\"default\\\",\\\"widget_set\\\":\\\"\\\",\\\"widget_refresh\\\":\\\"30\\\",\\\"editwidget\\\":\\\"Edit\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_type\\\":\\\"dispatching_widget\\\",\\\"widget_id\\\":\\\"panel_20_widget_6\\\"}\",\"panel_20_widget_7\":\"{\\\"widget_name\\\":\\\"Dynamic Routing\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_partition\\\":\\\"\\\",\\\"widget_refresh\\\":\\\"30\\\",\\\"editwidget\\\":\\\"Edit\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_type\\\":\\\"gateways_widget\\\",\\\"widget_id\\\":\\\"panel_20_widget_7\\\"}\",\"panel_20_widget_11\":\"{\\\"widget_name\\\":\\\"RTPProxy\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_set\\\":\\\"0\\\",\\\"widget_refresh\\\":\\\"60\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"rtpproxy_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_20_widget_11\\\"}\",\"panel_23_widget_15\":\"{\\\"widget_name\\\":\\\"RTPEngine\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_set\\\":\\\"0\\\",\\\"widget_refresh\\\":\\\"60\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"rtpengine_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_23_widget_15\\\"}\"}',1,'[{\"id\":\"panel_20_widget_1\",\"col\":6,\"row\":1,\"size_x\":2,\"size_y\":3},{\"id\":\"panel_20_widget_2\",\"col\":8,\"row\":1,\"size_x\":2,\"size_y\":3},{\"id\":\"panel_20_widget_3\",\"col\":4,\"row\":1,\"size_x\":2,\"size_y\":3},{\"id\":\"panel_20_widget_4\",\"col\":2,\"row\":1,\"size_x\":2,\"size_y\":3},{\"id\":\"panel_20_widget_6\",\"col\":2,\"row\":6,\"size_x\":2,\"size_y\":2},{\"id\":\"panel_20_widget_7\",\"col\":4,\"row\":6,\"size_x\":2,\"size_y\":2},{\"id\":\"panel_20_widget_11\",\"col\":6,\"row\":6,\"size_x\":2,\"size_y\":2},{\"id\":\"panel_23_widget_15\",\"col\":8,\"row\":6,\"size_x\":2,\"size_y\":2}]');
+INSERT INTO "ocp_dashboard" VALUES (1,'Default','{\"panel_20_widget_1\":\"{\\\"widget_title\\\":\\\"Users\\\",\\\"widget_box\\\":\\\"1\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"registered_users_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_20_widget_1\\\"}\",\"panel_20_widget_2\":\"{\\\"widget_name\\\":\\\"CDR\\\",\\\"widget_refresh\\\":\\\"30\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"cdr_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_20_widget_2\\\"}\",\"panel_20_widget_3\":\"{\\\"widget_title\\\":\\\"PKG Usage\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_warning\\\":\\\"50\\\",\\\"widget_critical\\\":\\\"75\\\",\\\"widget_refresh\\\":\\\"\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"pkg_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_20_widget_3\\\"}\",\"panel_20_widget_4\":\"{\\\"widget_title\\\":\\\"Shared memory\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_warning\\\":\\\"50\\\",\\\"widget_critical\\\":\\\"75\\\",\\\"widget_refresh\\\":\\\"60\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"shmem_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_20_widget_4\\\"}\",\"panel_20_widget_6\":\"{\\\"widget_name\\\":\\\"Dispatching\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_partition\\\":\\\"default\\\",\\\"widget_set\\\":\\\"\\\",\\\"widget_refresh\\\":\\\"30\\\",\\\"editwidget\\\":\\\"Edit\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_type\\\":\\\"dispatching_widget\\\",\\\"widget_id\\\":\\\"panel_20_widget_6\\\"}\",\"panel_20_widget_7\":\"{\\\"widget_name\\\":\\\"Dynamic Routing\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_partition\\\":\\\"\\\",\\\"widget_refresh\\\":\\\"30\\\",\\\"editwidget\\\":\\\"Edit\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_type\\\":\\\"gateways_widget\\\",\\\"widget_id\\\":\\\"panel_20_widget_7\\\"}\",\"panel_20_widget_11\":\"{\\\"widget_name\\\":\\\"RTPProxy\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_set\\\":\\\"0\\\",\\\"widget_refresh\\\":\\\"60\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"rtpproxy_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_20_widget_11\\\"}\",\"panel_23_widget_15\":\"{\\\"widget_name\\\":\\\"RTPEngine\\\",\\\"widget_box\\\":\\\"1\\\",\\\"widget_set\\\":\\\"0\\\",\\\"widget_refresh\\\":\\\"60\\\",\\\"addwidget\\\":\\\"Add\\\",\\\"widget_type\\\":\\\"rtpengine_widget\\\",\\\"panel_id\\\":\\\"23\\\",\\\"widget_id\\\":\\\"panel_23_widget_15\\\"}\"}',1,'[{\"id\":\"panel_20_widget_1\",\"col\":6,\"row\":1,\"size_x\":2,\"size_y\":3},{\"id\":\"panel_20_widget_2\",\"col\":8,\"row\":1,\"size_x\":2,\"size_y\":3},{\"id\":\"panel_20_widget_3\",\"col\":4,\"row\":1,\"size_x\":2,\"size_y\":3},{\"id\":\"panel_20_widget_4\",\"col\":2,\"row\":1,\"size_x\":2,\"size_y\":3},{\"id\":\"panel_20_widget_6\",\"col\":2,\"row\":6,\"size_x\":2,\"size_y\":2},{\"id\":\"panel_20_widget_7\",\"col\":4,\"row\":6,\"size_x\":2,\"size_y\":2},{\"id\":\"panel_20_widget_11\",\"col\":6,\"row\":6,\"size_x\":2,\"size_y\":2},{\"id\":\"panel_23_widget_15\",\"col\":8,\"row\":6,\"size_x\":2,\"size_y\":2}]');

 -- --------------------------------------------------------

diff --git a/web/common/cfg_comm.php b/web/common/cfg_comm.php
index 2d1b6e4f..bd217531 100644
--- a/web/common/cfg_comm.php
+++ b/web/common/cfg_comm.php
@@ -246,7 +246,7 @@ function load_panels() {
        require("".__DIR__."/../../config/tools/system/dashboard/settings.inc.php");
        unset($_SESSION['config']['panels']);
        $max_order = -1;
-       $sql = 'select `name`, id, content, positions, `order` from ocp_dashboard';
+       $sql = 'select "name", id, content, positions, "order" from ocp_dashboard';
        $stm = $link->prepare($sql);
        if ($stm === false) {
                die('Failed to issue query ['.$sql.'], error message : ' . print_r($link->errorInfo(), true));
diff --git a/web/common/tools/tviewer/lib/data_loader.php b/web/common/tools/tviewer/lib/data_loader.php
index 763c070f..872a7159 100644
--- a/web/common/tools/tviewer/lib/data_loader.php
+++ b/web/common/tools/tviewer/lib/data_loader.php
@@ -88,7 +88,7 @@
                {
                        if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
                        {
-                               $sOrder .= "`".$aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."` ".
+                               $sOrder .= "\"".$aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."\" ".
                                        mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";
                        }
                }
@@ -113,7 +113,7 @@
                $sWhere = "WHERE (";
                for ( $i=0 ; $iquery( $sQuery );
diff --git a/web/common/tools/tviewer/tviewer.php b/web/common/tools/tviewer/tviewer.php
index ef03365e..224cbcc1 100755
--- a/web/common/tools/tviewer/tviewer.php
+++ b/web/common/tools/tviewer/tviewer.php
@@ -65,7 +65,7 @@ else {
 # include custom columns action scripts  #
 ##########################################

-if (isset($custom_config[$module_id][$_SESSION[$module_id]['submenu_item_id']]['custom_action_columns']) {
+if (isset($custom_config[$module_id][$_SESSION[$module_id]['submenu_item_id']]['custom_action_columns'])) {
        for ($i=0; $i $params){
                        if ($params['show_in_edit_form']) {
                                if ($params['type'] != "password" || $_POST[$attr] != "") {
-                                       $update_query .= "`".$attr."`=?, ";
-                                       $params_names.="`".$attr."`, ";
+                                       $update_query .= "\"".$attr."\"=?, ";
+                                       $params_names.="\"".$attr."\", ";
                                        $unknowns.="?, ";
                                        $values[] = $_POST[$attr];
                                }
                        }
                }
-               $update_query.="`id`=?;";
+               $update_query.="\"id\"=?;";
                $params_names.="id";
                $unknowns.="?";
                $values[] = $box_id;
@@ -142,7 +142,7 @@ if ($action == "add_verify") {
                        if ($unknowns != "") $unknowns.=",";
                        if ($params_names != "") $params_names.=",";
                        $unknowns.="?";
-                       $params_names.="`".$attr."`";
+                       $params_names.="\"".$attr."\"";
                        $values[] = $_POST[$attr];
                }

diff --git a/web/tools/admin/boxes_config/system_config.php b/web/tools/admin/boxes_config/system_config.php
index e5506209..37f6e516 100644
--- a/web/tools/admin/boxes_config/system_config.php
+++ b/web/tools/admin/boxes_config/system_config.php
@@ -54,13 +54,13 @@ if ($action=="modify_params")
                $values = array();
                foreach ($system_params as $attr => $params){
                        if ($params['show_in_edit_form']) {
-                               $update_query .= "`".$attr."`=?, ";
-                               $params_names.="`".$attr."`, ";
+                               $update_query .= "\"".$attr."\"=?, ";
+                               $params_names.="\"".$attr."\", ";
                                $unknowns.="?, ";
                                $values[] = $_POST[$attr];
                        }
                }
-               $update_query.="`assoc_id`=?;";
+               $update_query.="\"assoc_id\"=?;";
                $params_names.="assoc_id";
                $unknowns.="?";
                $values[] = $assoc_id;
@@ -134,7 +134,7 @@ if ($action == "add_verify") {
                                if ($params_names != "") $params_names.=",";
                                if ($unknowns != "") $unknowns.=",";
                                $unknowns.="?";
-                               $params_names.="`".$attr."`";
+                               $params_names.="\"".$attr."\"";
                                $values[] = $_POST[$attr];
                        }
                }
diff --git a/web/tools/system/dashboard/dashboard.php b/web/tools/system/dashboard/dashboard.php
index 847fbe93..6161d40b 100644
--- a/web/tools/system/dashboard/dashboard.php
+++ b/web/tools/system/dashboard/dashboard.php
@@ -303,7 +303,7 @@ if ($action == "add_widget_verify") { //add widget in db
 if ($action == "add_verify") {
        if(!$_SESSION['read_only']){
                extract($_POST);
-               $sql = 'INSERT INTO '.$table.' (`name`, `order`) VALUES (?, ?) ';
+               $sql = 'INSERT INTO '.$table.' ("name", "order") VALUES (?, ?) ';
                $stm = $link->prepare($sql);
                if ($stm === false) {
                        die('Failed to issue query ['.$sql.'], error message : ' . print_r($link->errorInfo(), true));
@@ -375,7 +375,7 @@ if ($action == "clone_panel_verify") {
                }
                $widget_contents_json = json_encode($widget_contents);

-               $sql = 'UPDATE '.$table.' SET `name`=?, content=?, `order`=?, positions=? where id = ?';
+               $sql = 'UPDATE '.$table.' SET "name"=?, content=?, "order"=?, positions=? where id = ?';
                                $stm = $link->prepare($sql);
                if ($stm === false) {
                        die('Failed to issue query ['.$sql.'], error message : ' . print_r($link->errorInfo(), true));
diff --git a/web/tools/system/dashboard/template/dashboard.edit_panel.php b/web/tools/system/dashboard/template/dashboard.edit_panel.php
index e19fd6bf..8acebaf0 100644
--- a/web/tools/system/dashboard/template/dashboard.edit_panel.php
+++ b/web/tools/system/dashboard/template/dashboard.edit_panel.php
@@ -65,7 +65,7 @@ else
        }
        $start_limit=($page-1)*$res_no;
        //$sql_command.=" limit ".$start_limit.", ".$res_no;
-       $sql_command="select `name`, id, `order` from ".$table."  order by `order` asc limit ".$res_no;
+       $sql_command="select \"name\", id, \"order\" from ".$table."  order by \"order\" asc limit ".$res_no;
        if ($start_limit!=0)
                $sql_command.=" OFFSET " . $start_limit;
        $stm = $link->prepare( $sql_command );
diff --git a/web/tools/system/dashboard/template/functions.inc.php b/web/tools/system/dashboard/template/functions.inc.php
index d52e8d0f..e4a7d0c6 100644
--- a/web/tools/system/dashboard/template/functions.inc.php
+++ b/web/tools/system/dashboard/template/functions.inc.php
@@ -30,12 +30,12 @@
         require("../../../../config/db.inc.php");
         require("../../../../config/tools/system/dashboard/settings.inc.php");

-        $sql = 'UPDATE '.$table.' SET `order` = 
+        $sql = 'UPDATE '.$table.' SET "order" = 
         CASE
-         WHEN `order` = ? THEN ?
-         WHEN `order` = ? THEN ?
+         WHEN "order" = ? THEN ?
+         WHEN "order" = ? THEN ?
         END
-        WHERE `order` = ? or `order` = ?
+        WHERE "order" = ? or "order" = ?
         ';
         $stm = $link->prepare($sql);
         if ($stm === false) {
@@ -80,4 +80,4 @@

                ");
        }
-?>
\ No newline at end of file
+?>
diff --git a/web/tools/system/smonitor/statistics.php b/web/tools/system/smonitor/statistics.php
index d3f3c1f6..c900ff52 100644
--- a/web/tools/system/smonitor/statistics.php
+++ b/web/tools/system/smonitor/statistics.php
@@ -108,7 +108,7 @@ if ($action == "add_modify_statistic") {

        $input = json_encode($form_input);

-       $sql = "REPLACE INTO ocp_extra_stats (`name`, `input`, `tool`, `class`, box_id) VALUES (?,?,?,?,?);";
+       $sql = "REPLACE INTO ocp_extra_stats (\"name\", \"input\", \"tool\", \"class\", box_id) VALUES (?,?,?,?,?);";
                $stm = $link->prepare($sql);
                if ($stm === false) {
                        die('Failed to issue query ['.$sql.'], error message : ' . print_r($link->errorInfo(), true));
@@ -156,7 +156,7 @@ if ($action == "modify_statistic") {
                $info="Stat was added";
        }

-       $sql = "UPDATE ocp_extra_stats SET `name`=?, `input`=? where id = ?";
+       $sql = "UPDATE ocp_extra_stats SET \"name\"=?, \"input\"=? where id = ?";
        $stm = $link->prepare($sql);
        if ($stm === false) {
                die('Failed to issue query ['.$sql.'], error message : ' . print_r($link->errorInfo(), true));

[gmaruzz]

9.3.3.zip

9.3.3 patch file for 9.3.3 branch attached

[liviuchircu]

OK, then please start with commit 7b83e96 and see if all queries/PHP you found are covered there -- if it needs an extension, I can add up to it. I didn't merge it yet because it changes the "sql_mode" (for MySQL compatibility), and I wasn't sure how compatible it is with older PHP/PDO installations. But, it's been 6+ months with no reply in #293, so maybe it's time to just merge it and move forward.

[gmaruzz]

at least in 9.3.3 I had to modify for backticks:

        modified:   web/common/cfg_comm.php
        modified:   web/common/tools/tviewer/lib/data_loader.php
        modified:   web/tools/admin/boxes_config/boxes_config.php
        modified:   web/tools/admin/boxes_config/system_config.php
        modified:   web/tools/system/dashboard/dashboard.php
        modified:   web/tools/system/dashboard/template/dashboard.edit_panel.php
        modified:   web/tools/system/dashboard/template/functions.inc.php
        modified:   web/tools/system/smonitor/statistics.php

so, it seems in 9.3.3, in addition to your commit, you still need to edit:

        modified:   web/common/tools/tviewer/lib/data_loader.php
        modified:   web/tools/admin/boxes_config/boxes_config.php
        modified:   web/tools/admin/boxes_config/system_config.php
        modified:   web/tools/system/smonitor/statistics.php

in later branches, IIRC there are more places with backticks... maybe you want to grep :(