Code Security Guidelines

Prefer query builder over raw SQL

If you are writing simple SQL queries there is a high chance it can be achieved using the Frappe Query Builder.

Don't use frappe.db.sql for simple queries like this:

result = frappe.db.sql('select name, title, description from tabToDo where owner = "[email protected]"')

Use frappe.db.get_all instead:

result = frappe.db.get_all('ToDo', fields=['name', 'title', 'description'], filters={'owner': '[email protected]'})

Read the full API documentation.

Make your raw SQLs injection proof

If there are scenarios that you have to write raw SQL queries, make sure to account for SQL injections via user input that may be passed in a web request.

Don't use .format to substitute variables.

result = frappe.db.sql('select first_name from tabUser where name='{}'.format(user))

Pass variables to the sql method as a second parameter and they will be automatically sanitised and quoted.

result = frappe.db.sql('select first_name from tabUser where name=%s', [user])

If for some reason, you have to use .format to build your queries, make sure to sanitise your variables using frappe.db.escape.

result = frappe.db.sql('select first_name from tabUser where name={}'.format(frappe.db.escape(user)))