Escaping issue / SQL injection risk

[fjolliton]

The escaping is still lacking despite recent improvements. Escaping is not just about doubling single or double quotes, but taking into account each database specificity.

For ClickHouse, here is a potential SQL injection vector:

>>> from pypika import Query
>>> from pypika.dialects import ClickHouseQuery
>>> from pypika.terms import ValueWrapper
>>> print(ClickHouseQuery.select(ValueWrapper('foo\\')).get_sql())
SELECT 'foo\'

This is indeed ill-formatted, because \' is interpreted specifically by Clickhouse, leading to this:

:) select 'foo\';

Single quoted string is not closed: Syntax error: failed at position 8 ('foo\';):

select 'foo\';

Here, the correct output needs to be SELECT 'foo\\':

:) select 'foo\\';

SELECT 'foo\\'

Query id: 6fcd9601-4fe0-442a-82d6-6c4869a91d92

   ┌─'foo\\'─┐
1. │ foo\    │
   └─────────┘

But this is per-DB. Because with SQLite for instance, this works differently:

sqlite> select 'foo\';
foo\
sqlite> select 'foo\\';
foo\\