Secure Coding and Application Security Questions

Direct answer: A reporting dashboard that builds SQL by concatenating filters into a WHERE clause is vulnerable to injection through any filter value the caller controls, and the fix for a BI-style query layer needs an extra layer beyond basic parameterization: the query STRUCTURE itself (which columns, how many filters) often varies dynamically, which plain parameterized values alone don't cover.

The risk. A dashboard filter UI (in a Looker-, Power BI-, or Tableau-style query layer) typically builds a WHERE clause by joining a list of active filters: WHERE region = 'US' AND category = 'Electronics'. If any filter value is inserted via string concatenation rather than a bound parameter, an attacker who controls a filter value (directly, or via a saved/shared dashboard link with query-string parameters) can break out of the intended clause the same way as any other SQL injection.

Safe alternatives, verified for the value-injection case and the column-allowlisting case together:

ALLOWED_COLUMNS = {"region", "amount"}

def safe_filter_query(conn, column, value):
    if column not in ALLOWED_COLUMNS:
        raise ValueError(f"unknown filter column: {column}")
    sql = f"SELECT * FROM sales WHERE {column} = ?"
    return conn.execute(sql, (value,)).fetchall()

I verified three cases by execution: a legitimate filter (region = 'US') returns the expected single row; an injection attempt through the VALUE (region = "US' OR '1'='1") returns nothing, because the value is always parameterized regardless of which column was chosen; and an injection attempt through the COLUMN NAME itself (a malicious identifier like region; DROP TABLE sales;--) is rejected with a ValueError before it ever reaches the database, because column identifiers can't be parameterized the same way values can - they have to be checked against an explicit allowlist.

Additional patterns for a BI query layer specifically:

• Parameterized queries for every filter value, exactly as above.
• Allowlisting for anything that varies the query's STRUCTURE (which columns are selectable, which tables are joinable) rather than just its values - this is the part a generic "always parameterize" rule misses, because a reporting tool's whole value proposition is letting users choose what to query, which means some part of the query text itself is dynamic by design.
• Escaping LIKE wildcards: for a "contains" or "starts with" text filter using LIKE, the wildcard characters % and _ inside a legitimate search term need to be escaped (not to prevent injection, since the value is still parameterized, but to prevent a user's literal search for "50% off" from being silently interpreted as a wildcard pattern instead of a literal string). Concretely, using a single backslash as the SQL ESCAPE character:

def escape_like(term):
    return term.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_")

bound as WHERE name LIKE ? ESCAPE '\' with the parameter f"%{escape_like(term)}%". I verified this by execution against a real sqlite3 table (sqlite3 supports the standard LIKE ... ESCAPE clause): a legitimate search for "50% off" escapes to 50\% off (one backslash before the literal %) and, bound as the parameter, correctly matches the row containing "50% off". I also executed the tempting-but-wrong version that doubles every backslash a second time (term.replace("\\\\", "\\\\\\\\").replace("%", "\\\\%").replace("_", "\\\\_")) against the same table: it produces two backslashes before the % (50\\% off), and against ESCAPE '\' the database consumes the first backslash as an escaped literal backslash and reads the second as an unescaped wildcard again, so the query returns zero rows for the exact literal text it is supposed to match. The correct version escapes each special character exactly once; get the backslash count wrong and the mitigation silently breaks legitimate search instead of just failing to protect anything.

Trade-offs and pitfalls: the allowlist for selectable columns/tables needs to be maintained alongside the actual schema and the BI tool's exposed field list, or it either breaks legitimate new fields (too strict) or silently permits querying a field that was supposed to be hidden from this dashboard's audience (too loose, e.g. an internal cost field accidentally exposed through a generic "any column" filter). This is a genuinely harder problem than single-value parameterization, and it's worth treating the allowlist itself as security-sensitive configuration reviewed on the same cadence as an access-control policy, not as a one-time setup step.

Direct answer: When a tool needs to build dynamic SQL where the query STRUCTURE itself varies (not just filter values), plain parameterized queries aren't enough on their own, because a placeholder can only stand in for a value, never for a table name, column name, or clause shape - the injection risk moves from "what value is being compared" to "what SQL is being assembled at all."

Structured elaboration - why this is a genuinely harder problem than basic parameterization.

A standard parameterized query (WHERE status = ?) protects the VALUE side of a comparison. But an ad-hoc analysis tool that lets analysts build subqueries and CTEs based on their own input needs to vary things a parameter placeholder can't cover: which columns appear in the SELECT list, which tables are joined, how many WHERE conditions exist, whether a subquery references another CTE. If any of that structure is built by string-assembling raw user input, you have an injection surface even if every individual VALUE is properly parameterized.

Design approach to preserve flexibility without raw string assembly:

1. Allowlist every identifier (table names, column names) against a known-safe set derived from the actual schema, never accepted as free-text from the user. This is the direct extension of the value/identifier distinction: values get parameterized, identifiers get validated against an allowlist.
2. Use a query-builder AST instead of raw string concatenation. Rather than assembling SQL text directly, build an intermediate representation (a list of validated column references, a list of validated join conditions, a list of parameterized filter predicates) and have a single, trusted code path render that structure into final SQL. This means user input never directly touches the SQL string; it only ever populates a constrained data structure that the renderer controls.
3. Sandbox the execution role. Run these ad-hoc, semi-user-authored queries under a database role with genuinely minimal privileges - read-only, scoped to specific schemas/views, with query timeouts and row limits - so that even a successful bypass of the allowlisting has bounded impact (can't write data, can't read tables outside the intended scope, can't run an unbounded query that exhausts resources).
4. Constrain subquery/CTE nesting depth and complexity structurally (a maximum number of CTEs, a maximum join count) both as a security boundary and as a practical safeguard against runaway queries degrading the shared database for other users.

Worked example. An analyst wants to build a CTE that filters orders by a dynamic set of conditions and joins to a dynamically-chosen dimension table. Instead of letting the tool string-concatenate f"WITH filtered AS (SELECT * FROM orders WHERE {condition_text})", the tool exposes a constrained UI/API: choose a table from an allowlisted set, choose columns from that table's allowlisted set, choose a comparison operator from a fixed enum, supply a value (parameterized). The tool's own trusted code renders these validated pieces into the final SQL text; the user never authors SQL syntax directly, only selects from constrained options that the renderer assembles safely.

Trade-offs and pitfalls: this approach trades some genuine analyst flexibility (a power user who wants to write truly arbitrary SQL can't, by design) for safety; the practical middle ground many tools use is offering BOTH a constrained builder UI for most users and a separate, explicitly-gated "raw SQL" mode for trusted power users, run under a more restrictive read-only role with tighter resource limits and full query logging, so the flexibility trade-off is made deliberately per-user rather than blanket-applied.