morningman opened a new issue #381: Support 'NO_BACKSLASH_ESCAPES' sql_mode URL: https://github.com/apache/incubator-doris/issues/381 # Description Doris supports creating external MySQL table, and can query mysql table using SQL. When querying MySQL table, Doris simply assemble the predicates(filter) into a SQL and pass it to the MySQL server. On the other hand, Doris, as a SQL query engine, is using backslash to escape characters. So the lexical parser will consume the backslash in parsing phase. But when we are querying a MySQL table, This may take the SQL injection risk. For example: `SELECT * FROM mysql_table WHERE k1 = '1\') UNION ALL SELECT 1,2,3#'` The original statement expects to query the result where `k1` equals to the string `1') UNION ALL SELECT 1,2,3#`, where the `'` is escaped by a backslash. But because this is a MySQL table, so we simply pass the value `1') UNION ALL SELECT 1,2,3#` to the MySQL server, where the `'` is NOT escaped by backslash, as in MySQL server view. So, the final query statement becomes: `SELECT * FROM mysql_table WHERE (k1 = '1') UNION ALL SELECT 1,2,3#')` which is not what we expected. (We add the `()` to try protecting the integrity of the predicates. And`#` is treated as comments, so characters after `#` are ignored. ) # Solution The main reason why this happens is that Doris can not know whether user is querying a Doris table or an external MySQL table when in lexical parsing phase. So Doris can not determine whether to consume the backslash or not. A simple solution is to escape the backlash itself. For example: `SELECT * FROM mysql_table WHERE k1 = '1\\\\') UNION ALL SELECT 1,2,3#'` But this is inconvenient for user. So we reference the MySQL variable `sql_mode`, which has a mode called 'NO_BACKSLASH_ESCAPES', which means [Disable the use of the backslash character (\) as an escape character within strings](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_backslash_escapes). Before a user querying a MySQL table, it can first execute: `SET [GLOBALE] VARIABLE sql_mode=NO_BACKSLASH_ESCAPES;` and then simple query MySQL table: `SELECT * FROM mysql_table WHERE k1 = '1\') UNION ALL SELECT 1,2,3#'` without any further pre-processing of the SQL. And the Doris will pass the final statement to MySQL server as: `SELECT * FROM mysql_table WHERE (k1 = '1\') UNION ALL SELECT 1,2,3#')` which is correct.
---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
