T09 · Insecure Skill Coding Practices
- Location
scripts/data_api/queries.py:565- Finding
SQL Injection in Redshift COPY and UNLOAD Command Builders
- Content
View full analysis
Vulnerability Details
File Location:
scripts/data_api/queries.py:565-568andscripts/data_api/queries.py:607-611
Vulnerability Type: SQL injection through unsafe string interpolation
Risk Level: HighVulnerable Code
python sql = f"COPY {table} FROM '{s3_path}' IAM_ROLE '{role}' FORMAT AS {data_format}" if options: sql += f" {options}" sql += ";"python escaped_query = query.replace("'", "''") sql = f"UNLOAD ('{escaped_query}') TO '{s3_path}' IAM_ROLE '{role}' FORMAT AS {data_format}" if options: sql += f" {options}" sql += ";"Technical Analysis
The command builders interpolate caller-controlled values directly into executable Redshift SQL. The affected values include:
tables3_pathiam_role_arndata_formatoptions
The
optionsparameter is explicitly free-form SQL text. The table identifier is not safely quoted or validated, while string values such as the S3 path and IAM role ARN are inserted between single quotes without escaping. An attacker who can influence tool arguments can therefore introduce quotes, SQL comments, semicolons, additional clauses, or additional statements.The nested query used by
UNLOADhas its single quotes doubled, which protects that specific string boundary. However, this does not protects3_path,role,data_format, oroptions. The same protection is absent from all dynamic fields in theCOPYbuilder.This is particularly significant because these functions are exposed as agent tools in
scripts/data_api/redshift_data_cli.py. Untrusted natural-language content that is converted into tool arguments can reach the SQL builder without an intervening validation layer.Attack Path
- An attacker supplies content that causes the agent to invoke
execute_copyorexecute_unload. - The attacker influences a structured argument such as
table,s3_path,data_format, oroptions.
...[truncated 1372 chars]
- Remediation
View remediation
Remediation Suggestions
- Replace free-form command construction with a strict structured builder.
- Validate
data_formatagainst a fixed allowlist such asCSV,JSON,PARQUET,AVRO, andORC. - Remove the free-form
optionsparameter. Represent each supported option as a typed field and render only predefined syntax. - Validate table and schema names against Redshift identifier rules, then quote each identifier component safely. Do not accept comments, quotes, whitespace, or semicolons in identifier fields.
- Parse S3 destinations with a URI parser and require the
s3scheme, a valid bucket name, and an allowed bucket or prefix. - Validate IAM role values as properly formed IAM role ARNs and, where possible, restrict them to an administrator-configured allowlist.
- Escape all SQL string literals with a centralized routine. Parameterized Data API parameters should be used where supported, although identifier and command-option allowlisting remains necessary.
- Reject multi-statement syntax, SQL comments, unexpected quotes, and control characters in every structured field.
- Run Data API operations under a least-privileged database user and restrict the Redshift IAM role to required S3 buckets and prefixes.
- Add negative tests covering quote termination, semicolons, comments, Unicode separators, malformed ARNs, unsupported formats, and unauthorized S3 destinations.