How SQL injection happens in Python DuckDB view creation and how to fix it

Answer Summary

This was a SQL injection vulnerability (CWE-89) in a Python FastAPI + DuckDB endpoint: execute_sql() in python/src/idx/api.py used an f-string to build CREATE VIEW {name} AS SELECT * FROM read_parquet('{p_file}'), so any quote character in the parquet path could terminate the string literal and append attacker-controlled SQL. The fix parameterizes the file path using DuckDB's prepared-statement placeholder — con.execute("CREATE VIEW stock_summary AS SELECT * FROM read_parquet(?)", [p_file]) — and stores the five view definitions in a literal dictionary so identifiers are never interpolated. The general rule: never format runtime values into SQL strings; bind them as parameters, and treat identifiers as a fixed allowlist.

Vulnerability at a Glance

Introduction

The execute_sql endpoint in python/src/idx/api.py is the analytical heart of this Python library: it spins up an in-memory DuckDB connection, mounts a set of on-disk Parquet datasets as SQL views (stock_summary, financial_ratios, corporate_actions, broker_summary, index_summary), and then runs a caller-supplied SQL query against them.

The bootstrap loop that mounted those views did something that looks completely innocuous and is extremely common in data-engineering code:

con.execute(f"CREATE VIEW {name} AS SELECT * FROM read_parquet('{p_file}')")

Two runtime values — the view identifier name and the filesystem path p_file — were interpolated straight into SQL source text. The path is wrapped in single quotes, which feels like escaping but is not: a single ' character anywhere inside p_file closes the literal early and everything after it is parsed as SQL. That's a classic CWE-89 injection primitive, and it sits on the very code path that also executes untrusted user SQL.

This post walks through exactly how the flaw worked in this file, the concrete attack it enabled, and the parameterized fix that shipped.

The Vulnerability Explained

The vulnerable code

Here is the block as it existed before the fix, inside async def execute_sql(req: SQLQueryRequest):

import duckdb

con = duckdb.connect(database=":memory:")
for name in [
    "stock_summary",
    "financial_ratios",
    "corporate_actions",
    "broker_summary",
    "index_summary",
]:
    p_file = os.path.join(DATA_DIR, "parquet", f"{name}.parquet")
    if os.path.exists(p_file):
        con.execute(f"CREATE VIEW {name} AS SELECT * FROM read_parquet('{p_file}')")

try:
    res_df = con.execute(sql).fetchdf()

Three things are worth calling out precisely.

1. p_file is a composed runtime value, not a constant. It is built from DATA_DIR — a module-level configuration value that, in most deployments of this pattern, comes from an environment variable, a config file, or a CLI flag. os.path.join performs no validation and no quoting. Whatever characters DATA_DIR contributes end up verbatim inside a SQL string literal.

2. The quoting is hand-rolled. The '{p_file}' construct is manual escaping by wishful thinking. DuckDB, like every SQL dialect, terminates a string literal at the first unescaped '. The Python f-string has no idea it's producing SQL and will happily emit an unbalanced quote.

3. The view identifier is also interpolated. CREATE VIEW {name} puts name in the identifier position, where parameter binding is not even possible. In this snapshot the list was hardcoded, but that is a fragile invariant — the natural next commit for a data API is "discover the parquet files in the directory instead of hardcoding them," at which point filenames become the injection source directly.

The attack

Consider the exploitation path the scanner flagged: an attacker who can drop a file into DATA_DIR/parquet (a shared volume, an upload directory, a sync target, a CI artifact path) or influence the value of DATA_DIR itself.

Suppose DATA_DIR resolves to a path containing an injected fragment, or a future directory-scan variant picks up a file literally named:

users'); DROP VIEW financial_ratios; CREATE VIEW pwn AS SELECT * FROM read_csv('/etc/passwd

The f-string then produces:

CREATE VIEW stock_summary AS SELECT * FROM read_parquet('/data/parquet/users');
DROP VIEW financial_ratios;
CREATE VIEW pwn AS SELECT * FROM read_csv('/etc/passwd.parquet')

One ' and one ) are all it takes to escape the intended statement. From there the attacker has full DuckDB SQL at their disposal, executed by the server process before the user's query even runs:

