OOTB controls in place for CKAN's datastore_search_sql API against SQL injection

raymedia · May 15, 2018, 9:52am

Hello,

Would you mind to share your knowledge on the OOTB controls in place for CKAN’s datastore_search_sql API against SQL Injection/vulnerability? I can’t find any documentation or discussions on this topic. Thank you in advance.

Ray

jqnatividad · May 16, 2018, 1:44pm

datastore_search_sql ensures that you can only use resources you have access to in the SQL statement, with a default timeout of 60000 milliseconds

github.com

ckan/ckan/blob/555e0960c43d0ca86066b1954e5c94aad565baa7/ckanext/datastore/logic/action.py#L473


      
          
          
        p.toolkit.check_access('datastore_search', context, data_dict)
          
          
    result = backend.search(context, data_dict)
              result.pop('id', None)
              result.pop('connection_url', None)
              return result
          
          

          
@logic.side_effect_free
          def datastore_search_sql(context, data_dict):
              '''Execute SQL queries on the DataStore.
          
          
    The datastore_search_sql action allows a user to search data in a resource
              or connect multiple resources with join expressions. The underlying SQL
              engine is the
              `PostgreSQL engine <http://www.postgresql.org/docs/9.1/interactive/>`_.
              There is an enforced timeout on SQL queries to avoid an unintended DOS.
              Queries are only allowed if you have access to the all the CKAN resources
              in the query and send the appropriate authorization.

github.com

ckan/ckan/blob/555e0960c43d0ca86066b1954e5c94aad565baa7/ckanext/datastore/backend/postgres.py#L1475


      
                      'info': {
                          'statement': [e.statement],
                          'params': [e.params],
                          'orig': [str(e.orig)]
                      }
                  })
              finally:
                  context['connection'].close()
          
          

          
def search_sql(context, data_dict):
              backend = DatastorePostgresqlBackend.get_active_backend()
              engine = backend._get_read_engine()
          
          
    context['connection'] = engine.connect()
              timeout = context.get('query_timeout', _TIMEOUT)
              _cache_types(context)
          
          
    sql = data_dict['sql'].replace('%', '%%')
          
          
    try:

raymedia · May 17, 2018, 2:00am

That’s great info. Thanks Joel.

The following are shared by my counterpart at Australian Government. Might be useful for others in future

“* There are two levels of protection. Firstly you must have a seperate datastore database with different credentials to the main ckan database(which contains the dataset metadata and user credentials you would not want read). Secondly, the datastore_search_sql API uses a separate read only permission user within the same database so it cannot alter the data it is reading.*

These two protections are checked to be in place every time CKAN starts up
https://github.com/ckan/ckan/blob/master/ckanext/datastore/backend/postgres.py#L1545

There’s an additional check in each SQL API call that tables used in a query belong to the resource/dataset declared in the API query (ie. are not postgres system tables that could be used maliciously)
https://github.com/ckan/ckan/blob/master/ckanext/datastore/backend/postgres.py#L1490 “

Kind regards,

Ray

Topic		Replies	Views
Datastore search API CKAN	2	1445	August 16, 2017
Running CKAN "headless" CKAN	0	1143	September 7, 2018
Searching by data format with DKAN (like you can with CKAN) CKAN	6	2005	August 2, 2018
API for datahub DataHub	2	792	June 26, 2017
Introducing Datashades.info, a CKAN Community Service CKAN	7	1196	December 4, 2019

OOTB controls in place for CKAN's datastore_search_sql API against SQL injection

Related Topics