What is the role of 'Dynamic SQL' and its use cases?

Example Answer

Dynamic SQL builds a statement at runtime, which is useful when a report has optional filters or a controlled choice of sort order. I would assemble the structure from trusted fragments and bind every user-supplied value. For example, a sales report might add a date filter only when the caller supplies a date. Dynamic SQL does not provide tenant isolation by itself; the authenticated tenant must still constrain the query. The tradeoff is more combinations to test and potentially more query plans to inspect.

Design example

Keep a mapping such as {"date": "created_at", "total": "total_amount"} for selectable sort columns. Reject an unknown key. Build the optional WHERE clauses from fixed strings and pass their values separately through the driver's parameter API. Do not concatenate a raw column name or tenant ID from the request.

Parameterized static queries can handle many optional filters too. Choose dynamic structure when it simplifies the query or improves a measured plan, rather than assuming it is necessary for every report.

Practice follow-up

Which parts of your report query can be bound as parameters, and which require an allowlist?

Sources

OWASP SQL injection prevention; PostgreSQL dynamic command execution.