[CKAN-Security] SQL Injection Vulnerability

[Ian Ward]

This was fixed in
https://gitlab.com/ckan/ckan-security/merge_requests/1 which Adrià
backported to 2.4.1 and 2.3.2, so 2.2 and earlier are the only
versions not covered.

On Fri, Jun 8, 2018 at 3:37 PM, Ian Ward <ian at excess.org> wrote:
> 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