Generating SQL From YAML Templates Safely With Bound Values

A YAML file that describes a job is data, which is the whole attraction: analysts edit it, a scheduler reads it, nobody deploys code. The moment a script turns that file into SQL, the file stops being only data. It becomes input to a query builder, and whoever can edit the file can influence what the database runs. For a file only your team writes, that is a bug waiting for a typo. For a file that arrives from a form, an upload or another team's repository, it is an injection point. This post shows the failure on PostgreSQL and the three-part fix. It belongs to the cluster on YAML in data engineering workflows; the file-handling side of the same worry is in handling YAML uploads from users and YAML security pitfalls.

The job file and the naive builder

A job names what to count:

table: orders
column: status
value: paid

and the script builds the query with an f-string:

def naive(j):
    return f"select count(*) from {j['table']} where {j['column']} = '{j['value']}'"

For the honest file it works: the count is 1. Everything about it looks reasonable, which is why it ships.

Attack one: the table name

Now the file is edited, or generated by a tool that trusts its own input:

table: "orders; drop table victim; --"
column: status
value: "x' or '1'='1"

The text the builder produces is:

select count(*) from orders; drop table victim; -- where status = 'x' or '1'='1'

Two statements, with the second one deleting a table and a comment swallowing the rest. The script ran it through conn.execute(...) and no exception was raised. Afterwards:

victim still exists after naive attempt (single-statement execute): False

The table was gone. The label in my script says "single-statement execute" because that is what I expected to protect me, and it did not: psycopg 3 sends a query without parameters through the simple protocol, which accepts several statements in one string. Do not count on a driver's habits as a defense. Some drivers refuse stacked statements and some do not, and a version upgrade can change which.

Attack two: the value

The table name is the dramatic case. The quiet case is the value alone, with a legitimate table and column:

injected value returns 3 rows counted (all 3 expected 0)

The value x' or '1'='1 closes the string literal early, adds a condition that is always true, and the count is every row. Nothing fails and nothing logs. A report built from this query is wrong, and the person reading it cannot tell. On a delete or an update, the same trick matches every row.

The fix, in three parts

Bind values. A bound parameter travels to the server separately from the statement, and the server never parses it as SQL. The placeholder is %s in psycopg:

sql.SQL("select count(*) from {} where {} = %s").format(
    sql.Identifier(t), sql.Identifier(c)), (j["value"],)

The injected value is now just a string that no row equals:

safe good: 1
safe injected value: 0

One for the good job and zero for the hostile one, which is the right answer to the question "how many orders have this exact odd status".

Quote identifiers. A table or column name cannot be bound as a parameter; placeholders stand for values only. Names must be written into the statement text, and the way to do that safely is the driver's own quoting. sql.Identifier wraps a name in double quotes and doubles any embedded quote:

identifier quoting: "weird name" | "a""b"

A name with a space or a quote becomes one valid identifier and cannot end the statement. Hand-rolling this with '"' + name + '"' works until someone forgets the doubling, so use the library function.

Allow-list. Quoting makes a name safe to embed. It does not make it acceptable: a job file could still say table: users and count your customers. Decide which tables and columns a job may touch, in code or in a separate reviewed file:

ALLOWED = {"orders": {"id", "status"}}

def safe(j):
    t, c = j["table"], j["column"]
    if t not in ALLOWED or c not in ALLOWED[t]:
        raise ValueError(f"not allowed: {t}.{c}")
    ...

The hostile table name from attack one never reaches the database:

safe evil table: not allowed: orders; drop table victim; --.status

Check the names before you quote them, and keep the allow-list outside the job file. A list that lives in the file being checked controls nothing.

What else to bind, and what cannot be

Placeholders cover values: strings, numbers, dates, and lists with = any(%s). They do not cover keywords, operators, order by direction, a limit written as an expression or a function name. When a job file needs a choice among those, map the YAML value to a fixed fragment yourself:

DIRECTIONS = {"asc": "asc", "desc": "desc"}
direction = DIRECTIONS[job.get("direction", "asc")]

An unknown key raises KeyError, which is the behavior you want, and nothing from the file is ever concatenated. The same shape covers aggregate names (count, sum, max) and join types. An enumeration in code is the whole defense. If you catch yourself writing a regular expression to "clean" a fragment, stop and write the enumeration.

Templates, not strings

Many teams generate SQL with a template engine (Jinja in dbt, in Airflow, in a hand-rolled runner) and YAML variables as the inputs. Templates are text substitution, and substitution is the injection mechanism. Three rules carry over:

• Render identifiers through a quoting helper, such as dbt's adapter.quote() or {{ ref(...) }}, and not through {{ var }} pasted into the middle of a statement.

• Pass values as bind parameters where the tool supports it, and treat any variable substituted into a string literal as dangerous.

• Keep the variable's source in mind. A value from the repository is reviewed. A value from a form, a webhook payload or an uploaded YAML file is not.

The dbt case is covered in writing dbt model properties in YAML; the lesson there is that dbt's var() is convenient and does no escaping.

Test the defense

Put the hostile file into the test suite. The two files above are ten lines of YAML, and a test that runs them against a scratch schema and asserts that victim still exists and the injected value counts zero will fail loudly if someone later "simplifies" the builder back to an f-string (testing YAML driven jobs). Run it with a database role that has no drop privilege on anything but its scratch schema; a defense in depth that costs one grant statement turns the worst case from lost data into a permission error.

When generating SQL from YAML cannot be made safe

The file is a general query language in disguise. If the YAML lets the author write conditions, joins and expressions, you have rebuilt SQL with worse tooling and the same risk. Either give analysts SQL files that go through review, or restrict the YAML to a small closed vocabulary.

Authors are not trusted and the database is powerful. Allow-lists, binding and a least-privilege role reduce the risk to a smaller set of tables. They do not make an untrusted author safe against a query that is merely expensive. Add a statement timeout.

The allow-list cannot be kept current. If every new table needs a code change, people will route around it. Generate the list from a reviewed file, and make adding a table a small pull request.

You are escaping by hand. A regex that removes quotes and semicolons is not a defense. Anything that is not binding or driver quoting is a guess.

The mechanism is described in the psycopg documentation on SQL composition and the class of flaw in the OWASP SQL injection prevention cheat sheet. Check the YAML itself with the YAML formatter.