[MDEV-28533] CONNECT engine does not quote columns involved in WHERE clause Created: 2022-05-10  Updated: 2022-09-26  Resolved: 2022-09-26

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.7.3, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.3.37, 10.4.27, 10.5.18, 10.6.11, 10.7.7, 10.8.6

Type: Bug Priority: Major
Reporter: Trevor Gross Assignee: Anel Husakovic
Resolution: Fixed Votes: 0
Labels: connect-engine
Environment:

10.7.3-MariaDB-1:10.7.3+maria~focal-log from docker image for both source and CONNECT servers. `dpkg -s mariadb-plugin-connect | grep Version` reports `Version: 1:10.7.3+maria~focal` for connect plugin


 Description   

Hello,

I have noticed that the connect engine does not properly quote columns in WHERE when the table type is MySQL. For example, issuing the following query against a CONNECT database:

SELECT `ID` FROM t1 WHERE t1.`spaced col` = 'C-003';

will issue the following against the main server:

SELECT `ID`, `spaced col` FROM `t1` WHERE spaced col= 'C-003';

Note how the WHERE clause is unquoted, which raises a syntax error.

 Comments   
Comment by Alice Sherepa [ 2022-05-11 ]

Thank you for the report!

let $port= select @@port;
 
 
CREATE TABLE t1 (id int, `spaced col` varchar(10));
 
insert into t1 values (1,1),(2,'C-003');
 
 
CREATE TABLE t2 (id int, `spaced col` varchar(10))
 
engine=connect table_type=mysql option_list='host=localhost,user=root,port=3313' dbname='test' tabname='t1';
 
 
SELECT `id` FROM t2 WHERE t2.`spaced col` = 'C-003';


MariaDB [test]> SELECT `id` FROM t2 WHERE `spaced col` = 'C-003';
 
ERROR 1296 (HY000): Got error 174 '(1064) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'col= 'C-003'' at line 1 [SELECT `id`, `spaced col` FROM ' from CONNECT

Generated at Thu Feb 08 10:01:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.