question: escaping values necessary?

[David-Mueller]

Hey there,

rather a question than an issue.
Do you think it's necessary to escape values for passed objects to the insert function?

Does escaping with a standard mysql escape function suffice for "self-built" queries? If so, should the client expose such a function?

Thanks again!

[depyronick]

Hey,

Well, I'm not sure, but:

Actually I believe this subject should not be scoped into just a single part of any application. An application should always consider "user inputs" as dangerous without thinking, and do validations or escaping on them starting from application entry point, which are commonly controller endpoints. In my ClickHouse use cases, I never pass any value to a query that is "user input".

So, on ClickHouse specific, ClickHouse has strong typing. Which means that there are no implicit type conversions. Unlike MySQL, 0 is not equal to '0'. 0 is integer and '0' is string. This alone already eliminates most of problems.

One with security concerns should always check Security Policy information on ClickHouse's GitHub to ensure that their ClickHouse version receives security updates and Security Changelog for recently disclosed vulnerabilities to decide whether they should upgrade their ClickHouse version or not.

Besides that, in String Literal section of ClickHouse documentation states:

Only string literals in single quotes are supported. The enclosed characters can be backslash-escaped. The following escape sequences have a corresponding special value: \b, \f, \r, \n, \t, \0, \a, \v, \xHH. In all other cases, escape sequences in the format \c, where c is any character, are converted to c. It means that you can use the sequences \'and\. The value will have the String type.

In string literals, you need to escape at least ' and \. Single quotes can be escaped with the single quote, literals 'It\'s' and 'It''s' are equal.

Looks like ClickHouse already been through some "sql injection" issues in the past. But you know, ClickHouse is famous for being developed unbelievably fast according to their huge changelog.

I guess we should deep dive into security issues with ClickHouse & correctly understand how they're avoding these issues and what can be done to create an abstract security layer while using our library. For now, it seems standard mysql escape function might help.

Exposing such function requires reliability. So, we should not expose any function that we don't know if it's reliable or not.

I will do research on this and try to come to a conclusion.