API request throws SQL syntax error

Server Error

Traceback (most recent call last):
  File "/home/erpdev/frappe-bench/apps/frappe/frappe/app.py", line 62, in application
    response = frappe.api.handle()
  File "/home/erpdev/frappe-bench/apps/frappe/frappe/api.py", line 119, in handle
    doctype, **frappe.local.form_dict)})
           :              :              :              :              :
  File "/home/erpdev/frappe-bench/env/lib/python3.6/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/erpdev/frappe-bench/env/lib/python3.6/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError:
 (1064, "You have an error in your SQL syntax;
      check the manual that corresponds to your MariaDB server version for the right
      syntax to use near
'condition, webhook_json, request_url\n\t\t\tfrom `tabWebhook`\n\t\t\twhere `tabWebhook`.' 
at line 1")

My actual request was :

{{Site}}/api/resource/Webhook
     ?filters=[["webhook_doctype", "=", "Sales Invoice"], 
               ["webhook_docevent", "=", "on_change"]]
     &fields=["condition", "webhook_json", "request_url"];

Clearly, "condition" is a MariaDb reserved word, but what to do?

The trick is to "escape" the word in the same way you would if you were coding directly in SQL

{{Site}}/api/resource/Webhook
     ?filters=[["webhook_doctype", "=", "Sales Invoice"], 
               ["webhook_docevent", "=", "on_change"]]
     &fields=["`condition`", "webhook_json", "request_url"];

The "escape" character is the back tick " ` ". The API passes it directly through, unaltered, to the database manager.

2 Likes

Bravo. I think this can be included in the base framework itself. Small PR idea :stuck_out_tongue:

3 Likes

If I can, whenever I find little “gotchas” like that I note them in forums as easily googleable Q & As.

Presumably, eventually, they help out some else who maybe lacks the experience to spot the solution quickly.

2 Likes

This solution got me curious, so I looked and saw that all SQL in Frappe is purely concatenated strings, meaning SQL Injection is an ever-present possibility without total vigilance. Ouch. I’ll have to find out if there’s a standard way to harden this to avoid having to do the above all the time.

1 Like

You might want to review the security concerns raised by other users over the years; users who have gone away and aren’t coming back.