英文:
How to get rid of the default single quotes in parameterized queries in clickhouse
问题
考虑这个简单的程序：
from clickhouse_driver import Client
c = Client(host="localhost")
params = {"database": "test", "table": "t"}
query = c.substitute_params(query='SELECT * from %(database)s.%(table)s', params=params, context=c.connection.context)
print(query)
Clickhouse会在参数周围放置单引号，所以查询结果将是：
SELECT * from 'test'.'t'
我也可以使用f-string解决这个问题，但那样容易受到SQL注入攻击的影响。如果我理解正确，这就是在ClickHouse中使用参数化查询来防止SQL注入的方式。如何防止在参数周围放置引号？
英文:
Consider this simple program:
from clickhouse_driver import Client
c = Client(host="localhost")
params = {"database": "test", "table": "t"}
query = c.substitute_params( query='SELECT * from %(database)s.%(table)s', params= params, context=c.connection.context)
print(query)
Clickhouse will put single quotes around the parameters, so the query result will be:
SELECT * from 'test'.'t'
I could also use f-string and the problem will be solved but that's vulnerable to SQLI. If I understand correctly, this is how parameterized queries are used in clickhouse to prevent SQLI.
How can we prevent the quotes from being put around the parameters?
答案1
得分: 1
根据我了解，substitute_params 不适用于数据库对象标识符，比如数据库和表，因为这些标识符在 ClickHouse 中需要以不同方式进行引用，通常需要使用反引号，而不是单引号，来表示实际的字符串值。具体信息可以查看 https://clickhouse.com/docs/en/sql-reference/syntax/#identifiers
一般来说，你可以通过验证输入的数据库和表是否匹配一个简单的正则表达式，比如"全部是小写字母或下划线"，来实现一些"SQL 注入防御"。在这种情况下，使用 f-string 应该是安全的。
ClickHouse 还支持"服务器端替代"，你可以为此情况使用 Identifier 类型，但我不认为 clickhouse-driver 中提供了这个功能。
英文:
As I understand it, substitute_params is not intended for database object identifiers like database and table, since those have to be quoted "differently" in ClickHouse (generally with backticks) than actual literal string values (with single quotes). https://clickhouse.com/docs/en/sql-reference/syntax/#identifiers
In general you can do your own bit of "SQL Injection defense" by validating the inputs for database and table, like ensuring they match a simple regex like "are all lower case letters or underscore" that applies to your ClickHouse schema. In that case using an f-string should be safe.
ClickHouse also support "server side substitution" where you can use an Identifier type for this use case, but I don't believe that feature is available in clickhouse-driver.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论