[CKAN-Security] Blind SQL Injection & DOS vulnerability with CKAN DataStore

[Adrià Mercader]

Hi Philip,

Thanks for the report. This has been reported in the past and
discussed among the team. It's difficult to parse the incoming
requests and reject them based on the presence of internal functions
as it's quite easy to bury them in long queries or try to hide them
somehow. Other big players that expose SQL interfaces to Postgres have
similar issues:

http://amercader.cartodb.com/api/v2/sql?q=SELECT%20%20current_user%20||%20version();

That doesn't mean that we don't want to address this, we'll see if we
can find resources to work on a potential patch for this (any help
would be great of course)

Regarding the pg_sleep denial of service, is true that is bad that
this exposed and should definitely be filtered, but there is a global
timeout of 60 seconds for all queries on the DataStore that should
help alleviate this. For instance, this:

curl --connect-timeout 100
"http://localhost:5001/api/action/datastore_search_sql?sql=SELECT%20pg_sleep(62);"

will return

{"help": "http://localhost:5001/api/3/action/help_show?name=datastore_search_sql",
"success": false, "error": {"query": ["Query took too long"],
"__type": "Validation Error"}}


Best,

Adrià

On 7 June 2018 at 02:01, Philip Ashlock - QXA <philip.ashlock at gsa.gov> wrote:
> It looks like there are some SQL commands that go unsanitized when using the
> CKAN DataStore API. In some scenarios this would lead to a blind sql
> injection that could disclose sensitive information in the database, but I'm
> not familiar enough with DataStore to test for that. At the very least the
> fact that the "sleep()" command is not sanitized does create a denial of
> service vulnerability.
>
> Select current_user and version()
> https://inventory.data.gov/api/action/datastore_search_sql?sql=SELECT%20current_user,version();
> https://data.boston.gov/api/action/datastore_search_sql?sql=SELECT%20current_user,version();
>
> Sleep for 5 seconds
> https://inventory.data.gov/api/action/datastore_search_sql?sql=SELECT%20pg_sleep(5);
> https://data.boston.gov/api/action/datastore_search_sql?sql=SELECT%20pg_sleep(5);
>
> I noticed that I couldn't recreate this behavior on https://demo.ckan.org so
> perhaps this has been addressed with newer versions of CKAN or the relevant
> extensions?