Why do I not have permission to use script containing frappe.db.sql?

1

I created an API Script to perform a SQL Query. It looks like this:

query = """
/*my query here*/
"""

values = {'customer': 'my_customer'}
frappe.db.sql(query, values, as_dict=0)

When I navigate browser address bar to the URL for the API method, I get “You do not have enough permissions to complete the action”.

If I comment out the “frappe.db.sql()” line, the script runs fine. I even tested with a placeholder response and the browser receives the response just fine. Why is frappe.db.sql causing a permission error and how do I fix it?

2

What kind of query is it? I might be mistaken, but I believe the restricted Python environment used in server scripts allows only SELECT statements.

3

It contains a couple of SELECTS wrapped in CTEs. And the CTEs are used to perform a final SELECT statement.

Are you saying that Frappe Python code tries to parse the SQL to figure out what it might do? That completely boggles my mind – I never would have expected that.

4

Switched the CTEs to nested SELECT statements so that query begins with SELECT and that gets rid of the permission error.

Wowz. Just ridiculous.

5

I followed up with some personal commentary and investigatory questions on this subject:
https://discuss.frappe.io/t/suspicious-use-of-sql-parsing-as-a-security-feature-for-db-access/91150