Repository: mindsdb/mindsdb
Pull request: table name escaping
close #593 
this fix allow use via sql-interface predictors with names contains 'minus' and 'space' signs (and probably other else supported by current database)

Problem statement:
DB exception when running mysql api
In the latest version on staging branch running mindsdb mysql api as ` python3 -m mindsdb --api mysql --config config.json`  throws:

```
Using configuration file: config.json
Traceback (most recent call last):
  File "/usr/local/lib/python3.7/runpy.py", line 193, in _run_module_as_main
    "__main__", mod_spec)
  File "/usr/local/lib/python3.7/runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "/home/zoran/MyProjects/mindsdb/mindsdb/__main__.py", line 61, in <module>
    dbw.register_predictors(model_data_arr)
  File "/home/zoran/MyProjects/mindsdb/mindsdb/interfaces/database/database.py", line 41, in register_predictors
    for integration in it: integration.register_predictors(model_data_arr)
  File "/home/zoran/MyProjects/mindsdb/mindsdb/integrations/clickhouse/clickhouse.py", line 115, in register_predictors
    self._query(q)
  File "/home/zoran/MyProjects/mindsdb/mindsdb/integrations/clickhouse/clickhouse.py", line 58, in _query
    raise Exception(f'Error: {response.content}\nQuery:{query}')
Exception: Error: b'Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 51 (line 2, col 50): -class\n                    ( `Number of times pregnant` Nullable(String) , `Plasma glucose concentration` Nullable(Int64) , `Diastolic blood pressure` Nullable(. Expected one of: storage definition, ENGINE, AS, UUID, OpeningRoundBracket, ON, Dot, token (version 20.5.2.7 (official build))\n'
Query:
                    CREATE TABLE mindsdb.diabetes-class
                    ( `Number of times pregnant` Nullable(String) , `Plasma glucose concentration` Nullable(Int64) , `Diastolic blood pressure` Nullable(Int64) , `Triceps skin fold thickness` Nullable(Int64) , `2-Hour serum insulin` Nullable(Int64) , `Body mass index` Nullable(Float64) , `Diabetes pedigree function` Nullable(Float64) , `Age` Nullable(Int64) , `Class` Nullable(String) , `Class_original` Nullable(String) ,`select_data_query` Nullable(String),`Class_confidence` Nullable(Float64),`Class_explain` Nullable(String)
                    ) ENGINE=MySQL('127.0.0.1:47335', 'mindsdb', 'diabetes-class_clickhouse', 'root', 'somepass')
```

It looks like it tries to recreate some of the tables that already exist in ClickHouse. The config file used:

```
{
    "use_gpu": false,
    "api": {
        "http": {
            "host": "0.0.0.0",
            "port": "47334"
        }, 
        "mysql": {
            "certificate_path": "/home/zoran/MyProjects/mindsdb/tests/integration_tests/flows/config/cert.pem",
            "datasources": [],
            "host": "127.0.0.1",
            "log": {
                "console_level": "INFO",
                "file": "mysql.log",
                "file_level": "INFO",
                "folder": "logs/",
                "format": "%(asctime)s - %(levelname)s - %(message)s"
            },
            "password": "somepassword",
            "port": "47335",
            "user": "root"
        }
    },
    "config_version": 1,
    "debug": true,
    "integrations": {
        "default_clickhouse": {
            "enabled": true,
            "type": "clickhouse",
            "host": "localhost",
            "password": "somepassword",
            "port": 8123,
            "user": "default"
        }
    },
    "interface":{
        "datastore": {
            "enabled": false,
            "storage_dir": "/home/zoran/MyProjects/mindsdb-examples/tescl/datastore"
        }
    }
}
```

Hints from issue discussion:

Commit message: close #593 table name escaping

Patch to review:
diff --git a/mindsdb/api/mysql/mysql_proxy/classes/sql_query.py b/mindsdb/api/mysql/mysql_proxy/classes/sql_query.py
index 17bad908487..597484d027c 100644
--- a/mindsdb/api/mysql/mysql_proxy/classes/sql_query.py
+++ b/mindsdb/api/mysql/mysql_proxy/classes/sql_query.py
@@ -166,7 +166,7 @@ def _parseQuery(self, sql):
         fromStatements = self.struct.get('from')
         if isinstance(fromStatements, list) is False:
             fromStatements = [fromStatements]
-        
+
         self.struct['from'] = [self._format_from_statement(x) for x in fromStatements]
 
         orderby = self.struct.get('orderby')
diff --git a/mindsdb/integrations/clickhouse/clickhouse.py b/mindsdb/integrations/clickhouse/clickhouse.py
index 26544aad5c1..30199ca3dd4 100644
--- a/mindsdb/integrations/clickhouse/clickhouse.py
+++ b/mindsdb/integrations/clickhouse/clickhouse.py
@@ -63,6 +63,9 @@ def _query(self, query):
     def _get_mysql_user(self):
         return f"{self.config['api']['mysql']['user']}_{self.name}"
 
+    def _escape_table_name(self, name):
+        return '`' + name.replace('`', '\\`') + '`'
+
     def setup(self):
         self._query('DROP DATABASE IF EXISTS mindsdb')
 
