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

security

#1

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


#2

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


#3

That’s great info. Thanks Joel.

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

“* 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