[CKAN-Security] SQL Injection Vulnerability

[David Read]

tldr: I had a quick play with this, but I can't see what is going on.
I think we should ask the reporter for more info. He's not said why he
thinks this is a vulnerability.

The longer version:

The site he mentions runs CKAN 2.2.1 and I don't want to try these
POSTs on it in case it damages it. When I try on my local CKAN I can't
spot anything untoward. Maybe he misunderstands that we let people run
SQL but that isn't malicious in itself. Or is he saying he can cause
something malicious through those the || characters and quotes.

The first query seems harmless, failing before it even gets near postgres:

curl -X POST http://192.168.33.60:5000/api/3/action/datastore_search
-d @/tmp/payload1
"Bad request - JSON Error: Error decoding JSON data. Error:
JSONDecodeError(\"Expecting ',' delimiter or '}': line 1 column 63
(char 62)\",) JSON data extracted from the request: {}"

The second query (giving a valid resource ID for the particular CKAN
instance) runs this query (from the postgres logs):

SELECT  count(*)
FROM "b8b84a01-b1c8-4bd3-8198-81e31a3b4a79" ,
plainto_tsquery('english', '''|| (SELECT CASE substr(version(),2,1)
WHEN ''o'' THEN ''A'' ELSE sleep(5) END) ||''') "query" WHERE
(_full_text @@ "query");

and I haven't thought too hard about it, but I don't really understand
what is going on, but can't see much wrong with it. No sleep occurs,
for example. And you could do that anyway. I'm missing something.

The onus should be on him to explain this stuff. But I wanted to check
with you, since I don't want to look dumb if it's obvious...

David

On 8 June 2018 at 03:37, Gonzalo Garcia | ODS Red Team
<gonzalo.g at opendatasecurity.io> wrote:
> Hi, I've found a SQL Injection vulnerability on
> /api/3/action/datastore_search. I've exploited it on "q" parameter, but
> maybe other parameters are affected too.
>
> Example POST data:
> Query fails
> {"resource_id":"38a8888c-30fd-4002-810e-6b7fe87a6fb2","q":" ' "
> ,"filters":{},"limit":100,"offset":0}
>
> Query doesn't fail because PostgreSQL version string is "PostrgreSQL..." and
> "o" is the second char of the string.
> {"resource_id":"38a8888c-30fd-4002-810e-6b7fe87a6fb2","q":"'|| (SELECT CASE
> substr(version(),2,1) WHEN 'o' THEN 'A' ELSE sleep(5) END) ||'"
> ,"filters":{},"limit":100,"offset":0}
> _______________________________________________
> CKAN security
> https://lists.okfn.org/mailman/listinfo/security
> https://lists.okfn.org/mailman/options/security/david.read%40hackneyworkshop.com
>
> Repo: https://github.com/ckan/ckan-security