[CKAN-Security] SQL Injection Vulnerability

[Ian Ward]

The issue seems limited to a DoS and  private datastore data
disclosure (leaking data slowly by observing the effect of the where
clause on data returned).

I don't see a fix for this issue between 2.2.1 and 2.2.4. What
versions of CKAN are we still supporting? I can make sure at least
those ones aren't affected.

This one isn't exploitable on master, as David said. All the single
quotes in q are escaped before being included in the query string.

On Fri, Jun 8, 2018 at 12:34 PM, David Read
<david.read at hackneyworkshop.com> wrote:
> 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
> _______________________________________________
> CKAN security
> https://lists.okfn.org/mailman/listinfo/security
> https://lists.okfn.org/mailman/options/security/ian%40excess.org
>
> Repo: https://github.com/ckan/ckan-security