• Data destruction / denial of analytics — DROP VIEW financial_ratios breaks every downstream query against that dataset. Because views are recreated per request on an in-memory database, an attacker who controls the path controls which datasets exist at all.
• Local file disclosure — DuckDB's read_csv, read_parquet, and read_json_auto functions read arbitrary filesystem paths. A view like CREATE VIEW pwn AS SELECT * FROM read_csv('/etc/passwd') turns the injection into arbitrary file read, and the results are returned through the endpoint's normal res_df response body via SELECT * FROM pwn.
• View shadowing / silent data poisoning — an injected CREATE VIEW stock_summary AS SELECT ... pointing at attacker-supplied Parquet makes every legitimate query return fabricated financial data. In a library whose views are named financial_ratios and broker_summary, that is an integrity problem with real financial consequences for consumers of this API.
• Extension and configuration abuse — DuckDB supports INSTALL/LOAD and SET statements, widening the blast radius well beyond the intended read-only Parquet access.

Because this is a Python library, the impact multiplies: every downstream application that imports idx.api and exposes execute_sql inherits the flaw, and each one may wire DATA_DIR to a different, more attacker-reachable location than the original author assumed.

The Fix

The change is tightly scoped to the view-bootstrap loop in python/src/idx/api.py. Instead of building SQL text at runtime, the statements are now fixed literals and the only runtime value — the file path — is bound as a parameter.

Before

con = duckdb.connect(database=":memory:")
for name in [
    "stock_summary",
    "financial_ratios",
    "corporate_actions",
    "broker_summary",
    "index_summary",
]:
    p_file = os.path.join(DATA_DIR, "parquet", f"{name}.parquet")
    if os.path.exists(p_file):
        con.execute(f"CREATE VIEW {name} AS SELECT * FROM read_parquet('{p_file}')")

After

con = duckdb.connect(database=":memory:")
view_statements = {
    "stock_summary": "CREATE VIEW stock_summary AS SELECT * FROM read_parquet(?)",
    "financial_ratios": "CREATE VIEW financial_ratios AS SELECT * FROM read_parquet(?)",
    "corporate_actions": "CREATE VIEW corporate_actions AS SELECT * FROM read_parquet(?)",
    "broker_summary": "CREATE VIEW broker_summary AS SELECT * FROM read_parquet(?)",
    "index_summary": "CREATE VIEW index_summary AS SELECT * FROM read_parquet(?)",
}
for name, create_view_stmt in view_statements.items():
    p_file = os.path.join(DATA_DIR, "parquet", f"{name}.parquet")
    if os.path.exists(p_file):
        con.execute(create_view_stmt, [p_file])

Why this specific change closes the hole

The path is now data, not code. read_parquet(?) with con.execute(create_view_stmt, [p_file]) uses DuckDB's prepared-statement API. The SQL is parsed once, with the ? placeholder occupying a value slot in the parse tree. The value in [p_file] is bound afterward as a string, so a path containing ', ;, ), or -- is treated as literally part of the filename. There is no character an attacker can supply in DATA_DIR or a filename that changes the statement's structure — the parser has already finished by the time the value arrives.

Identifiers are no longer interpolated at all. Each view name (stock_summary, financial_ratios, …) is now baked into a hardcoded statement string in the view_statements dictionary. Identifiers cannot be parameterized in SQL, so the safe pattern is exactly this: an allowlist mapping where the SQL text is a source-code constant. The dictionary key is used only for building the filesystem path (f"{name}.parquet") and for iteration — never concatenated into SQL.

No f-string reaches execute() on this path. That's the invariant that makes the fix durable. A future refactor that switches from a hardcoded list to directory discovery can no longer reintroduce injection through the view-creation call, because the SQL strings live in a literal dict and the only dynamic input goes through the parameter list.

Behavior is preserved. The same five views are created, from the same paths, with the same os.path.exists guard, and the downstream con.execute(sql).fetchdf() is untouched. This is a pure hardening change with no functional delta.

One remaining hardening opportunity

The parameterized path fixes the injection, but defense in depth on this endpoint is still worth pursuing:

import os

DATA_DIR = os.path.realpath(DATA_DIR)
PARQUET_ROOT = os.path.join(DATA_DIR, "parquet")

p_file = os.path.realpath(os.path.join(PARQUET_ROOT, f"{name}.parquet"))
if not p_file.startswith(PARQUET_ROOT + os.sep):
    continue  # refuse paths that escape the data directory

And because execute_sql runs caller-supplied SQL, the DuckDB connection itself should be constrained — for example by disabling external file access and extension loading after the views are mounted:

con.execute("SET enable_external_access = false")
con.execute("SET disabled_filesystems = 'LocalFileSystem'")

