def _execute_single_statement(context, sql_string, where_values): if not datastore_helpers.is_single_statement(sql_string): raise ValidationError({'query': ['Query is not a single statement.']}) results = context['connection'].execute(sql_string, [where_values]) return results
def test_is_single_statement(self): singles = ['SELECT * FROM footable', 'SELECT * FROM "bartable"', 'SELECT * FROM "bartable";', 'SELECT * FROM "bart;able";', "select 'foo'||chr(59)||'bar'"] multiples = ['SELECT * FROM abc; SET LOCAL statement_timeout to' 'SET LOCAL statement_timeout to; SELECT * FROM abc', 'SELECT * FROM "foo"; SELECT * FROM "abc"'] for single in singles: assert datastore_helpers.is_single_statement(single) is True for multiple in multiples: assert datastore_helpers.is_single_statement(multiple) is False
def _execute_single_statement(context, sql_string, where_values): if not datastore_helpers.is_single_statement(sql_string): raise ValidationError({"query": ["Query is not a single statement."]}) results = context["connection"].execute(sql_string, [where_values]) return results
def query_extent(data_dict, connection=None): """ Return the spatial query extent of a datastore search @param data_dict: Dictionary defining the search @returns a dictionary defining: { total_count: The total number of rows in the query, geom_count: The number of rows that have a geom, bounds: ((lat min, long min), (lat max, long max)) for the queries rows """ r = toolkit.get_action('datastore_search')({}, data_dict) if 'total' not in r or r['total'] == 0: return { 'total_count': 0, 'geom_count': 0, 'bounds': None } result = { 'total_count': r['total'], 'bounds': None } field_types = dict([(f['id'], f['type']) for f in r['fields']]) field_types['_id'] = 'int' # Call plugin to obtain correct where statement (ts_query, where_clause, values) = invoke_search_plugins(data_dict, field_types) # Prepare and run our query query = """ SELECT COUNT(r) AS count, ST_YMIN(ST_EXTENT(r)) AS ymin, ST_XMIN(ST_EXTENT(r)) AS xmin, ST_YMAX(ST_EXTENT(r)) AS ymax, ST_XMAX(ST_EXTENT(r)) AS xmax FROM ( SELECT "{geom_field}" AS r FROM "{resource_id}" {ts_query} {where_clause} ) _tilemap_sub """.format( geom_field=config['postgis.field'], resource_id=data_dict['resource_id'], where_clause=where_clause, ts_query=ts_query ) if not is_single_statement(query): raise datastore_db.ValidationError({ 'query': ['Query is not a single statement.'] }) with get_connection(connection) as c: query_result = c.execute(query, values) r = query_result.fetchone() result['geom_count'] = r['count'] if result['geom_count'] > 0: result['bounds'] = ((r['ymin'], r['xmin']), (r['ymax'], r['xmax'])) 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 datastore_helpers.is_single_statement(sql): raise p.toolkit.ValidationError({ 'query': ['Query is not a single statement.'] }) 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
def run_stats_query(select, resource_id, ts_query, where_clause, group_by, values): query = 'SELECT {select} FROM "{resource_id}" {ts_query} {where_clause} {group_by}'.format( select=select, resource_id=resource_id, where_clause=where_clause, ts_query=ts_query, group_by=group_by) if not is_single_statement(query): raise datastore_db.ValidationError( {'query': ['Query is not a single statement.']}) # The interfaces.IDatastore return SQL to be directly executed # So just use an sqlalchemy connection, rather than the API # So we don't have to faff around converting to pure SQL engine = _get_engine() with engine.begin() as connection: try: query_result = connection.execute(query, values) return query_result.fetchall() except (DatabaseError, DBAPIError): pass
def run_stats_query(select, resource_id, ts_query, where_clause, group_by, values): query = 'SELECT {select} FROM "{resource_id}" {ts_query} {where_clause} {group_by}'.format( select=select, resource_id=resource_id, where_clause=where_clause, ts_query=ts_query, group_by=group_by ) if not is_single_statement(query): raise datastore_db.ValidationError({ 'query': ['Query is not a single statement.'] }) # The interfaces.IDatastore return SQL to be directly executed # So just use an sqlalchemy connection, rather than the API # So we don't have to faff around converting to pure SQL engine = _get_engine() with engine.begin() as connection: try: query_result = connection.execute(query, values) return query_result.fetchall() except (DatabaseError, DBAPIError): pass