Secure SQL Queries in Frappe That Respect Permissions

Database engines use C/C++, which makes them highly optimized for filtering and joining data. That’s why joining and filtering is much faster in SQL than doing it in Python.

Combined with database indexing, this makes SQL an entirely different beast.

SQL is the universal language of data. Most developers already know SQL, making it simpler for existing devs to write the code and for new devs to understand it.

✕ User Permissions — e.g., “User A can only see Territory B”

✕ Role Permissions — e.g., “Sales Users cannot read Purchase Receipts”

1. get_list (docs)

Takes the name of a DocType, a list of filters, and fetches all matching entries. Works well for single-DocType fetches.

But when you need joins (e.g. customers connected to a department), it falls short — you must fetch data into Python, creating a bottleneck.

2. Query Builder

Although some sources claim it handles permissions, in practice it is often unreliable.

① Import the helper

from frappe.desk.reportview \
    import build_match_conditions

② Fetch the permissions

permission_sql = build_match_conditions(
    "Sales Order"
)

③ Handle aliases CRUCIAL

The system returns a string with the full table name. If your SQL uses an alias, replace it to avoid DB errors.

permission_sql = permission_sql.replace(
    "`tabSales Order`",
    "so"
)

④ Inject into your query

if permission_sql:
    where_clause += \
        f" AND {permission_sql}"
    # Empty = user has all perms
    # — prevents syntax error

data = frappe.db.sql(f"""
    SELECT
        so.name,
        so.grand_total
    FROM `tabSales Order` so
    WHERE
        so.docstatus = 1
        AND {permission_sql}
""", as_dict=True)

This query now only returns records the user can access.

⚠ Permission Error

If the user lacks access to a DocType, the function raises a PermissionError. Handle it:

try:
    permission_sql = \
        build_match_conditions(
            "Purchase Order"
        )
except frappe.PermissionError:
    # No read access
    permission_sql = "1=0"

Empty string: If no restrictions exist, the function returns an empty string. Handle this to avoid SQL syntax errors.

One DocType at a time: Call the function for each DocType individually and combine the conditions.

import frappe
from frappe.desk.reportview import \
    build_match_conditions

def get_permission_filters(
    doctype_map
):
    """
    SQL conditions for multiple
    DocTypes.

    Args:
        doctype_map (dict):
            Key = DocType
            Value = Alias
            {"Sales Order": "so"}

    Returns:
        list of SQL conditions.
        Denied -> ["1=0"]
    """
    conditions = []
    for dt, alias in \
            doctype_map.items():
        try:
            rule = \
                build_match_conditions(dt)
            if rule:
                if alias:
                    rule = rule.replace(
                        f"`tab{dt}`",
                        alias
                    )
                conditions.append(
                    f"({rule})"
                )
        except frappe.PermissionError:
            return ["1=0"]
    return conditions