That way even a logic bug elsewhere in the endpoint can't be escalated into arbitrary local file reads through read_csv.

Prevention & Best Practices

Never format runtime values into SQL. In Python this means no f-strings, no %, no .format(), no + on any string that reaches execute(). Treat execute(f"...") as a code smell that requires justification in review, regardless of whether the interpolated value "comes from a trusted list."

Bind values, allowlist identifiers. Table, view, and column names cannot be parameterized. The correct pattern is the one this fix adopted: a hardcoded dictionary of complete SQL statements keyed by a validated name, with ? placeholders for every value.

Remember that filesystem paths are untrusted input. Injection reviews tend to focus on HTTP parameters, but DATA_DIR, environment variables, config values, filenames from os.listdir(), and CI artifact names are all attacker-influenceable in realistic deployments. Any of them reaching a SQL string is a taint flow.

Don't trust "hardcoded list" invariants. The pre-fix loop was arguably not exploitable as written because the names were literals — but the surrounding p_file was not, and a single future commit switching to directory discovery would have made it trivially exploitable. Fix the pattern, not just the current reachability.

Constrain analytical engines that execute user SQL. DuckDB, ClickHouse, and similar engines expose filesystem and extension primitives by design. If your endpoint runs user-supplied SQL, apply SET enable_external_access = false, disable extension installation, run in a read-only or ephemeral database, and enforce query timeouts and row limits.

Automate detection. Semgrep rules such as python.lang.security.audit.formatted-sql-query and python.sqlalchemy.security.sqlalchemy-execute-raw-query catch formatted SQL reaching execute sinks. bandit -r python/ flags B608 (hardcoded SQL expressions). Add these to CI so the pattern cannot come back.

Standards mapping: CWE-89 (SQL Injection), CWE-943 (Improper Neutralization of Special Elements in Data Query Logic), OWASP Top 10 A03:2021 – Injection, and the OWASP SQL Injection Prevention Cheat Sheet.

Key Takeaways

• The wrapper quotes in read_parquet('{p_file}') were not escaping — a single ' in DATA_DIR or a parquet filename terminated the literal and injected arbitrary DuckDB SQL.
• execute_sql() in python/src/idx/api.py now uses con.execute(create_view_stmt, [p_file]) with read_parquet(?), so the path is bound as data and can never alter the statement's parse tree.
• View identifiers like financial_ratios and broker_summary are hardcoded into the view_statements dictionary because SQL identifiers cannot be parameterized — an allowlist of literal statements is the only safe pattern.
• DuckDB injection is not limited to data tampering: read_csv('/etc/passwd') inside an injected CREATE VIEW turns SQL injection into arbitrary local file read, returned through the endpoint's normal DataFrame response.
• Filesystem-derived values (DATA_DIR, os.listdir() results, uploaded filenames) are untrusted input for injection analysis, even when no HTTP parameter is involved.

How Orbis AppSec Detected This

• Source: Filesystem-derived path p_file, composed from the configurable DATA_DIR and a {name}.parquet filename via os.path.join in python/src/idx/api.py
• Sink: con.execute(f"CREATE VIEW {name} AS SELECT * FROM read_parquet('{p_file}')") — DuckDB statement execution at python/src/idx/api.py:265
• Missing control: No parameter binding and no path validation; the value was placed inside a single-quoted SQL string literal with no escaping, and the view identifier was interpolated into a position where binding is impossible
• CWE: CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')
• Fix: The five CREATE VIEW statements are now hardcoded literals in a view_statements dictionary and the parquet path is passed as a bound parameter to read_parquet(?), removing all runtime interpolation from the SQL.

Orbis AppSec automatically detected this vulnerability and opened a pull request with the fix. Try Orbis AppSec on your repositories to find and fix issues like this automatically.

Conclusion

This was a two-line change with an outsized security payoff. The pre-fix loop in python/src/idx/api.py looked safe because the view names were hardcoded and the path was wrapped in quotes — but neither of those provided real protection. The quotes were decorative, the path was configuration-derived, and the endpoint that ran this bootstrap also executed caller-supplied SQL against the resulting views.

By moving the five CREATE VIEW statements into literal constants and binding the parquet path with read_parquet(?), the code now enforces a hard boundary between SQL structure and SQL data. That boundary is the entire defense against injection, and it holds no matter what characters appear in DATA_DIR or a filename — today or after the next refactor.

If your codebase mounts data files into DuckDB, SQLite, or any analytical engine, grep for execute(f" and execute("... " + right now. Every hit is a candidate