Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

修改源代码遇到的问题 #3

Open
chenyanlann opened this issue Aug 1, 2021 · 1 comment
Open

修改源代码遇到的问题 #3

chenyanlann opened this issue Aug 1, 2021 · 1 comment

Comments

@chenyanlann
Copy link
Owner

关于"`"的问题

(使用JDBC驱动clickhouse4j时)

在ClickHouse中是可以使用"`"的

例如查询

SELECT `Comment`.*, `User`.`id`,`User`.`name` FROM `sys`.`Comment` AS `Comment`  
   INNER JOIN `sys`.`apijson_user` AS `User` ON `User`.`id` = `Comment`.`userId` WHERE  (  (  (match(`Comment`.`content`, 'a'))  )  AND  (  (  (match(`User`.`name`, 'a') OR match(`User`.`name`, 't'))  )  )  )  ORDER BY `Comment`.`date` DESC  LIMIT 10

ClickHouse可以正确返回结果,但是最后的请求结果却是:

 {

    "ok": true ,
    "code": 200 ,
    "msg": "success" ,
    "sql:generate|cache|execute|maxExecute": "1|0|1|200" ,
    "depth:count|max": "3|5" ,
    "time:start|duration|end": "1627816512477|605|1627816513082" 

}

看一下控制台输出的日志记录,发现AbstractParser提前结束了:

2021-08-01 07:15:13.082: AbstractParser.DEBUG: putQueryResult queryResultMap.containsKey(valuePath) >> queryResultMap.put(path, result);
2021-08-01 07:15:13.082:

AbstractParser.DEBUG: <<<<<<<<<<<<<<<<<<<<<<< close >>>>>>>>>>>>>>>>>>>>>>>

排查发现apijson.orm.AbstractSQLExecutor.execute中的代码:

} else if (!config.getSQLTable().equalsIgnoreCase(rsmd.getTableName(i))) {
    viceColumnStart = i;
}

在执行时rsmd.getTableName返回"‘Comment’",而和它比较的字符串却是"Comment"

由于在ClickHouse中可以不使用"`"

在getQuote()中去掉"`"可以返回正确结果

@TommyLemon
Copy link

TommyLemon commented Aug 1, 2021

加引号是主要为了保证不和 SQL 关键词冲突,以及避免可能的 SQL 注入风险(目前应该没有这个风险,所有表名、字段名及它们的别名全都用正则判断是否为对应的 Java 变量名格式)。

} else if (!.equalsIgnoreCase(rsmd.getTableName(i))) {

可以改为

String sqlTable = config.getSQLTable();
if (config.isClickHouse()) {
  sqlTable = "‘" + sqlTable + "‘";
}
...

} else if ( ! sqlTable .equalsIgnoreCase(rsmd.getTableName(i))) {

不过这种方式需要确定 JDBC 对 ClickHouse 返回的表名一定是以 ‘ 这个引号包裹的(可能在 不同版本 JDBC 或 不同版本 ClickHouse 或 ClickHouse 不同配置下 表现也不一致)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants