Yandex.ClickHouse injection

In Russian:

In order to process a large amount of data in Yandex.Metrika, Yandex created a column-oriented DBMS ClickHouse. During penetration tests, we encountered ClickHouse in the systems of statistics, event collection, stock exchange systems.



The main advantages of ClickHouse are speed, flexibility, scalability, and the main application areas are analytics systems.

Column-oriented DBMSs provide high performance for tasks in which it is necessary to query data from one column frequently, while the rest of the data from the row is not needed.

Это изображение имеет пустой атрибут alt; его имя файла - M9NUuqSM9iMdxaot54TAK_TH9BOaeQLiZerC6EnlxO0uUlBb7b6Cid-WbsM31tm71I_4lDKWxEPwJbL-4THGpS1gRYn98Ntf_QvmMPNX9LQmDDkTIkuBVAt6147xlA96lJJpA3im
Это изображение имеет пустой атрибут alt; его имя файла - UnOI3tPbl-vs5_8rpz_E-xABmH6l8eLhA6C6qETH_qx7uqouvhfH1gblaxOKS1zO_3mFOEdqo0el288G5WI2FhujnPuswx4MV9uj8Lc0X_agp8gnrUL6c3doapYoeZysCue4z07F

Due to the structure of the stored data, the only the needed columns are accessed, which significantly increases the performance. It also makes it possible to compress the data more efficiently during storage, because the data in one column is homogeneous.

Это изображение имеет пустой атрибут alt; его имя файла - Ro7X9r3YzRIXl_xiPS69t_iRLUy61EAvFXLz5fKlV83pxaxNga0Zw20PiwffDW456-IsDeyBmPynvJVPYfP1VCkv86QPQOmGqa-k5SmvJEWwsolsPvXNFq9tdmKjdPmkr4cxVXJW

See more details:


The Clickhouse developers outlined several significant limitations that exist in the DBMS:

  • Limited support for JOIN
  • Weak compatibility with SQL standard

Lack of common UPDATE and DELETE operators can be explained with peculiarities of storage architecture. The data is stored in columns and in order to simply delete some record it is necessary to read and mutate n columns.

However, support for ALTER UPDATE and ALTER DELETE was added in 2018. This is how they differ from standard UPDATE and DELETE:

  • Executed asynchronously
  • Do not block inserts
  • Do not block queries
  • Do not block each other

Unlike some SQL dialects, ClickHouse has strict typing. There are no implicit conversions between types.

Instead of the standard UNION, ClickHouse only supports UNION ALL to combine requests:

Это изображение имеет пустой атрибут alt; его имя файла - -TwM6PK44NW_zdhExVSsFMmmg6rRrc1yCn90mKpqmLbsgHPcU7lVXkwL1pL6HoGzKZhgLKkzALostT8u3mn732ICiRVjkEEaHM65ybTeeB9vb1TgTF7sQvbj95SogbRxAycaXmQR

The useful (for hackers) INTO OUTFILE expression works only in the console client. There is no access to it via the HTTP-interface (


There 2 connection types for ClickHouse:

  • HTTP
  • Native TCP

Both protocols can also be wrapped in TLS, but in practice, it’s rarely done.

The HTTP interface in ClickHouse makes it easy to write a connector to the DB in any language.

When using the HTTP GET method, only requests that do not change the data are allowed, and in order to change or create records, you need to use the POST method. At the same time, the request itself can be transmitted both in the body of the POST request and in the query string (both GET and POST requests).

How do I find ClickHouse online? Below is a sample request to search for publicly available instances through Shodan:

Это изображение имеет пустой атрибут alt; его имя файла - image-3.png

The ClickHouse language supports the tabular url function, which allows you to access remote hosts via the HTTP and HTTPS protocols (only).

Это изображение имеет пустой атрибут alt; его имя файла - sknx0IBrcltYvP4ZO_G-cJ4azDpGMJIjqHxhyejWYLAlnSY4oPn1P7vaPktABdknqONF9wAvws9wiWeU_XyjCQflJlN7Ctcx9aIUTKKBsAnuSO46DnLjXD-YjGdh8dAi7Pdt2iwO

There is also a tabular file function that allows you to read files from a certain directory, and the restriction cannot be bypassed.

Это изображение имеет пустой атрибут alt; его имя файла - xgiwonQ7DtTH1FIrNLdOkCKTbwTXiyL_etaId-2yu6FcBgzsrRr7LeXhdz4qdmn08nB1uJ9CW5NjQ2nS2HP7ug-V_vKrxUV8Rwq9UXd2OAXUL1etZEoblLZUFIg2akWpthUzv4px

Since ClickHouse has a MySQL client built-in, of course, we immediately thought about reading arbitrary files via LOAD DATA LOCAL INFILE. But it turned out that such a vulnerability did exist, and it had already been fixed by the internal Yandex security team. They also fixed vulnerabilities allowing protocol smuggling (via username) in JDBC connections.


Patch (disables the feature):

Useful information can be obtained from system tables, they provide access to system status information, available databases, tables and columns, currently running requests (processes table), etc. More about system tables:

HTTP attacks

ClickHouse’s HTTP interface means that some web application-specific attacks may be relevant for this DBMS.

Reflected File Download

The HTTP interface of ClickHouse may allow a Reflected File Download attack. For that to work, it must meet one of the following conditions:

  • There’s no password
  • Password was saved in the victim’s browser
  • We know the password

Example exploit: http://clickhouse:8123/yandex.bat?query=select+’calc’

Это изображение имеет пустой атрибут alt; его имя файла - image-1.png

As you can see, the response from the webserver is not a 404 error, but an attachment with an attacker-controlled name. Thus, you can download an executable file with any content to the victim’s computer.


As with RFDs, CSRF exploitation is possible when there’s no authorization (or a password is known).

Example exploit:


Another weakness of the HTTP interface is that SSRF attacks on another application can be used to make arbitrary requests to ClickHouse.

GET method will be enough for read-only requests. If authorization is required, the login and password can be passed to the query string: ?username=…&password=…

SQL injection

Error-based injection

Sample error-based exploit via the tabular url function for SQL-injection in ClickHouse in a real application:

Это изображение имеет пустой атрибут alt; его имя файла - PZmIMHRv-9heHwqFhBUCCv-n6aXBDxrqB0M74_-FtNToo4WQElAlezj6nwTcOaPai_Gnebx18-pF1kdM-QyPl-UqQidwxlv5SFiIj5sXTUpiIdjNACXxyIGF8HIGWo3-zMQVHzT7

The result of the subquery is used as the hostname for the HTTP request in the url function. Since there is no such host, the Poco library (used in ClickHouse) throws out an exception that has a result in its text.

SSRF via SQL injection

Obviously, the url function allows you to conduct an SSRF attack through an SQL injection, such access to the AWS EC2 API is demonstrated below:

Это изображение имеет пустой атрибут alt; его имя файла - 2D022aBaEfjM5IaLgwdsUrEwEINWJ-GPBM7fJ7cc-1uuEmpzllWSmNS-VXEmusqY0OO1kQY8V9HjHaeKiM20YIahoo1gfRjcF49LbCk_8a2JfrQsrY_ApDkGXSjS3SMvqtboApnp

This time we pass the result of the url function again into the url function, so we see the content of the HTTP response in the error text. It should be noted that the error text is cast to the lower case (you can see it on the substring “result” in the screenshot).

Cheat Sheet

VersionSELECT version()
Current DBSELECT currentDatabase()
List DBSHOW databases OR SELECT * FROM system.databases
List columnsSELECT * FROM system.columns
List tablesSELECT * FROM system.tables
HostnameSELECT hostName()
ConcatSELECT concat(‘one’,’two’) OR SELECT ‘one’||’two’
CommentSELECT 1 /*comment*/ OR SELECT 1—comment
Dummy table (dual)SELECT * FROM
Current UserSELECT ‘current_user’,user FROM system.processes WHERE query LIKE ‘%current_user%’
Current os_userSELECT os_user FROM system.processes
HTTP requestSELECT * FROM url(‘http://server’, ‘CSV’, col String)
Read fileSELECT * FROM file(‘nameFile’, ‘CSV’, col String)
UnhexSELECT unhex(‘746f62695f70697a6461’)
Create an array of argument valuesSELECT groupArray(x)
Concat array of stringsSELECT arrayStringConcat(arr[, separator])
Connect to MySQLmysql(‘host:port’, ‘database’, ‘table’, ‘user’, ‘password'[, replace_query, ‘on_duplicate_clause’]);
JDBC connectionSELECT * FROM jdbc(‘jdbc:mysql://localhost:3306/?user=root&password=root’, ‘schema’, ‘table’)

Leave a Reply

Your email address will not be published. Required fields are marked *