def post(self, query_id): query = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org) require_access(query.groups, self.current_user, not_view_only) parameter_values = collect_parameters_from_request(request.args) return run_query(query.data_source, parameter_values, query.query, query.id)
def post(self): """ Execute a query (or retrieve recent results). :qparam string query: The query text to execute :qparam number query_id: The query object to update with the result (optional) :qparam number max_age: If query results less than `max_age` seconds old are available, return them, otherwise execute the query; if omitted or -1, returns any cached result, or executes if not available. Set to zero to always execute. :qparam number data_source_id: ID of data source to query """ params = request.get_json(force=True) parameter_values = collect_parameters_from_request(request.args) query = params['query'] max_age = int(params.get('max_age', -1)) query_id = params.get('query_id', 'adhoc') data_source = models.DataSource.get_by_id_and_org(params.get('data_source_id'), self.current_org) if not has_access(data_source.groups, self.current_user, not_view_only): return {'job': {'status': 4, 'error': 'You do not have permission to run queries with this data source.'}}, 403 self.record_event({ 'action': 'execute_query', 'object_id': data_source.id, 'object_type': 'data_source', 'query': query }) return run_query(data_source, parameter_values, query, query_id, max_age)
def embed(query_id, visualization_id, org_slug=None): query = models.Query.get_by_id_and_org(query_id, current_org) require_access(query.groups, current_user, view_only) vis = query.visualizations.where(models.Visualization.id == visualization_id).first() qr = {} parameter_values = collect_parameters_from_request(request.args) if vis is not None: vis = vis.to_dict() qr = query.latest_query_data logging.info("jonhere") logging.info( settings.ALLOW_PARAMETERS_IN_EMBEDS) if settings.ALLOW_PARAMETERS_IN_EMBEDS == True and len(parameter_values) > 0: #abort(404,message="jlk") # run parameterized query # # WARNING: Note that the external query parameters # are a potential risk of SQL injections. # results = run_query_sync(query.data_source, parameter_values, query.query) logging.info("jonhere2") logging.info("results") if results is None: abort(400, message="Unable to get results for this query") else: qr = {"data": json.loads(results)} elif qr is None: abort(400, message="No Results for this query") else: qr = qr.to_dict() else: abort(404, message="Visualization not found.") record_event(current_org, current_user, { 'action': 'view', 'object_id': visualization_id, 'object_type': 'visualization', 'query_id': query_id, 'embed': True, 'referer': request.headers.get('Referer') }) client_config = {} client_config.update(settings.COMMON_CLIENT_CONFIG) qr = project(qr, ('data', 'id', 'retrieved_at')) vis = project(vis, ('description', 'name', 'id', 'options', 'query', 'type', 'updated_at')) vis['query'] = project(vis['query'], ('created_at', 'description', 'name', 'id', 'latest_query_data_id', 'name', 'updated_at')) return render_template("embed.html", client_config=json_dumps(client_config), visualization=json_dumps(vis), query_result=json_dumps(qr))
def post(self, query_id): """ Execute a query, updating the query object with the results. :param query_id: ID of query to execute Responds with query task details. """ query = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org) require_access(query.groups, self.current_user, not_view_only) parameter_values = collect_parameters_from_request(request.args) return run_query(query.data_source, parameter_values, query.query_text, query.id)
def post(self, query_id): """ Execute a query, updating the query object with the results. :param query_id: ID of query to execute Responds with query task details. """ # TODO: this should actually check for permissions, but because currently you can only # get here either with a user API key or a query one, we can just check whether it's # an api key (meaning this is a query API key, which only grants read access). if self.current_user.is_api_user(): abort(403, message="Please use a user API key.") query = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org) require_access(query.groups, self.current_user, not_view_only) parameter_values = collect_parameters_from_request(request.args) return run_query(query.data_source, parameter_values, query.query_text, query.id)
def post(self): """ Execute a query (or retrieve recent results). :qparam string query: The query text to execute :qparam number query_id: The query object to update with the result (optional) :qparam number max_age: If query results less than `max_age` seconds old are available, return them, otherwise execute the query; if omitted or -1, returns any cached result, or executes if not available. Set to zero to always execute. :qparam number data_source_id: ID of data source to query :qparam object parameters: A set of parameter values to apply to the query. """ params = request.get_json(force=True) query = params['query'] max_age = params.get('max_age', -1) # max_age might have the value of None, in which case calling int(None) will fail if max_age is None: max_age = -1 max_age = int(max_age) query_id = params.get('query_id', 'adhoc') parameters = params.get('parameters', collect_parameters_from_request(request.args)) parameterized_query = ParameterizedQuery(query) data_source = models.DataSource.get_by_id_and_org(params.get('data_source_id'), self.current_org) if not has_access(data_source, self.current_user, not_view_only): return {'job': {'status': 4, 'error': 'You do not have permission to run queries with this data source.'}}, 403 self.record_event({ 'action': 'execute_query', 'object_id': data_source.id, 'object_type': 'data_source', 'query': query, 'query_id': query_id, 'parameters': parameters }) return run_query(parameterized_query, parameters, data_source, query_id, max_age)
def post(self): params = request.get_json(force=True) parameter_values = collect_parameters_from_request(request.args) query = params['query'] max_age = int(params.get('max_age', -1)) query_id = params.get('query_id', 'adhoc') data_source = models.DataSource.get_by_id_and_org(params.get('data_source_id'), self.current_org) if not has_access(data_source.groups, self.current_user, not_view_only): return {'job': {'status': 4, 'error': 'You do not have permission to run queries with this data source.'}}, 403 self.record_event({ 'action': 'execute_query', 'timestamp': int(time.time()), 'object_id': data_source.id, 'object_type': 'data_source', 'query': query }) return run_query(data_source, parameter_values, query, query_id, max_age)
def post(self): """ Execute a query (or retrieve recent results). :qparam string query: The query text to execute :qparam number query_id: The query object to update with the result (optional) :qparam number max_age: If query results less than `max_age` seconds old are available, return them, otherwise execute the query; if omitted, always execute :qparam number data_source_id: ID of data source to query """ params = request.get_json(force=True) parameter_values = collect_parameters_from_request(request.args) query = params['query'] max_age = int(params.get('max_age', -1)) query_id = params.get('query_id', 'adhoc') data_source = models.DataSource.get_by_id_and_org( params.get('data_source_id'), self.current_org) if not has_access(data_source.groups, self.current_user, view_only): return { 'job': { 'status': 4, 'error': 'You do not have permission to run queries with this data source.' } }, 403 self.record_event({ 'action': 'execute_query', 'timestamp': int(time.time()), 'object_id': data_source.id, 'object_type': 'data_source', 'query': query }) return run_query(data_source, parameter_values, query, query_id, max_age)
def post(self, query_id): """ Execute a query, updating the query object with the results. :param query_id: ID of query to execute Responds with query task details. """ # TODO: this should actually check for permissions, but because currently you can only # get here either with a user API key or a query one, we can just check whether it's # an api key (meaning this is a query API key, which only grants read access). if self.current_user.is_api_user(): abort(403, message="Please use a user API key.") query = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org) require_access(query.groups, self.current_user, not_view_only) parameter_values = collect_parameters_from_request(request.args) parameterized_query = ParameterizedQuery(query.query_text) return run_query(parameterized_query, parameter_values, query.data_source, query.id)
def post(self): """ Execute a query (or retrieve recent results). :qparam string query: The query text to execute :qparam number query_id: The query object to update with the result (optional) :qparam number max_age: If query results less than `max_age` seconds old are available, return them, otherwise execute the query; if omitted or -1, returns any cached result, or executes if not available. Set to zero to always execute. :qparam number data_source_id: ID of data source to query :qparam object parameters: A set of parameter values to apply to the query. """ params = request.get_json(force=True) query = params['query'] max_age = params.get('max_age', -1) # max_age might have the value of None, in which case calling int(None) will fail if max_age is None: max_age = -1 max_age = int(max_age) query_id = params.get('query_id', 'adhoc') parameters = params.get('parameters', collect_parameters_from_request(request.args)) parameterized_query = ParameterizedQuery(query, org=self.current_org) data_source_id = params.get('data_source_id') if data_source_id: data_source = models.DataSource.get_by_id_and_org(params.get('data_source_id'), self.current_org) else: return error_messages['select_data_source'] if not has_access(data_source, self.current_user, not_view_only): return error_messages['no_permission'] return run_query(parameterized_query, parameters, data_source, query_id, max_age)
def post(self): """ Execute a query (or retrieve recent results). :qparam string query: The query text to execute :qparam number query_id: The query object to update with the result (optional) :qparam number max_age: If query results less than `max_age` seconds old are available, return them, otherwise execute the query; if omitted, always execute :qparam number data_source_id: ID of data source to query """ print("#QueryResultListResource POST hit") params = request.get_json(force=True) parameter_values = collect_parameters_from_request(request.args) query = params['query'] max_age = int(params.get('max_age', -1)) query_id = params.get('query_id', 'adhoc') # print(params.get('data_source_id')) # data_source = models.DataSource.get_by_id(params.get('data_source_id')) data_source = models.DataSource.get_by_id_and_org(params.get('data_source_id'), self.current_org) # print("#end get_by_id_and_org") # if not has_access(data_source.groups, self.current_user, not_view_only): # return {'job': {'status': 4, 'error': 'You do not have permission to run queries with this data source.'}}, 403 print("#QueryResultListResource -> requery ",query) return run_query(data_source, parameter_values, query, query_id, max_age)
def embedjon(query_id, org_slug=None): visualizations=1 query = models.Query.get_by_id_and_org(query_id, current_org) require_access(query.groups, current_user, view_only) qr = {} parameter_values = collect_parameters_from_request(request.args) qr = query.latest_query_data if settings.ALLOW_PARAMETERS_IN_EMBEDS == True and len(parameter_values) > 0: # run parameterized query # # WARNING: Note that the external query parameters # are a potential risk of SQL injections. # results = run_query_sync(query.data_source, parameter_values, query.query) if results is None: abort(400, message="Unable to get results for this query") else: qr = {"data": json.loads(results)} elif qr is None: abort(400, message="No Results for this query") else: qr = qr.to_dict() record_event(current_org, current_user, { 'action': 'embedjon', 'query_id': query_id, 'embed': True, 'referer': request.headers.get('Referer') }) client_config = {} client_config.update(settings.COMMON_CLIENT_CONFIG) qr = project(qr, ('data', 'id', 'retrieved_at')) return json_dumps(qr)
def test_ignores_non_prefixed_values(self): self.assertEqual({}, collect_parameters_from_request({'test': 1}))
def get(self, query_id=None, query_result_id=None, filetype='json'): """ Retrieve query results. :param number query_id: The ID of the query whose results should be fetched :param number query_result_id: the ID of the query result to fetch :param string filetype: Format to return. One of 'json', 'xlsx', or 'csv'. Defaults to 'json'. :<json number id: Query result ID :<json string query: Query that produced this result :<json string query_hash: Hash code for query text :<json object data: Query output :<json number data_source_id: ID of data source that produced this result :<json number runtime: Length of execution time in seconds :<json string retrieved_at: Query retrieval date/time, in ISO format """ # TODO: # This method handles two cases: retrieving result by id & retrieving result by query id. # They need to be split, as they have different logic (for example, retrieving by query id # should check for query parameters and shouldn't cache the result). should_cache = query_result_id is not None parameter_values = collect_parameters_from_request(request.args) max_age = int(request.args.get('maxAge', 0)) query_result = None if query_result_id: query_result = get_object_or_404(models.QueryResult.get_by_id_and_org, query_result_id, self.current_org) if query_id is not None: query = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org) if query_result is None and query is not None: if settings.ALLOW_PARAMETERS_IN_EMBEDS and parameter_values: query_result = run_query_sync(query.data_source, parameter_values, query.query_text, max_age=max_age) elif query.latest_query_data_id is not None: query_result = get_object_or_404(models.QueryResult.get_by_id_and_org, query.latest_query_data_id, self.current_org) if query is not None and query_result is not None and self.current_user.is_api_user(): if query.query_hash != query_result.query_hash: abort(404, message='No cached result found for this query.') if query_result: require_access(query_result.data_source.groups, self.current_user, view_only) if isinstance(self.current_user, models.ApiUser): event = { 'user_id': None, 'org_id': self.current_org.id, 'action': 'api_get', 'api_key': self.current_user.name, 'file_type': filetype, 'user_agent': request.user_agent.string, 'ip': request.remote_addr } if query_id: event['object_type'] = 'query' event['object_id'] = query_id else: event['object_type'] = 'query_result' event['object_id'] = query_result_id record_event.delay(event) if filetype == 'json': response = self.make_json_response(query_result) elif filetype == 'xlsx': response = self.make_excel_response(query_result) else: response = self.make_csv_response(query_result) if len(settings.ACCESS_CONTROL_ALLOW_ORIGIN) > 0: self.add_cors_headers(response.headers) if should_cache: response.headers.add_header('Cache-Control', 'private,max-age=%d' % ONE_YEAR) return response else: abort(404, message='No cached result found for this query.')
def test_takes_prefixed_values(self): self.assertDictEqual({'test': 1, 'something_else': 'test'}, collect_parameters_from_request({'p_test': 1, 'p_something_else': 'test'}))
def test_takes_prefixed_values(self): self.assertDictEqual( {"test": 1, "something_else": "test"}, collect_parameters_from_request({"p_test": 1, "p_something_else": "test"}), )
def get(self, query_id=None, query_result_id=None, filetype="json"): """ Retrieve query results. :param number query_id: The ID of the query whose results should be fetched :param number query_result_id: the ID of the query result to fetch :param string filetype: Format to return. One of 'json', 'xlsx', or 'csv'. Defaults to 'json'. :<json number id: Query result ID :<json string query: Query that produced this result :<json string query_hash: Hash code for query text :<json object data: Query output :<json number data_source_id: ID of data source that produced this result :<json number runtime: Length of execution time in seconds :<json string retrieved_at: Query retrieval date/time, in ISO format """ # TODO: # This method handles two cases: retrieving result by id & retrieving result by query id. # They need to be split, as they have different logic (for example, retrieving by query id # should check for query parameters and shouldn't cache the result). should_cache = query_result_id is not None parameter_values = collect_parameters_from_request(request.args) max_age = int(request.args.get("maxAge", 0)) query_result = None query = None if query_result_id: query_result = get_object_or_404( models.QueryResult.get_by_id_and_org, query_result_id, self.current_org) if query_id is not None: query = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org) if (query_result is None and query is not None and query.latest_query_data_id is not None): query_result = get_object_or_404( models.QueryResult.get_by_id_and_org, query.latest_query_data_id, self.current_org, ) if (query is not None and query_result is not None and self.current_user.is_api_user()): if query.query_hash != query_result.query_hash: abort(404, message="No cached result found for this query.") if query_result: require_access(query_result.data_source, self.current_user, view_only) if isinstance(self.current_user, models.ApiUser): event = { "user_id": None, "org_id": self.current_org.id, "action": "api_get", "api_key": self.current_user.name, "file_type": filetype, "user_agent": request.user_agent.string, "ip": request.remote_addr, } if query_id: event["object_type"] = "query" event["object_id"] = query_id else: event["object_type"] = "query_result" event["object_id"] = query_result_id self.record_event(event) response_builders = { 'json': self.make_json_response, 'xlsx': self.make_excel_response, 'csv': self.make_csv_response, 'tsv': self.make_tsv_response } response = response_builders[filetype](query_result) if len(settings.ACCESS_CONTROL_ALLOW_ORIGIN) > 0: self.add_cors_headers(response.headers) if should_cache: response.headers.add_header("Cache-Control", "private,max-age=%d" % ONE_YEAR) filename = get_download_filename(query_result, query, filetype) filenames = content_disposition_filenames(filename) response.headers.add("Content-Disposition", "attachment", **filenames) return response else: abort(404, message="No cached result found for this query.")
def get(self, query_id=None, query_result_id=None, filetype='json'): """ Retrieve query results. :param number query_id: The ID of the query whose results should be fetched :param number query_result_id: the ID of the query result to fetch :param string filetype: Format to return. One of 'json', 'xlsx', or 'csv'. Defaults to 'json'. :<json number id: Query result ID :<json string query: Query that produced this result :<json string query_hash: Hash code for query text :<json object data: Query output :<json number data_source_id: ID of data source that produced this result :<json number runtime: Length of execution time in seconds :<json string retrieved_at: Query retrieval date/time, in ISO format """ # TODO: # This method handles two cases: retrieving result by id & retrieving result by query id. # They need to be split, as they have different logic (for example, retrieving by query id # should check for query parameters and shouldn't cache the result). should_cache = query_result_id is not None parameter_values = collect_parameters_from_request(request.args) max_age = int(request.args.get('maxAge', 0)) query_result = None if query_result_id: query_result = get_object_or_404( models.QueryResult.get_by_id_and_org, query_result_id, self.current_org) # this is the table only result - a new one every time execute button is clicked # don't update this variable - can't call another func here cause we don't have query_id to update (maybe adhoc) else: query_result = None if query_result: require_access(query_result.data_source.groups, self.current_user, view_only) if isinstance(self.current_user, models.ApiUser): event = { 'user_id': None, 'org_id': self.current_org.id, 'action': 'api_get', 'timestamp': int(time.time()), 'api_key': self.current_user.name, 'file_type': filetype, 'user_agent': request.user_agent.string, 'ip': request.remote_addr } if query_id: event['object_type'] = 'query' event['object_id'] = query_id else: event['object_type'] = 'query_result' event['object_id'] = query_result_id record_event.delay(event) query_result_dict = query_result.to_dict() # TODO saving again even saved queries - Solved # but correct way is through frontend js ... sepearate endpoint, here same string saved again as new query won't auto visualise # another bug ? if sql directly typed again - then auto visualize wont happen mapper = get_mappings() file_name = None saved_query_id = None for k in mapper: if (mapper[k][0] == query_result_dict['query']): file_name = mapper[k][1] query_text = mapper[k][2] #query_name = None if file_name: if (not query_result.is_same_query(query_text, query_result.data_source)): # 1. save the query 2. get predefined visual json 3. add json to query object visualizations visualization_resource = VisualizationListResource() try: saved_query_id = visualization_resource.save_and_add_visual( query_result_dict, file_name, query_text) except Exception as e: abort(500, e.message) if saved_query_id: query_result_dict['query_id'] = saved_query_id if filetype == 'json': response = self.make_json_response(query_result_dict) elif filetype == 'xlsx': response = self.make_excel_response(query_result) else: response = self.make_csv_response(query_result) if len(settings.ACCESS_CONTROL_ALLOW_ORIGIN) > 0: self.add_cors_headers(response.headers) if should_cache: response.headers.add_header('Cache-Control', 'max-age=%d' % ONE_YEAR) return response else: abort(404, message='No cached result found for this query.')
def get(self, query_id=None, query_result_id=None, filetype='json'): """ Retrieve query results. :param number query_id: The ID of the query whose results should be fetched :param number query_result_id: the ID of the query result to fetch :param string filetype: Format to return. One of 'json', 'xlsx', or 'csv'. Defaults to 'json'. :<json number id: Query result ID :<json string query: Query that produced this result :<json string query_hash: Hash code for query text :<json object data: Query output :<json number data_source_id: ID of data source that produced this result :<json number runtime: Length of execution time in seconds :<json string retrieved_at: Query retrieval date/time, in ISO format """ # TODO: # This method handles two cases: retrieving result by id & retrieving result by query id. # They need to be split, as they have different logic (for example, retrieving by query id # should check for query parameters and shouldn't cache the result). should_cache = query_result_id is not None parameter_values = collect_parameters_from_request(request.args) max_age = int(request.args.get('maxAge', 0)) query_result = None query = None if query_result_id: query_result = get_object_or_404( models.QueryResult.get_by_id_and_org, query_result_id, self.current_org) if query_id is not None: query = get_object_or_404(models.Query.get_by_id_and_org, query_id, self.current_org) if query_result is None and query is not None: if settings.ALLOW_PARAMETERS_IN_EMBEDS and parameter_values: query_result = run_query_sync(query.data_source, parameter_values, query.query_text, max_age=max_age) elif query.latest_query_data_id is not None: query_result = get_object_or_404( models.QueryResult.get_by_id_and_org, query.latest_query_data_id, self.current_org) if query is not None and query_result is not None and self.current_user.is_api_user( ): if query.query_hash != query_result.query_hash: abort(404, message='No cached result found for this query.') if query_result: require_access(query_result.data_source.groups, self.current_user, view_only) if isinstance(self.current_user, models.ApiUser): event = { 'user_id': None, 'org_id': self.current_org.id, 'action': 'api_get', 'api_key': self.current_user.name, 'file_type': filetype, 'user_agent': request.user_agent.string, 'ip': request.remote_addr } if query_id: event['object_type'] = 'query' event['object_id'] = query_id else: event['object_type'] = 'query_result' event['object_id'] = query_result_id self.record_event(event) if filetype == 'json': response = self.make_json_response(query_result) elif filetype == 'xlsx': response = self.make_excel_response(query_result) else: response = self.make_csv_response(query_result) if len(settings.ACCESS_CONTROL_ALLOW_ORIGIN) > 0: self.add_cors_headers(response.headers) if should_cache: response.headers.add_header('Cache-Control', 'private,max-age=%d' % ONE_YEAR) filename = get_download_filename(query_result, query, filetype) response.headers.add_header( "Content-Disposition", 'attachment; filename="{}"'.format(filename.encode("utf-8"))) return response else: abort(404, message='No cached result found for this query.')
def post(self): """ Execute a query (or retrieve recent results). :qparam string query: The query text to execute :qparam number query_id: The query object to update with the result (optional) :qparam number max_age: If query results less than `max_age` seconds old are available, return them, otherwise execute the query; if omitted, always execute :qparam number data_source_id: ID of data source to query """ params = request.get_json(force=True) parameter_values = collect_parameters_from_request(request.args) query = params['query'] max_age = int(params.get('max_age', -1)) query_id = params.get('query_id', 'adhoc') data_source = models.DataSource.get_by_id_and_org( params.get('data_source_id'), self.current_org) if not has_access(data_source.groups, self.current_user, not_view_only): return { 'job': { 'status': 4, 'error': 'You do not have permission to run queries with this data source.' } }, 403 if settings.REMOTE_RESOURCE_RESTRICTION_ENABLED and remote_resource_restriction( parameter_values, self.current_user, request): return { 'job': { 'status': 4, 'error': 'You have a remote resource restriction on the provided parameters.' } }, 403 self.record_event({ 'action': 'execute_query', 'timestamp': int(time.time()), 'object_id': data_source.id, 'object_type': 'data_source', 'query': query }) try: return run_query(data_source, parameter_values, query, query_id, max_age) except jwt.ExpiredSignatureError: return { 'job': { 'status': 4, 'error': 'Your authentication credentials have expired, please re-login.', 'provider': settings.REMOTE_JWT_EXPIRED_ENDPOINT + urllib.quote_plus(request.referrer or settings.ROOT_UI_URL) } }, 401