Summary
PyAthena contains a SQL injection flaw in its parameter formatting logic, where attacker-controlled values can break out of string literals due to incorrect quote escaping.
When applications pass untrusted input into DELETE or CTAS statements through DefaultParameterFormatter.format(), an unauthenticated attacker can inject arbitrary SQL, leading to data exfiltration or destructive operations.
Affected context
This vulnerability arises in applications that use PyAthena to build SQL queries dynamically and rely on DefaultParameterFormatter for parameter substitution, especially with DELETE and CTAS statements.
It becomes relevant when user-supplied or otherwise untrusted data is interpolated into SQL without robust, driver-level parameterization, and the resulting SQL is executed against Athena or Trino backends that do not interpret backslashes as escape characters within string literals.
Exploit preconditions
- Attack surface: Any PyAthena-backed API, job, or interface that constructs SQL with DefaultParameterFormatter and exposes parameters derived from untrusted input.
- Required access: Ability to supply values that are incorporated into SQL queries (e.g., request parameters, job configs, or data fields) without strict validation or sanitization.
- Trigger point: Execution of DELETE or CTAS queries where the formatter routes parameters to
_escape_hiveand treats backslashes as quote escaping. - Why exploitation is possible: Single quotes in attacker input are only backslash-escaped instead of being doubled, and Athena/Trino do not treat backslashes as valid escape characters, allowing the attacker to terminate a string literal and append arbitrary SQL syntax.
Why this happens
The underlying bug is an incorrect assumption about how string literal escaping works in the target SQL dialects.
DefaultParameterFormatter.format() relies on _escape_hive, which backslash-escapes single quotes (e.g., \') instead of using the correct doubling approach ('') required in Athena and Trino string literals.
This mismatch means that user input containing a single quote followed by SQL syntax can break out of the intended string context, a classic CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') pattern, where application-level escaping fails to constrain untrusted data to data-only semantics.
Potential impact
- Attackers may inject arbitrary SQL, including UNION SELECT queries, to read sensitive tables or views.
- They could execute destructive statements (e.g., DELETE, DROP, or TRUNCATE) impacting data availability and integrity.
- They can control CTAS destination names and the content written, enabling data tampering or unauthorized dataset creation.
- They may pivot to broader data discovery by enumerating schemas and tables available to the PyAthena connection.
General mitigation guidance
Effective mitigation focuses on treating all untrusted values as data, never as SQL syntax, and aligning escaping with the actual behavior of the underlying engine.
Recommended defensive patterns include:
- Prefer true, driver-level parameterized queries or prepared statements rather than manually formatting SQL strings.
- Avoid custom escape helpers for SQL literals; when necessary, ensure they follow the exact quoting rules of Athena/Trino (e.g., doubling single quotes).
- Reduce the attack surface by constraining dynamically constructed SQL (e.g., restricting CTAS destinations, disallowing ad-hoc WHERE/ORDER BY from user input).
- Apply least-privilege credentials for PyAthena connections so injected SQL cannot access or modify unnecessary data sets.
- Introduce central input validation that normalizes or rejects dangerous patterns before they reach the query-building layer.
How teams detect issues like this
Detection typically combines code-centric and runtime approaches:
- Use static analysis or linters to flag string-concatenated SQL and custom escaping functions, particularly around DELETE and CTAS statements.
- During code review, look for places where PyAthena queries are built using
.format()-style parameter injection instead of parameterized APIs, and for any reliance on_escape_hive-style helpers. - Enforce security policies that prohibit direct interpolation of untrusted input into SQL and require a standardized query-construction abstraction.
- At runtime, monitor query logs for abnormal UNION SELECT patterns, unexpected CTAS destinations, or destructive statements originating from user-facing flows, optionally using automated tools to correlate suspicious queries with originating code paths.