@@ -94,7 +97,7 @@ def setup(self):
 
     def register_predictors(self, model_data_arr):
         for model_meta in model_data_arr:
-            name = model_meta['name']
+            name = self._escape_table_name(model_meta['name'])
             stats = model_meta['data_analysis']
             if 'columns_to_ignore' in stats:
                 del stats['columns_to_ignore']
@@ -121,7 +124,7 @@ def register_predictors(self, model_data_arr):
 
     def unregister_predictor(self, name):
         q = f"""
-            drop table if exists mindsdb.{name};
+            drop table if exists mindsdb.{self._escape_table_name(name)};
         """
         self._query(q)
 
diff --git a/mindsdb/integrations/mariadb/mariadb.py b/mindsdb/integrations/mariadb/mariadb.py
index db1a0a2a589..90e9656b59c 100644
--- a/mindsdb/integrations/mariadb/mariadb.py
+++ b/mindsdb/integrations/mariadb/mariadb.py
@@ -38,6 +38,9 @@ def _to_mariadb_table(self, stats, predicted_cols):
 
         return column_declaration
 
+    def _escape_table_name(self, name):
+        return '`' + name.replace('`', '``') + '`'
+
     def _query(self, query):
         con = mysql.connector.connect(host=self.config['integrations'][self.name]['host'], port=self.config['integrations'][self.name]['port'], user=self.config['integrations'][self.name]['user'], password=self.config['integrations'][self.name]['password'])
 
@@ -112,7 +115,7 @@ def register_predictors(self, model_data_arr):
             connect = self._get_connect_string(name)
 
             q = f"""
-                    CREATE TABLE mindsdb.{name}
+                    CREATE TABLE mindsdb.{self._escape_table_name(name)}
                     ({columns_sql}
                     ) ENGINE=CONNECT TABLE_TYPE=MYSQL CONNECTION='{connect}';
             """
@@ -120,13 +123,18 @@ def register_predictors(self, model_data_arr):
 
     def unregister_predictor(self, name):
         q = f"""
-            drop table if exists mindsdb.{name};
+            drop table if exists mindsdb.{self._escape_table_name(name)};
         """
         self._query(q)
 
     def check_connection(self):
         try:
-            con = mysql.connector.connect(host=self.config['integrations'][self.name]['host'], port=self.config['integrations'][self.name]['port'], user=self.config['integrations'][self.name]['user'], password=self.config['integrations'][self.name]['password'])
+            con = mysql.connector.connect(
+                host=self.config['integrations'][self.name]['host'],
+                port=self.config['integrations'][self.name]['port'],
+                user=self.config['integrations'][self.name]['user'],
+                password=self.config['integrations'][self.name]['password']
+            )
             connected = con.is_connected()
             con.close()
         except Exception:
diff --git a/mindsdb/interfaces/datastore/datastore.py b/mindsdb/interfaces/datastore/datastore.py
index 16106ddc84b..a193c759332 100644
--- a/mindsdb/interfaces/datastore/datastore.py
+++ b/mindsdb/interfaces/datastore/datastore.py
@@ -89,7 +89,7 @@ def save_datasource(self, name, source_type, source, file_path=None):
             user = self.config['integrations']['default_clickhouse']['user']
             password = self.config['integrations']['default_clickhouse']['password']
             # TODO add host port params
-            ds = ClickhouseDS(source, user=user, password=password)
+            ds = ClickhouseDS(query=source, user=user, password=password)
             picklable = {
                 'class': 'ClickhouseDS'
                 ,'args': [source]
@@ -100,10 +100,10 @@ def save_datasource(self, name, source_type, source, file_path=None):
             password = self.config['integrations']['default_mariadb']['password']
             host = self.config['integrations']['default_mariadb']['host']
             port = self.config['integrations']['default_mariadb']['port']
-            ds = MariaDS(source, user=user, password=password, host=host, port=port)
+            ds = MariaDS(table='', query=source, user=user, password=password, host=host, port=port)
             picklable = {
                 'class': 'MariaDS'
-                ,'args': [source]
+                ,'args': ['', source]
                 ,'kwargs': {
                     'user': user,
                     'password': password,


Code review comments:
diff_hunk: @@ -100,10 +100,10 @@ def save_datasource(self, name, source_type, source, file_path=None):
             password = self.config['integrations']['default_mariadb']['password']
             host = self.config['integrations']['default_mariadb']['host']
             port = self.config['integrations']['default_mariadb']['port']
-            ds = MariaDS(source, user=user, password=password, host=host, port=port)
+            ds = MariaDS(table='', query=source, user=user, password=password, host=host, port=port)
path: mindsdb/interfaces/datastore/datastore.py
text: @author:
@user1 please check this. ClickhouseDS above has not 'table' parameter in constructor, but for MariaDS it required. As i assume we should delete 'table' from MariaDB or add it to ClickhouseDS, depending on how GUI will create such datasources.

@user1:
We can leave `table` for MariaDS, I don't think we need to specify it as a parameter, if `query` is specified then `table` is ignored.

Leave it unspecified here please

@author:
done