def test_is_single_statement(self):
singles = ['SELECT * FROM footable',
'SELECT * FROM "bartable"',
'SELECT * FROM "bartable";',
"select 'foo'||chr(59)||'bar'"]
for single in singles:
assert db._is_single_statement(single) is True
multiples = ['SELECT * FROM abc; SET LOCAL statement_timeout to'
'SET LOCAL statement_timeout to; SELECT * FROM abc',
'SELECT * FROM "foo"; SELECT * FROM "abc"']
for multiple in multiples:
assert db._is_single_statement(multiple) is False
def test_is_single_statement(self):
singles = ['SELECT * FROM footable',
'SELECT * FROM "bartable"',
'SELECT * FROM "bartable";',
"select 'foo'||chr(59)||'bar'"]
for single in singles:
assert db._is_single_statement(single) is True
multiples = ['SELECT * FROM abc; SET LOCAL statement_timeout to'
'SET LOCAL statement_timeout to; SELECT * FROM abc',
'SELECT * FROM "foo"; SELECT * FROM "abc"']
for multiple in multiples:
assert db._is_single_statement(multiple) is False
def datastore_search_sql(context, data_dict):
'''Execute SQL-Queries on the datastore.
:param sql: a single sql select statement
:type sql: string
:returns: a dictionary containing the search results.
keys: fields: columns for results
records: results from the query
:rtype: dictionary
'''
sql = _get_or_bust(data_dict, 'sql')
if not db._is_single_statement(sql):
raise p.toolkit.ValidationError({
'query': ['Query is not a single statement or contains semicolons.'],
'hint': [('If you want to use semicolons, use character encoding'
'(; equals chr(59)) and string concatenation (||). ')]
})
p.toolkit.check_access('datastore_search', context, data_dict)
data_dict['connection_url'] = pylons.config['ckan.datastore.read_url']
result = db.search_sql(context, data_dict)
result.pop('id', None)
result.pop('connection_url')
return result
def datastore_search_sql(context, data_dict):
'''Execute SQL-Queries on the datastore.
:param sql: a single sql select statement
:type sql: string
:returns: a dictionary containing the search results.
keys: fields: columns for results
records: results from the query
:rtype: dictionary
'''
sql = _get_or_bust(data_dict, 'sql')
if not db._is_single_statement(sql):
raise p.toolkit.ValidationError({
'query':
['Query is not a single statement or contains semicolons.'],
'hint': [('If you want to use semicolons, use character encoding'
'(; equals chr(59)) and string concatenation (||). ')]
})
p.toolkit.check_access('datastore_search', context, data_dict)
data_dict['connection_url'] = pylons.config['ckan.datastore.read_url']
result = db.search_sql(context, data_dict)
result.pop('id', None)
result.pop('connection_url')
return result
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/sql/.html>`_.
There is an enforced timeout on SQL queries to avoid an unintended DOS.
DataStore resource that belong to a private CKAN resource cannot be searched with
this action. Use :meth:`~ckanext.datastore.logic.action.datastore_search` instead.
.. note:: This action is only available when using PostgreSQL 9.X and using a read-only user on the database.
It is not available in :ref:`legacy mode<legacy-mode>`.
:param sql: a single SQL select statement
:type sql: string
**Results:**
The result of this action is a dictionary with the following keys:
:rtype: A dictionary with the following keys
:param fields: fields/columns and their extra metadata
:type fields: list of dictionaries
:param records: list of matching results
:type records: list of dictionaries
'''
sql = _get_or_bust(data_dict, 'sql')
if not db._is_single_statement(sql):
raise p.toolkit.ValidationError({
'query':
['Query is not a single statement or contains semicolons.'],
'hint': [('If you want to use semicolons, use character encoding'
'(; equals chr(59)) and string concatenation (||). ')]
})
p.toolkit.check_access('datastore_search', context, data_dict)
data_dict['connection_url'] = pylons.config['ckan.datastore.read_url']
result = db.search_sql(context, data_dict)
result.pop('id', None)
result.pop('connection_url')
return result
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/sql/.html>`_.
There is an enforced timeout on SQL queries to avoid an unintended DOS.
DataStore resource that belong to a private CKAN resource cannot be searched with
this action. Use :meth:`~ckanext.datastore.logic.action.datastore_search` instead.
.. note:: This action is only available when using PostgreSQL 9.X and using a read-only user on the database.
It is not available in :ref:`legacy mode<legacy_mode>`.
:param sql: a single SQL select statement
:type sql: string
**Results:**
The result of this action is a dictionary with the following keys:
:rtype: A dictionary with the following keys
:param fields: fields/columns and their extra metadata
:type fields: list of dictionaries
:param records: list of matching results
:type records: list of dictionaries
'''
sql = _get_or_bust(data_dict, 'sql')
if not db._is_single_statement(sql):
raise p.toolkit.ValidationError({
'query': ['Query is not a single statement or contains semicolons.'],
'hint': [('If you want to use semicolons, use character encoding'
'(; equals chr(59)) and string concatenation (||). ')]
})
p.toolkit.check_access('datastore_search_sql', context, data_dict)
data_dict['connection_url'] = pylons.config['ckan.datastore.read_url']
result = db.search_sql(context, data_dict)
result.pop('id', None)
result.pop('connection_url')
return result
