How to Use Python t-strings to Make SQL and HTML Injection-Safe by Default

What you will build, and why t-strings matter

By the end of this tutorial you will have two small, tested Python modules that make the safe way of building SQL and HTML the easy way for everyone on your team. tsql.py (79 lines) turns a t-string into SQL text with placeholders plus a separate tuple of values, and it knows how to handle column names, optional filters, lists and LIKE patterns. thtml.py (120 lines) turns an HTML t-string into text that is escaped for the exact spot each value lands in, and it refuses the spots it cannot protect. You will also write a 30-line check that fails the build when code outside tsql.py calls execute() directly, 85 tests, and a script that weakens tsql.py six different ways to check that the tests notice.

A t-string is a Python 3.14 feature that looks like an f-string with a t in front. The difference is the whole point of this tutorial. An f-string glues your values into the text immediately and hands you a finished string, so by the time any function sees it, the user’s input is already part of the code. A t-string hands you the pieces instead: the fixed text in one place and the values in another. The proposal that introduced them, PEP 750, names this exact motivation: a user executing a SQL query with sqlite3 “may be tempted to use an f-string to embed values into their SQL expression, which could lead to a SQL injection attack.” The same document points at HTML, where building a page from unescaped input leads to cross-site scripting.

The catch, and the reason this is a tutorial and not a one-line tip, is that a t-string does nothing safe by itself. It only gives your function the pieces. The safety lives in what that function does with them, and in whether everyone is steered into using it. Along the way you will reproduce these results:

• An f-string search leaks two unreleased books to a hostile search term. The same search written as a t-string leaks none (Steps 3 and 4).
• The type checker mypy flags an f-string passed where a t-string is required (Step 5).
• A column name passed as an ordinary placeholder does not fail. It silently sorts nothing (Step 7).
• A LIKE search for 100% matches two books, because % is a wildcard that placeholders do not neutralize (Step 9).
• Escaping that is correct for text still lets a value break out of an attribute that is not quoted, and in Microsoft Edge 155 the injected handler runs (Step 12).
• Weakening tsql.py in six different ways is noticed by the tests each time (Step 11).

This is the follow-up to How to Prevent SQL Injection in Python With Parameterized Queries, which teaches placeholders from scratch and breaks a login form with real attacks, and to How to Stop XSS Attacks in a Python Web App With a Content Security Policy, which covers the browser side. Read them first if the attacks are new to you. Here the question is different: how do you arrange your code so that the safe call is the only call a teammate can make by accident?

Key terms in plain language

SQL injection. An attack in which text typed by a user changes the meaning of a SQL statement because the program pasted that text into the statement as if it were code.

Placeholder, or parameterized query. A ? in the SQL text with the real value sent separately. The Python documentation for the sqlite3 module says: “Always use placeholders instead of string formatting to bind Python values to SQL statements, to avoid SQL injection attacks.” OWASP, the web security community whose cheat sheets this tutorial cites, explains why it works: “parameterized queries force the developer to define all SQL code first and pass in each parameter to the query later.”

f-string and t-string. Both are string literals with {} holes. An f-string (prefix f) evaluates to a finished str. A t-string (prefix t) evaluates to a Template object that keeps the fixed text and the values apart. The Python documentation puts it this way: “Template strings are a mechanism for custom string processing.”

Template and Interpolation. The two types in the standard library module string.templatelib. A Template holds the fixed text pieces (strings) and one Interpolation for each {} hole. An Interpolation carries four fields: the evaluated value, the source text of the expression, an optional conversion such as !r, and a format_spec such as .2f (the text after the colon).

Identifier. A table or column name. Databases do not accept a placeholder where a name belongs, so names need a separate, stricter path.

Allowlist. A list of the values you accept, with everything else refused. It is the opposite of a blocklist, which tries to name the bad values and misses some.

Context (HTML). The place in a page where a value lands: the text between tags, a quoted attribute, an unquoted attribute, a URL attribute, and so on. The correct escaping depends on the context, which is why HTML is harder than SQL.

XSS (cross-site scripting). An attack in which a value typed by one user ends up running as script in another user’s browser.

Prerequisites

• Python 3.14 or newer. Everything here ran on Python 3.15.0 on Windows 11. A t-string is a syntax error on older versions (Python 3.13.14 reports SyntaxError: invalid syntax), so check first.
• The sqlite3 module that ships with Python (SQLite 3.53.4 in this build) and pytest==9.1.1. For Step 5, mypy==2.4.0 and ruff==0.17.1. For Step 13, psycopg[binary]==3.3.6.
• For the browser checks in Step 12, a Chromium-based browser. The lab uses Microsoft Edge 155 in headless mode; you can skip the browser and read the results printed in this article.
• Basic Python (functions, loops, classes) and a first feel for SQL SELECT ... WHERE. You do not need to know t-strings, security or HTML parsing.
• About ninety minutes. Each listing carries its file name in the first line. Save every listing under that name in one folder. The numbers inside file names such as step15_psycopg.py are the order the lab ran in and do not always match the step headings.

Step 1: Create the project and a practice database

Make the folder and install the test runner

Create an empty folder, check your Python version, and install pytest. The version check matters: on Python 3.13 or older the very first t-string in this tutorial is a syntax error.

mkdir tstring-lab
cd tstring-lab
python --version
python -m pip install pytest==9.1.1

The version line must read 3.14 or higher. Now confirm that the standard library has the new module and see which SQLite you are running:

python -c "import sys, sqlite3; from string.templatelib import Template; print('Python', sys.version.split()[0]); print('SQLite', sqlite3.sqlite_version); print(Template)"

Python 3.15.0
SQLite 3.53.4
<class 'string.templatelib.Template'>

Create the practice data

Save this as data.py. It builds an in-memory SQLite database with one table of books. Two of the rows are marked hidden = 1: they are unreleased manuscripts that no search should ever show. They are the treasure the attacks in this tutorial go after, which makes a leak easy to count.

# data.py - a tiny book catalogue shared by every step
import sqlite3

ROWS = [
    # title, author, price in cents, hidden (1 = unreleased, must never be shown)
    ("Learning Python the Slow Way", "Ada Okafor", 3495, 0),
    ("100% Pure Python", "Sam Reyes", 2995, 0),
    ("100 Python Tips", "Sam Reyes", 1995, 0),
    ("SQL for Humans", "Ada Okafor", 2750, 0),
    ("Cooking With Cron", "Lee Park", 1500, 0),
    ("The Quiet Compiler", "Lee Park", 4200, 0),
    ("Zero-Day Gardening", "Mina Sato", 3100, 0),
    ("Unreleased: Memoirs of a Sysadmin", "Mina Sato", 0, 1),
    ("Unreleased: Pricing Spreadsheet 2027", "Sam Reyes", 0, 1),
]


def make_db():
    conn = sqlite3.connect(":memory:")
    conn.execute(
        "CREATE TABLE books ("
        "id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, "
        "price_cents INTEGER NOT NULL, hidden INTEGER NOT NULL DEFAULT 0)"
    )
    conn.executemany("INSERT INTO books (title, author, price_cents, hidden) VALUES (?, ?, ?, ?)", ROWS)
    conn.commit()
    return conn

Check that it works. The in-memory database exists only while the command runs.

python -c "from data import make_db; print(make_db().execute('SELECT count(*) FROM books').fetchone())"

(9,)

Nine rows: seven public and two hidden. If you see a different number, re-check that ROWS matches the listing.

Step 2: Take a t-string apart

Before you write any security code, look at what a t-string actually gives you. Save this as step02_anatomy.py and run it with python step02_anatomy.py.

# step02_anatomy.py - take a t-string apart
from string.templatelib import convert

author = "O'Brien"
limit = 1500
t = t"SELECT title FROM books WHERE author = {author} AND price_cents <= {limit}"

print("type          :", type(t).__name__)
print("strings       :", t.strings)
print("values        :", t.values)
print("interpolations:")
for item in t.interpolations:
    print("   ", item)
print("iteration     :", list(t))
print("str(t) == repr(t):", str(t) == repr(t))

hostile = "x' OR hidden = 1 --"
as_f = f"WHERE author = '{hostile}'"
as_t = t"WHERE author = '{hostile}'"
print("f-string      :", as_f)
print("t-string      :", as_t.strings, as_t.values)

price = 1234.5
item = t"{price!r:>12}".interpolations[0]
print("conversion    :", item.conversion, "| format_spec:", repr(item.format_spec), "| value:", item.value)
print("f-string      :", repr(f"{price!r:>12}"))
print("convert()     :", repr(convert(price, "r")))

calls = []


def fetch():
    calls.append("fetch() ran")
    return 42


later = t"{fetch()}"
print("calls so far  :", calls)

try:
    t + " extra text"
except TypeError as exc:
    print("Template + str:", type(exc).__name__, "-", exc)
print("Template + Template:", list(t"a " + t"{author}"))

type          : Template
strings       : ('SELECT title FROM books WHERE author = ', ' AND price_cents <= ', '')
values        : ("O'Brien", 1500)
interpolations:
    Interpolation("O'Brien", 'author', None, '')
    Interpolation(1500, 'limit', None, '')
iteration     : ['SELECT title FROM books WHERE author = ', Interpolation("O'Brien", 'author', None, ''), ' AND price_cents <= ', Interpolation(1500, 'limit', None, '')]
str(t) == repr(t): True
f-string      : WHERE author = 'x' OR hidden = 1 --'
t-string      : ("WHERE author = '", "'") ("x' OR hidden = 1 --",)
conversion    : r | format_spec: '>12' | value: 1234.5
f-string      : '      1234.5'
convert()     : '1234.5'
calls so far  : ['fetch() ran']
Template + str: TypeError - can only concatenate string.templatelib.Template (not "str") to string.templatelib.Template
Template + Template: ['a ', Interpolation("O'Brien", 'author', None, '')]

What each line of output teaches

Strings and values are separate. t.strings is the fixed text and t.values is the evaluated data. The strings tuple has a trailing empty string: it always has one more element than the values, because every hole sits between two pieces of text.

Each hole is an Interpolation. The line Interpolation("O'Brien", 'author', None, '') shows the four fields in order: value, expression text, conversion (None), format spec (empty). Iterating over the template with list(t) yields the text pieces and the interpolations in order. The documentation warns: “Empty strings are not included in the iteration”, which is why the trailing empty string is missing from that list although it is in t.strings.

Printing a template does not render it. str(t) == repr(t) is True, so str(t) gives the developer-oriented display with the values inside. That matters for logging: if you log the template itself, the values go into your logs. Later you will log the rendered query text instead, which has placeholders where the values were.

The f-string versus t-string lines are the heart of it. With the hostile value x' OR hidden = 1 --, the f-string produced one string in which the quote character is now part of the text. The t-string produced two pieces of fixed text and a separate value tuple. Nothing has been combined, so nothing has been confused.

Conversions and format specs are recorded, not applied. In an f-string, {price!r:>12} produced ' 1234.5'. In a t-string the interpolation just notes conversion r and format spec >12. The documentation says: “Unlike f-strings, where conversions are applied automatically, the expected behavior with t-strings is that code that processes the Template will decide how to interpret and whether to apply the conversion.” It adds that format_spec values “can be arbitrary strings, including those that do not conform to the format() protocol.” The helper convert() applies a conversion for you if you want f-string behavior. Step 6 turns this freedom into a safety rule.

Expressions run immediately. The calls so far line shows that fetch() ran when the template was created, not when something later used it. A t-string defers the combining of text and values, not the evaluation of the expressions.

Templates join with templates, not strings. The documentation explains why adding a plain string fails: “Concatenating a Template and a str is not supported.” The reason it gives is that it is “unclear whether the string should be treated as a static string or an interpolation.” That ambiguity is exactly the question that matters for security, and the library refuses to guess.

Step 3: See what an f-string does to a search

Now the problem. Save this as step03_fstring_attack.py. It is the search function most people write first, with an f-string, and a hostile search term that is a classic: it closes the quoted pattern, adds an OR condition, and comments out the rest.

# step03_fstring_attack.py - the f-string version of the search, and what a hostile search term does to it
from data import make_db


def search_books(conn, text):
    query = f"SELECT title FROM books WHERE hidden = 0 AND title LIKE '%{text}%'"
    return query, [title for (title,) in conn.execute(query)]


conn = make_db()
for label, text in (("benign ", "Python"), ("hostile", "zzz%' OR hidden = 1 --")):
    query, titles = search_books(conn, text)
    print(label, "query :", query)
    print(label, "rows  :", titles)

benign  query : SELECT title FROM books WHERE hidden = 0 AND title LIKE '%Python%'
benign  rows  : ['Learning Python the Slow Way', '100% Pure Python', '100 Python Tips']
hostile query : SELECT title FROM books WHERE hidden = 0 AND title LIKE '%zzz%' OR hidden = 1 --%'
hostile rows  : ['Unreleased: Memoirs of a Sysadmin', 'Unreleased: Pricing Spreadsheet 2027']

Read the hostile query line carefully: ... title LIKE '%zzz%' OR hidden = 1 --%'. The quote after zzz% ended the pattern, OR hidden = 1 became a second condition, and -- started a SQL comment that swallowed the leftover %'. In SQL, AND binds tighter than OR, so the database reads the whole thing as (hidden = 0 AND title LIKE '%zzz%') OR hidden = 1, and the two unreleased books come back. The benign search shows the code working as the author intended, which is why this kind of bug survives review.

Step 4: Write the first version of sql()

The plan: a function that takes a t-string and returns the query text with a ? where each value was, plus the values as a tuple. Save this as tsql.py. You will grow it over the next steps; this is version 1.

# tsql.py - turn a t-string into SQL text plus parameters
from dataclasses import dataclass
from string.templatelib import Template


@dataclass(frozen=True)
class Query:
    text: str
    params: tuple


def sql(template: Template) -> Query:
    if not isinstance(template, Template):
        raise TypeError(f"sql() needs a t-string, got {type(template).__name__}")
    parts = []
    params = []
    for item in template:
        if isinstance(item, str):
            parts.append(item)
        else:
            parts.append("?")
            params.append(item.value)
    return Query("".join(parts), tuple(params))

How it works

Query is a small frozen dataclass that holds the two things a database driver wants: the text and the parameters. sql() first refuses anything that is not a Template, which is how a stray f-string gets caught at run time. Then it loops over the template. A piece of fixed text is copied as it is. An Interpolation becomes a ? in the text and its value goes into the parameter list. The values never touch the text, so they cannot change its meaning.

Now use it. Save step04_first_template.py and run it.

# step04_first_template.py - the same search, written as a t-string
from data import make_db
from tsql import sql


def search_books(conn, text):
    query = sql(t"SELECT title FROM books WHERE hidden = 0 AND title LIKE {'%' + text + '%'}")
    rows = conn.execute(query.text, query.params).fetchall()
    return query, [title for (title,) in rows]


conn = make_db()
for label, text in (("benign ", "Python"), ("hostile", "zzz%' OR hidden = 1 --")):
    query, titles = search_books(conn, text)
    print(label, "query :", query.text)
    print(label, "params:", query.params)
    print(label, "rows  :", titles)

hostile = "zzz%' OR hidden = 1 --"
try:
    sql(f"SELECT title FROM books WHERE title LIKE '%{hostile}%'")
except TypeError as exc:
    print("f-string slip:", exc)

try:
    conn.execute(t"SELECT title FROM books WHERE title LIKE {hostile}")
except Exception as exc:
    print("sqlite3 given a t-string:", type(exc).__name__, "-", exc)

benign  query : SELECT title FROM books WHERE hidden = 0 AND title LIKE ?
benign  params: ('%Python%',)
benign  rows  : ['Learning Python the Slow Way', '100% Pure Python', '100 Python Tips']
hostile query : SELECT title FROM books WHERE hidden = 0 AND title LIKE ?
hostile params: ("%zzz%' OR hidden = 1 --%",)
hostile rows  : []
f-string slip: sql() needs a t-string, got str
sqlite3 given a t-string: TypeError - execute() argument 1 must be str, not string.templatelib.Template

What to notice

• Both queries have the same text, ... title LIKE ?. Only the parameter differs. The hostile term sits entirely inside one parameter, "%zzz%' OR hidden = 1 --%", and the search returns no rows. It is just a title that does not exist.
• The percent signs are added inside the braces: {'%' + text + '%'}. Python evaluates that expression before sql() sees it, so the finished pattern is one value. Step 9 comes back to this line, because it hides a trap.
• The f-string slip is stopped: sql() needs a t-string, got str. This is the guard at the top of the function doing its job.
• The last line shows that sqlite3 in Python 3.15.0 does not understand t-strings: execute() argument 1 must be str, not string.templatelib.Template. That is why we convert the template ourselves. A database driver that does accept templates is shown in Step 13.

Step 5: Let the type checker and the linter catch the slip

A run-time error is good, but an error before the code ships is better. Install the two tools and save slip.py, which holds one correct call and one slip:

python -m pip install mypy==2.4.0 ruff==0.17.1
python -m mypy --version
python -m ruff --version

mypy 2.4.0 (compiled: yes)
ruff 0.17.1

# slip.py - one correct call and one slip, for the type checker
from tsql import sql


def good(author: str):
    return sql(t"SELECT title FROM books WHERE author = {author}")


def slip(author: str):
    return sql(f"SELECT title FROM books WHERE author = '{author}'")

python -m mypy --python-version 3.15 --no-error-summary slip.py

slip.py:10: error: Argument 1 to "sql" has incompatible type "str"; expected "Template"  [arg-type]

The annotation template: Template on sql() is all mypy needed. It reports the f-string call on line 10 as an incompatible argument and says nothing about the correct call on line 6. The command exits with status 1, so a CI job that runs it fails the build.

The linter Ruff has a rule for the old mistake. Save these two small files, which differ only in how they build the query:

# bad_fstring.py - the mistake the linter and the gate should catch
def find(conn, text):
    return conn.execute(f"SELECT title FROM books WHERE title LIKE '%{text}%'").fetchall()

# good_template.py - the same function written the way the gate wants it
from tsql import run


def find(conn, text):
    return run(conn, t"SELECT title FROM books WHERE title LIKE {text:like}")

python -m ruff check --isolated --target-version py314 --select S608 --output-format concise bad_fstring.py
python -m ruff check --isolated --target-version py314 --select S608 --output-format concise good_template.py

bad_fstring.py:3:25: S608 Possible SQL injection vector through string-based query construction
Found 1 error.

All checks passed!

The flags keep the run self-contained: --isolated ignores any configuration file on your machine, --target-version py314 tells Ruff to read the code as Python 3.14, the first version with t-strings, and --select S608 turns on just the one rule. Ruff’s documentation describes S608 as one that “Checks for strings that resemble SQL statements involved in some form string building operation.” It flagged the f-string and passed the template version.

Treat both tools as early warnings, not as proof. mypy only helps where the call site is type-checked, and S608 looks for a pattern, so a differently shaped mistake can slip past it. The run-time guard in sql() and the gate you will write in Step 10 are what hold when the early warnings miss.

Step 6: Refuse format specs and conversions you cannot explain

Step 2 showed that a t-string records a conversion such as !r and a format spec such as >12 without applying them. Version 1 of sql() ignores both, which sets a quiet trap: someone who writes {price:.2f} expects formatting, someone who mistypes a format spec gets no error, and the query still runs. Save this as step05_surprises.py and run it against your current version 1:

# step05_surprises.py - format specs and conversions that an f-string would apply and a t-string does not
from tsql import sql

attempts = {
    "format spec": lambda price: sql(t"SELECT title FROM books WHERE price_cents <= {price:.2f}"),
    "conversion": lambda price: sql(t"SELECT title FROM books WHERE price_cents <= {price!r}"),
    "typo in a spec": lambda price: sql(t"SELECT title FROM books WHERE price_cents <= {price:idnet}"),
}
for label, build in attempts.items():
    try:
        query = build(1500)
        print(f"{label:14} -> accepted: {query.text!r} {query.params}")
    except Exception as exc:
        print(f"{label:14} -> {type(exc).__name__}: {exc}")

format spec    -> accepted: 'SELECT title FROM books WHERE price_cents <= ?' (1500,)
conversion     -> accepted: 'SELECT title FROM books WHERE price_cents <= ?' (1500,)
typo in a spec -> accepted: 'SELECT title FROM books WHERE price_cents <= ?' (1500,)

All three templates were accepted and produced the same query. The typo idnet is the dangerous one, because in the next step ident will become a meaningful format spec, and a misspelling must not silently fall back to being treated as a value. A good rule for a processing function is to refuse what it does not understand.

Open tsql.py. Add the new exception class above sql and replace the whole sql function with this version 2:

# tsql.py (add UnsafeTemplate above sql, replace sql)
class UnsafeTemplate(ValueError):
    """The template asks for something this module refuses to do."""


def sql(template: Template) -> Query:
    if not isinstance(template, Template):
        raise TypeError(f"sql() needs a t-string, got {type(template).__name__}")
    parts = []
    params = []
    for item in template:
        if isinstance(item, str):
            parts.append(item)
            continue
        if item.conversion is not None:
            raise UnsafeTemplate(f"{item.expression}: conversion !{item.conversion} is not supported")
        if item.format_spec:
            raise UnsafeTemplate(f"{item.expression}: format spec {item.format_spec!r} is not supported")
        parts.append("?")
        params.append(item.value)
    return Query("".join(parts), tuple(params))

Run step05_surprises.py again:

format spec    -> UnsafeTemplate: price: format spec '.2f' is not supported
conversion     -> UnsafeTemplate: price: conversion !r is not supported
typo in a spec -> UnsafeTemplate: price: format spec 'idnet' is not supported

Each surprise is now a clear UnsafeTemplate error that names the expression and the offending conversion or spec. UnsafeTemplate inherits from ValueError, so callers can catch it separately from a database error.

Step 7: Pass column names safely

See why a placeholder cannot be a name

Sorting is where beginners meet the next wall: the sort column comes from a request, and there is a ? right there. Save step06_order_by_param.py and run it (it works with your version 2):

# step06_order_by_param.py - what happens when a column name is passed as a parameter
from data import make_db
from tsql import sql

conn = make_db()
by_price = [title for (title, _) in conn.execute("SELECT title, price_cents FROM books WHERE hidden = 0 ORDER BY price_cents")]
by_second_column = [title for (title, _) in conn.execute("SELECT title, price_cents FROM books WHERE hidden = 0 ORDER BY 2")]


def titles_ordered_by(value):
    query = sql(t"SELECT title, price_cents FROM books WHERE hidden = 0 ORDER BY {value}")
    rows = conn.execute(query.text, query.params).fetchall()
    return query, [title for (title, _) in rows]


query, got = titles_ordered_by("price_cents")
print("query                   :", query.text, query.params)
print("first three returned    :", got[:3])
print("first three by price    :", by_price[:3])
print("sorted by price?        :", got == by_price)
print("same as insertion order?:", got == [title for (title,) in conn.execute("SELECT title FROM books WHERE hidden = 0 ORDER BY id")])
query, got = titles_ordered_by(2)
print("bound integer 2 sorts like column 2?:", got == by_second_column)

query                   : SELECT title, price_cents FROM books WHERE hidden = 0 ORDER BY ? ('price_cents',)
first three returned    : ['Learning Python the Slow Way', '100% Pure Python', '100 Python Tips']
first three by price    : ['Cooking With Cron', '100 Python Tips', 'SQL for Humans']
sorted by price?        : False
same as insertion order?: True
bound integer 2 sorts like column 2?: False

Nothing raised an error, and nothing was sorted. The rows came back in insertion order, and even the bound integer 2 did not behave like ORDER BY 2. A bound value reaches the database as a constant, never as a name. SQLite’s documentation spells out what an expression in ORDER BY does: “if the ORDER BY expression is any other expression, it is evaluated and the returned value used to order the output rows.” A constant orders every row the same way, so the sort is a no-op. This is the safe failure mode, since a value cannot become code, but it is a silent wrong answer.

Add an identifier path with an allowlist

The right tool is different from a placeholder. OWASP’s SQL injection cheat sheet says: “developers should map the parameter values to the legal/expected table or column names to make sure unvalidated user input doesn’t end up in the query.” The design here is a format spec, {column:ident}, with three checks: the value must be a string, it must match a strict pattern of letters, digits and underscores, and it must appear in an allowlist that the caller passes to that specific call. The default allowlist is empty, so forgetting to pass one refuses everything instead of allowing everything.

Update tsql.py again: add import re at the top, add IDENT and run above sql, and replace sql with version 3:

# tsql.py (add import re at the top, add IDENT and run above sql, replace sql)
import re


IDENT = re.compile(r"[A-Za-z_][A-Za-z0-9_]*")


def run(conn, template: Template, idents: frozenset = frozenset()) -> list:
    query = sql(template, idents)
    return conn.execute(query.text, query.params).fetchall()


def sql(template: Template, idents: frozenset = frozenset()) -> Query:
    if not isinstance(template, Template):
        raise TypeError(f"sql() needs a t-string, got {type(template).__name__}")
    parts = []
    params = []
    for item in template:
        if isinstance(item, str):
            parts.append(item)
            continue
        if item.conversion is not None:
            raise UnsafeTemplate(f"{item.expression}: conversion !{item.conversion} is not supported")
        value = item.value
        if item.format_spec == "ident":
            if not isinstance(value, str) or not IDENT.fullmatch(value) or value not in idents:
                raise UnsafeTemplate(f"{value!r} is not an allowed identifier")
            parts.append('"' + value + '"')
            continue
        if item.format_spec:
            raise UnsafeTemplate(f"{item.expression}: format spec {item.format_spec!r} is not supported")
        parts.append("?")
        params.append(value)
    return Query("".join(parts), tuple(params))

Three details are worth knowing. The identifier is wrapped in double quotes in the output, the standard SQL way to quote a name. The check uses IDENT.fullmatch and not re.match with a $ at the end, because Python’s documentation says that $ “Matches the end of the string or just before the newline at the end of the string”, so a name with a trailing newline could slip through. The new run() helper executes a template in one call; Step 10 will make it the only door to the database.

Save step07_ident.py and run it:

# step07_ident.py - identifiers need their own, stricter path
import re

from data import make_db
from tsql import IDENT, UnsafeTemplate, run, sql

conn = make_db()
print("re.match with $ accepts 'title' + newline:", bool(re.match(r"[A-Za-z_][A-Za-z0-9_]*$", "title\n")))
print("IDENT.fullmatch accepts it               :", bool(IDENT.fullmatch("title\n")))
ALLOWED = frozenset({"title", "author", "price_cents"})


def titles_ordered_by(column):
    rows = run(conn, t"SELECT title FROM books WHERE hidden = 0 ORDER BY {column:ident}", ALLOWED)
    return [title for (title,) in rows]


print("price_cents:", titles_ordered_by("price_cents")[:3])
query = sql(t"SELECT title FROM books ORDER BY {'title':ident}", ALLOWED)
print("rendered   :", query.text)
for column in ("hidden", "price_cents; DROP TABLE books", '"title"', "title\n", 42):
    try:
        titles_ordered_by(column)
        print(repr(column), "-> accepted")
    except UnsafeTemplate as exc:
        print(repr(column), "->", exc)
try:
    sql(t"SELECT title FROM books ORDER BY {'title':ident}")
except UnsafeTemplate as exc:
    print("no allowlist given ->", exc)

re.match with $ accepts 'title' + newline: True
IDENT.fullmatch accepts it               : False
price_cents: ['Cooking With Cron', '100 Python Tips', 'SQL for Humans']
rendered   : SELECT title FROM books ORDER BY "title"
'hidden' -> 'hidden' is not an allowed identifier
'price_cents; DROP TABLE books' -> 'price_cents; DROP TABLE books' is not an allowed identifier
'"title"' -> '"title"' is not an allowed identifier
'title\n' -> 'title\n' is not an allowed identifier
42 -> 42 is not an allowed identifier
no allowlist given -> 'title' is not an allowed identifier

The first two lines show the newline point: the $ pattern accepted 'title\n' and fullmatch did not. Sorting by price_cents now works (the cheapest book comes first), the rendered text has ORDER BY "title", and every attempt outside the allowlist is refused: a column that exists but was not offered (hidden), an injection attempt, a pre-quoted name, a trailing newline, a number, and a call that supplied no allowlist.

Step 8: Build queries from optional pieces and lists

Real search screens have optional filters: text, authors, a maximum price, a sort order. The classic way to build that is string concatenation, which is exactly the pattern you are trying to escape. With templates, each filter is its own small template, and templates can be nested inside other templates. Psycopg, which you will meet in Step 13, supports the same pattern. Update tsql.py to version 4: add MAX_LIST_ITEMS and join above sql, and replace sql:

# tsql.py (add MAX_LIST_ITEMS and join above sql, replace sql)
MAX_LIST_ITEMS = 1000


def join(separator: Template, pieces: list) -> Template:
    result = Template()
    for number, piece in enumerate(pieces):
        if number:
            result += separator
        result += piece
    return result


def sql(template: Template, idents: frozenset = frozenset()) -> Query:
    if not isinstance(template, Template):
        raise TypeError(f"sql() needs a t-string, got {type(template).__name__}")
    parts = []
    params = []
    for item in template:
        if isinstance(item, str):
            parts.append(item)
            continue
        if item.conversion is not None:
            raise UnsafeTemplate(f"{item.expression}: conversion !{item.conversion} is not supported")
        value = item.value
        if item.format_spec == "ident":
            if not isinstance(value, str) or not IDENT.fullmatch(value) or value not in idents:
                raise UnsafeTemplate(f"{value!r} is not an allowed identifier")
            parts.append('"' + value + '"')
            continue
        if item.format_spec:
            raise UnsafeTemplate(f"{item.expression}: format spec {item.format_spec!r} is not supported")
        if isinstance(value, Template):
            inner = sql(value, idents)
            parts.append(inner.text)
            params.extend(inner.params)
        elif isinstance(value, (list, tuple)):
            if not value:
                raise UnsafeTemplate(f"{item.expression}: an empty list cannot be expanded")
            if len(value) > MAX_LIST_ITEMS:
                raise UnsafeTemplate(f"{item.expression}: {len(value)} items is more than {MAX_LIST_ITEMS}")
            parts.append(", ".join(["?"] * len(value)))
            params.extend(value)
        else:
            parts.append("?")
            params.append(value)
    return Query("".join(parts), tuple(params))

The new branches of sql() do two jobs. When a value is itself a Template, it is rendered by a recursive call and its text and parameters are spliced in, in order. When a value is a list or tuple, it becomes a comma-separated run of placeholders, one per item, which is what an IN (...) test needs. An empty list is refused, because what an empty list should mean is a decision for the caller, and a list longer than 1,000 items is refused too. join() glues a list of templates together with a separator template.

Save step08_compose.py and run it:

# step08_compose.py - build a search from optional filters without ever writing SQL text by hand
import random
import sqlite3
from string.templatelib import Template

from data import make_db
from tsql import MAX_LIST_ITEMS, UnsafeTemplate, join, run, sql

SORTS = {"title": t"title ASC", "cheap": t"price_cents ASC", "dear": t"price_cents DESC"}


def build_search(text=None, authors=None, max_price=None, sort="title") -> Template:
    filters = [t"hidden = 0"]
    if text is not None:
        filters.append(t"title LIKE {'%' + text + '%'}")
    if authors is not None:
        filters.append(t"author IN ({authors})")
    if max_price is not None:
        filters.append(t"price_cents <= {max_price}")
    order = SORTS.get(sort)
    if order is None:
        raise ValueError(f"unknown sort {sort!r}; choose one of {sorted(SORTS)}")
    return t"SELECT title, price_cents FROM books WHERE {join(t' AND ', filters)} ORDER BY {order}"


conn = make_db()
examples = (
    {},
    {"text": "Python", "max_price": 3000},
    {"authors": ["Ada Okafor", "Sam Reyes"], "sort": "dear"},
    {"text": "o", "authors": ["Lee Park"], "max_price": 2000, "sort": "cheap"},
)
for kwargs in examples:
    query = sql(build_search(**kwargs))
    print(kwargs)
    print("   ", query.text)
    print("   ", query.params)
    print("    rows:", len(run(conn, build_search(**kwargs))))

for label, kwargs in (("unknown sort", {"sort": "price; DROP TABLE books"}), ("empty list", {"authors": []})):
    try:
        sql(build_search(**kwargs))
    except (ValueError, UnsafeTemplate) as exc:
        print(f"{label:12} -> {type(exc).__name__}: {exc}")

for operator in ("IN", "NOT IN"):
    count = conn.execute(f"SELECT count(*) FROM books WHERE author {operator} ()").fetchone()
    print(f"SQLite itself runs 'author {operator} ()' and counts", count[0], "rows")

fragment = "hidden = 0"
query = sql(t"SELECT title FROM books WHERE {fragment}")
rows = conn.execute(query.text, query.params).fetchall()
print("a str is data:", query.text, query.params, "->", len(rows), "rows")

limit = conn.getlimit(sqlite3.SQLITE_LIMIT_VARIABLE_NUMBER)
many = list(range(limit + 10))
try:
    conn.execute("SELECT 1 WHERE 1 IN (" + ",".join("?" * len(many)) + ")", many)
except sqlite3.OperationalError as exc:
    print(f"SQLite variable limit {limit}, {len(many)} values ->", exc)
try:
    sql(t"SELECT 1 WHERE 1 IN ({many})")
except UnsafeTemplate as exc:
    print(f"our cap of {MAX_LIST_ITEMS}      ->", exc)

rng = random.Random(11)
authors_pool = ["Ada Okafor", "Sam Reyes", "Lee Park", "Mina Sato"]
for _ in range(200):
    kwargs = {"sort": rng.choice(sorted(SORTS))}
    if rng.random() < 0.5:
        kwargs["text"] = rng.choice(["a", "Python", "x%", "'"])
    if rng.random() < 0.5:
        kwargs["authors"] = rng.sample(authors_pool, rng.randint(1, 4))
    if rng.random() < 0.5:
        kwargs["max_price"] = rng.randint(0, 5000)
    query = sql(build_search(**kwargs))
    assert query.text.count("?") == len(query.params)
print("200 random combinations: placeholders always equal parameters")

{}
    SELECT title, price_cents FROM books WHERE hidden = 0 ORDER BY title ASC
    ()
    rows: 7
{'text': 'Python', 'max_price': 3000}
    SELECT title, price_cents FROM books WHERE hidden = 0 AND title LIKE ? AND price_cents <= ? ORDER BY title ASC
    ('%Python%', 3000)
    rows: 2
{'authors': ['Ada Okafor', 'Sam Reyes'], 'sort': 'dear'}
    SELECT title, price_cents FROM books WHERE hidden = 0 AND author IN (?, ?) ORDER BY price_cents DESC
    ('Ada Okafor', 'Sam Reyes')
    rows: 4
{'text': 'o', 'authors': ['Lee Park'], 'max_price': 2000, 'sort': 'cheap'}
    SELECT title, price_cents FROM books WHERE hidden = 0 AND title LIKE ? AND author IN (?) AND price_cents <= ? ORDER BY price_cents ASC
    ('%o%', 'Lee Park', 2000)
    rows: 1
unknown sort -> ValueError: unknown sort 'price; DROP TABLE books'; choose one of ['cheap', 'dear', 'title']
empty list   -> UnsafeTemplate: authors: an empty list cannot be expanded
SQLite itself runs 'author IN ()' and counts 0 rows
SQLite itself runs 'author NOT IN ()' and counts 9 rows
a str is data: SELECT title FROM books WHERE ? ('hidden = 0',) -> 0 rows
SQLite variable limit 32766, 32776 values -> too many SQL variables
our cap of 1000      -> many: 32776 items is more than 1000
200 random combinations: placeholders always equal parameters

What the output shows

• Dynamic queries stay parameterized. Four different combinations of filters produce four different query texts, and in each one the only values in the text are ? marks. The parameters come out in the order the placeholders appear, which is the property that nesting must keep right.
• Sort order comes from an allowlist of fragments. SORTS maps the three accepted keys to fixed templates. An unknown key such as price; DROP TABLE books is a ValueError, and it never reaches SQL. This is the same idea as before, map the value to a legal one, with templates as the legal values.
• An empty list is refused. SQLite itself runs IN () and NOT IN () without complaint (0 rows and 9 rows here), but whether an empty selection should mean no rows or no filter at all is a decision for the caller, so tsql.py makes the caller choose.
• A plain string is data, not SQL. The line a str is data shows WHERE ? with the parameter 'hidden = 0', and zero rows. Someone who tries to pass a SQL fragment as a string does not create an injection hole. They get an empty result and no error, which is a silent wrong answer again. The way to pass a fragment is to pass a template, as SORTS does.
• Lists have a hard ceiling. SQLite’s documentation gives the limit: the maximum parameter number “defaults to 999 for SQLite versions prior to 3.32.0 (2020-05-22) or 32766 for SQLite versions after 3.32.0”. This machine reports 32766, and a list of 32,776 values fails with too many SQL variables. The cap of 1,000 in tsql.py stops an oversized list from a request long before that.
• The invariant held in 200 random combinations: in each one, the number of ? marks equaled the number of parameters.

Step 9: Match LIKE patterns literally

Placeholders stop injection. They do not stop a user’s % or _ from acting as wildcards inside a LIKE pattern. That is not an injection, since the user only changes which rows match, but it is a correctness bug: the search returns books that do not contain what the user typed. Save step09_like_wildcards.py and run it with your version 4:

# step09_like_wildcards.py - parameters stop injection, not wildcards
from data import make_db
from tsql import run

conn = make_db()


def search(text):
    rows = run(conn, t"SELECT title FROM books WHERE hidden = 0 AND title LIKE {'%' + text + '%'} ORDER BY title")
    return [title for (title,) in rows]


print("search '100%'  :", search("100%"))
print("search 'Pyth_n':", search("Pyth_n"))

search '100%'  : ['100 Python Tips', '100% Pure Python']
search 'Pyth_n': ['100 Python Tips', '100% Pure Python', 'Learning Python the Slow Way']

Searching for 100% returned two books because the pattern %100%% means contains 100, followed by anything. Searching for Pyth_n returned three titles because _ matches any single character, here the o. Neither title literally contains what the user typed.

SQLite’s LIKE has an escape mechanism: “This character may be used in the LIKE pattern to include literal percent or underscore characters.” The fix is a second format spec, :like, that escapes the wildcards in the user’s text, wraps it in % signs, and adds the ESCAPE clause itself so nobody has to remember it. Update tsql.py to its final version 5: add like_pattern above sql and replace sql:

# tsql.py (add like_pattern above sql, replace sql)
def like_pattern(text: str) -> str:
    escaped = text.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_")
    return f"%{escaped}%"


def sql(template: Template, idents: frozenset = frozenset()) -> Query:
    if not isinstance(template, Template):
        raise TypeError(f"sql() needs a t-string, got {type(template).__name__}")
    parts = []
    params = []
    for item in template:
        if isinstance(item, str):
            parts.append(item)
            continue
        if item.conversion is not None:
            raise UnsafeTemplate(f"{item.expression}: conversion !{item.conversion} is not supported")
        value = item.value
        if item.format_spec == "ident":
            if not isinstance(value, str) or not IDENT.fullmatch(value) or value not in idents:
                raise UnsafeTemplate(f"{value!r} is not an allowed identifier")
            parts.append('"' + value + '"')
            continue
        if item.format_spec == "like":
            if not isinstance(value, str):
                raise UnsafeTemplate(f"{item.expression}: :like needs a str, got {type(value).__name__}")
            parts.append("? ESCAPE '\\'")
            params.append(like_pattern(value))
            continue
        if item.format_spec:
            raise UnsafeTemplate(f"{item.expression}: format spec {item.format_spec!r} is not supported")
        if isinstance(value, Template):
            inner = sql(value, idents)
            parts.append(inner.text)
            params.extend(inner.params)
        elif isinstance(value, (list, tuple)):
            if not value:
                raise UnsafeTemplate(f"{item.expression}: an empty list cannot be expanded")
            if len(value) > MAX_LIST_ITEMS:
                raise UnsafeTemplate(f"{item.expression}: {len(value)} items is more than {MAX_LIST_ITEMS}")
            parts.append(", ".join(["?"] * len(value)))
            params.extend(value)
        else:
            parts.append("?")
            params.append(value)
    return Query("".join(parts), tuple(params))

Now save step10_like_spec.py and run it:

# step10_like_spec.py - the same searches with the :like format spec
from data import make_db
from tsql import UnsafeTemplate, run, sql

conn = make_db()


def search(text):
    rows = run(conn, t"SELECT title FROM books WHERE hidden = 0 AND title LIKE {text:like} ORDER BY title")
    return [title for (title,) in rows]


query = sql(t"SELECT title FROM books WHERE title LIKE {'100%':like}")
print("query          :", query.text)
print("params         :", query.params)
print("search '100%'  :", search("100%"))
print("search 'Pyth_n':", search("Pyth_n"))
print("search 'Python':", search("Python"))
print("search 'zzz%' OR hidden = 1 --':", search("zzz%' OR hidden = 1 --"))
try:
    run(conn, t"SELECT title FROM books WHERE title LIKE {42:like}")
except UnsafeTemplate as exc:
    print("an int is refused ->", exc)

query          : SELECT title FROM books WHERE title LIKE ? ESCAPE '\'
params         : ('%100\\%%',)
search '100%'  : ['100% Pure Python']
search 'Pyth_n': []
search 'Python': ['100 Python Tips', '100% Pure Python', 'Learning Python the Slow Way']
search 'zzz%' OR hidden = 1 --': []
an int is refused -> 42: :like needs a str, got int

The rendered text ends with ESCAPE '\'. The parameter prints as '%100\\%%' only because Python shows a backslash twice inside a tuple; the real string has one. Searching for 100% now finds the one book that has a percent sign in its title, Pyth_n finds nothing, an ordinary word still works, the injection string finds nothing, and a number given to :like is refused.

Step 10: Make the safe path the only path

Good helpers do not help if a teammate can still write conn.execute(f"...") on a Friday afternoon. The last layer is a rule enforced by a machine: nothing outside tsql.py may call execute, executemany or executescript. Save this as no_raw_execute.py. It reads Python files, builds their syntax tree with the standard ast module, and reports any call to one of those methods:

# no_raw_execute.py - fail the build when code outside tsql.py calls execute() directly
import ast
import sys
from pathlib import Path

ALLOWED_FILES = {"tsql.py"}
BANNED_METHODS = {"execute", "executemany", "executescript"}


def violations(path: Path):
    tree = ast.parse(path.read_text(encoding="utf-8"), filename=str(path))
    for node in ast.walk(tree):
        if isinstance(node, ast.Call) and isinstance(node.func, ast.Attribute) and node.func.attr in BANNED_METHODS:
            yield node.lineno, node.func.attr


def main(paths: list[str]) -> int:
    found = 0
    for name in paths:
        path = Path(name)
        if path.name in ALLOWED_FILES:
            continue
        for lineno, method in violations(path):
            print(f"{path}:{lineno}: .{method}() called outside tsql.py; use tsql.run() instead")
            found += 1
    return 1 if found else 0


if __name__ == "__main__":
    sys.exit(main(sys.argv[1:]))

Run it on the two files from Step 5, the bad one first:

python no_raw_execute.py bad_fstring.py good_template.py

bad_fstring.py:3: .execute() called outside tsql.py; use tsql.run() instead

The script prints one line for the f-string version and exits with status 1, which fails a CI job. Run on good_template.py alone, it prints nothing and exits with status 0, because that file goes through run(). In CI you would pass it the list of your application’s source files. Test and migration scripts that create tables need either an exemption or their own folder, which you handle by leaving them out of the file list.

Know what this gate does not do. It matches method names, so a driver call hidden behind getattr or a wrapper with another name is invisible to it. It complements the S608 rule from Step 5, which looks at how strings are built, and the run-time guard in sql(). Three imperfect layers that fail in different ways are stronger than one layer that you trust completely.

Step 11: Test the promises, then try to break them

Write the tests

Each behavior you built is now a promise that a future edit can break. Save this as test_tsql.py. It checks placeholders, the type guard, the refusals, the allowlist, nesting, lists, LIKE, and it runs two hostile-input suites: twelve nasty strings (quotes, comments, UNION, a NUL byte, a newline) through both the plain parameter and the :like path, and 2,000 seeded random strings assembled from SQL fragments, none of which may return an unreleased book.

# test_tsql.py - the promises tsql.py makes, written as tests
import random

import pytest

from data import make_db
from tsql import MAX_LIST_ITEMS, UnsafeTemplate, join, like_pattern, run, sql

IDENTS = frozenset({"title", "author", "price_cents"})
NASTY = [
    "x' OR '1'='1",
    "'; DROP TABLE books; --",
    "zzz%' OR hidden = 1 --",
    "\" OR \"\"=\"",
    "' UNION SELECT title, author, 0, 0, 0 FROM books --",
    "\\",
    "%",
    "_",
    "\x00",
    "Robert'); DROP TABLE books;--",
    "1; SELECT 1",
    "\n",
]


@pytest.fixture()
def conn():
    return make_db()


def test_values_become_placeholders():
    query = sql(t"SELECT title FROM books WHERE author = {'Ada Okafor'} AND price_cents <= {5000}")
    assert query.text == "SELECT title FROM books WHERE author = ? AND price_cents <= ?"
    assert query.params == ("Ada Okafor", 5000)


def test_f_string_is_refused():
    author = "Ada"
    with pytest.raises(TypeError, match="needs a t-string"):
        sql(f"SELECT title FROM books WHERE author = '{author}'")


def test_plain_str_is_refused():
    with pytest.raises(TypeError):
        sql("SELECT title FROM books")


def test_conversion_is_refused():
    price = 1500
    with pytest.raises(UnsafeTemplate, match="conversion"):
        sql(t"SELECT title FROM books WHERE price_cents <= {price!r}")


def test_unknown_format_spec_is_refused():
    price = 1500
    with pytest.raises(UnsafeTemplate, match="format spec"):
        sql(t"SELECT title FROM books WHERE price_cents <= {price:.2f}")


def test_ident_needs_an_allowlist():
    with pytest.raises(UnsafeTemplate):
        sql(t"SELECT title FROM books ORDER BY {'title':ident}")


@pytest.mark.parametrize("column", ["hidden", "price_cents; DROP TABLE books", '"title"', "title\n", "", 42, None])
def test_ident_refuses_anything_not_allowed(column):
    with pytest.raises(UnsafeTemplate):
        sql(t"SELECT title FROM books ORDER BY {column:ident}", IDENTS)


def test_ident_is_quoted_and_sorts(conn):
    query = sql(t"SELECT title FROM books ORDER BY {'price_cents':ident}", IDENTS)
    assert query.text == 'SELECT title FROM books ORDER BY "price_cents"'
    assert query.params == ()
    rows = run(conn, t"SELECT price_cents FROM books WHERE hidden = 0 ORDER BY {'price_cents':ident}", IDENTS)
    prices = [price for (price,) in rows]
    assert prices == sorted(prices)


def test_nested_templates_keep_parameter_order():
    inner = t"author = {'Lee Park'} AND price_cents <= {2000}"
    query = sql(t"SELECT title FROM books WHERE title LIKE {'%o%'} AND {inner} AND hidden = {0}")
    assert query.text == "SELECT title FROM books WHERE title LIKE ? AND author = ? AND price_cents <= ? AND hidden = ?"
    assert query.params == ("%o%", "Lee Park", 2000, 0)


def test_join_joins_three_pieces():
    pieces = [t"a = {1}", t"b = {2}", t"c = {3}"]
    query = sql(t"WHERE {join(t' AND ', pieces)}")
    assert query.text == "WHERE a = ? AND b = ? AND c = ?"
    assert query.params == (1, 2, 3)
    assert sql(t"WHERE {join(t' AND ', [])}").text == "WHERE "


def test_a_str_fragment_is_data_not_sql(conn):
    fragment = "hidden = 1"
    query = sql(t"SELECT title FROM books WHERE {fragment}")
    assert query.text == "SELECT title FROM books WHERE ?"
    assert conn.execute(query.text, query.params).fetchall() == []


def test_lists_expand_to_placeholders(conn):
    authors = ["Ada Okafor", "Lee Park"]
    query = sql(t"SELECT title FROM books WHERE author IN ({authors})")
    assert query.text == "SELECT title FROM books WHERE author IN (?, ?)"
    assert len(conn.execute(query.text, query.params).fetchall()) == 4


def test_empty_list_is_refused():
    with pytest.raises(UnsafeTemplate, match="empty list"):
        sql(t"SELECT title FROM books WHERE author IN ({[]})")


def test_long_list_is_refused():
    values = list(range(MAX_LIST_ITEMS + 1))
    with pytest.raises(UnsafeTemplate, match="more than"):
        sql(t"SELECT 1 WHERE 1 IN ({values})")


def test_like_pattern_escapes_wildcards():
    assert like_pattern("100%") == "%100\\%%"
    assert like_pattern("a_b") == "%a\\_b%"
    assert like_pattern("a\\b") == "%a\\\\b%"


def test_like_spec_matches_literally(conn):
    rows = run(conn, t"SELECT title FROM books WHERE title LIKE {'100%':like}")
    assert [title for (title,) in rows] == ["100% Pure Python"]
    assert run(conn, t"SELECT title FROM books WHERE title LIKE {'Pyth_n':like}") == []


def test_like_spec_needs_a_string():
    with pytest.raises(UnsafeTemplate):
        sql(t"SELECT title FROM books WHERE title LIKE {42:like}")


def test_template_plus_str_is_a_type_error():
    base = t"SELECT 1 "
    with pytest.raises(TypeError):
        base + "WHERE 1"


@pytest.mark.parametrize("text", NASTY)
def test_hostile_text_never_reaches_the_sql_text(conn, text):
    query = sql(t"SELECT title FROM books WHERE hidden = 0 AND title LIKE {text:like}")
    assert query.text == "SELECT title FROM books WHERE hidden = 0 AND title LIKE ? ESCAPE '\\'"
    rows = conn.execute(query.text, query.params).fetchall()
    assert all(not title.startswith("Unreleased") for (title,) in rows)
    assert conn.execute("SELECT count(*) FROM books").fetchone() == (9,)


@pytest.mark.parametrize("text", NASTY)
def test_hostile_text_as_a_plain_parameter(conn, text):
    query = sql(t"SELECT title FROM books WHERE hidden = 0 AND title = {text}")
    assert query.text == "SELECT title FROM books WHERE hidden = 0 AND title = ?"
    assert query.params == (text,)
    assert conn.execute(query.text, query.params).fetchall() == []


def test_random_hostile_strings_never_leak(conn):
    rng = random.Random(7)
    alphabet = ["'", '"', ";", "--", "%", "_", "\\", " OR ", "1=1", "UNION", "SELECT", "hidden", "=", "(", ")", "a", "0"]
    for _ in range(2000):
        text = "".join(rng.choice(alphabet) for _ in range(rng.randint(1, 12)))
        rows = run(conn, t"SELECT title FROM books WHERE hidden = 0 AND title LIKE {text:like}")
        assert all(not title.startswith("Unreleased") for (title,) in rows), text

python -m pytest -q test_tsql.py

.................................................                                                                                                                  [100%]
49 passed in 0.05s

Forty-nine tests pass in a fraction of a second. A passing suite only matters if it can fail, so the next script checks that.

Weaken the code and watch the tests notice

Save break_it.py. For each of six one-line mutations it copies the project into a temporary folder, damages tsql.py there (it never touches your real file), runs the tests, and prints which ones failed.

# break_it.py - weaken tsql.py one line at a time and see which tests notice
import re
import shutil
import subprocess
import sys
import tempfile
from pathlib import Path

LAB = Path(__file__).resolve().parent
MUTATIONS = [
    ("type guard removed", "    if not isinstance(template, Template):\n        raise TypeError", "    if False:\n        raise TypeError"),
    ("value pasted into the SQL text", '            parts.append("?")\n            params.append(value)', "            parts.append(repr(value))"),
    ("identifier allowlist skipped", " or value not in idents", ""),
    ("LIKE wildcards not escaped", r""".replace("%", "\\%").replace("_", "\\_")""", ""),
    ("empty list allowed", "            if not value:", "            if False:"),
    ("conversion ignored", "        if item.conversion is not None:", "        if False:"),
]

source = (LAB / "tsql.py").read_text(encoding="utf-8")
print(f"{'mutation':34} failed  tests that noticed")
for label, old, new in MUTATIONS:
    if source.count(old) != 1:
        raise SystemExit(f"mutation {label!r}: text found {source.count(old)} times")
    work = Path(tempfile.mkdtemp(prefix="tstring-mutant-"))
    try:
        for name in ("data.py", "test_tsql.py"):
            shutil.copy(LAB / name, work / name)
        (work / "tsql.py").write_text(source.replace(old, new), encoding="utf-8")
        done = subprocess.run([sys.executable, "-m", "pytest", "-q", "-p", "no:cacheprovider", "--no-header", "-rf"],
                              cwd=work, capture_output=True, text=True, encoding="utf-8", errors="replace")
        failed = re.findall(r"^FAILED test_tsql\.py::(\w+)", done.stdout, re.M)
        unique = sorted(set(failed))
        shown = ", ".join(unique[:2]) + (f" (+{len(unique) - 2} more)" if len(unique) > 2 else "")
        print(f"{label:34} {len(failed):>4}    {shown}")
    finally:
        shutil.rmtree(work, ignore_errors=True)

python break_it.py

mutation                           failed  tests that noticed
type guard removed                    2    test_f_string_is_refused, test_plain_str_is_refused
value pasted into the SQL text       16    test_a_str_fragment_is_data_not_sql, test_hostile_text_as_a_plain_parameter (+3 more)
identifier allowlist skipped          2    test_ident_needs_an_allowlist, test_ident_refuses_anything_not_allowed
LIKE wildcards not escaped            2    test_like_pattern_escapes_wildcards, test_like_spec_matches_literally
empty list allowed                    1    test_empty_list_is_refused
conversion ignored                    1    test_conversion_is_refused

Every mutation is caught by at least one test, and the dangerous ones, pasting values into the SQL text and skipping the identifier allowlist, are caught by several. The mutation that pastes values into the text fails 16 test cases, including the hostile-input suite that uses plain parameters. If you delete a safeguard and nothing here fails, you have found a gap in the tests, not a safe change.

Step 12: Apply the same idea to HTML

Why HTML is harder than SQL

SQL has a clean split between code and values, and the placeholder expresses it. HTML has no such split. The same value needs different treatment depending on where it lands: between tags, inside a quoted attribute, inside an unquoted attribute, in a URL, in an event handler. OWASP’s cross-site scripting cheat sheet makes the point in two sentences: “Keep element and attribute names fixed. Attributes containing URLs, CSS, HTML, or JavaScript need controls for those additional contexts.” It is also candid about the limit of the whole approach: “Output encoding is not perfect. It will not always prevent XSS.”

So html() follows a simple policy. It escapes values for element text and for quoted attributes. It refuses every position it cannot protect. A table makes the rules concrete:

Build the position tracker

To know where a value lands, html() reads the fixed text of the template as it goes, like a very small browser, and tracks which of six states it is in: text, tag (between attributes), before_value (just after an =), unquoted, dq (inside double quotes) and sq (inside single quotes). Save the module in three parts. First the imports, constants, the SafeHTML marker type and the Scan record:

# thtml.py - turn an HTML t-string into text that is escaped for the place each value lands
import html as _html
from dataclasses import dataclass
from string.templatelib import Template

from tsql import UnsafeTemplate

URL_ATTRS = frozenset({"href", "src", "action", "formaction", "poster", "cite", "data"})
URL_PREFIXES = ("https://", "http://", "mailto:", "/", "#")
UNSUPPORTED_MARKUP = ("<script", "<style", "<!--")
POSITIONS = {
    "tag": "a tag name or attribute name",
    "before_value": "an unquoted attribute value",
    "unquoted": "an unquoted attribute value",
}


class SafeHTML(str):
    """Markup that html() has already made safe for the content of an element."""

    __slots__ = ()


@dataclass
class Scan:
    state: str = "text"  # text, tag, before_value, unquoted, dq (double quotes) or sq (single quotes)
    word: str = ""  # the name being read inside a tag
    last: str = ""  # the last complete name inside the tag
    attr: str = ""  # the attribute whose value we are inside
    fresh: bool = False  # True while nothing has been written into the current quoted value yet

Then the tracker itself. advance() feeds the fixed text through the state machine one character at a time. A > inside a quoted value does not end the tag, and spaces around the = are handled, because both are legal HTML:

# thtml.py (continued)
def advance(scan: Scan, text: str) -> None:
    """Walk the static text of a template and track where the next value will land."""
    for ch in text:
        state = scan.state
        if state == "text":
            if ch == "<":
                scan.state, scan.word, scan.last = "tag", "", ""
        elif state == "tag":
            if ch == ">":
                scan.state = "text"
            elif ch == "=":
                scan.attr = (scan.word or scan.last).lower()
                scan.word, scan.state = "", "before_value"
            elif ch.isspace() or ch == "/":
                scan.last, scan.word = scan.word or scan.last, ""
            else:
                scan.word += ch
        elif state == "before_value":
            if ch in "\"'":
                scan.state, scan.fresh = ("dq" if ch == '"' else "sq"), True
            elif ch == ">":
                scan.state = "text"
            elif not ch.isspace():
                scan.state = "unquoted"
        elif state == "unquoted":
            if ch.isspace():
                scan.state, scan.word, scan.last = "tag", "", ""
            elif ch == ">":
                scan.state = "text"
        else:  # inside a quoted value
            if ch == ('"' if state == "dq" else "'"):
                scan.state, scan.word, scan.last = "tag", "", ""
            else:
                scan.fresh = False

Finally the rendering functions and html(). Notice that html() imports UnsafeTemplate from tsql.py and shares its rule about conversions and format specs. For URL attributes it uses a prefix allowlist, not a search for the word “javascript”, for reasons the browser tests below show:

# thtml.py (continued)
def safe_url(value: str) -> bool:
    return value.lower().startswith(URL_PREFIXES)


def render_content(value) -> str:
    if isinstance(value, SafeHTML):
        return value
    if isinstance(value, Template):
        return html(value)
    if isinstance(value, (list, tuple)):
        return "".join(render_content(part) for part in value)
    return _html.escape(str(value), quote=True)


def render_attribute(item, scan: Scan) -> str:
    if scan.attr.startswith("on") or scan.attr == "style":
        raise UnsafeTemplate(f"{item.expression}: a value inside {scan.attr}= is code, not text")
    if not isinstance(item.value, (str, int, float)):
        raise UnsafeTemplate(f"{item.expression}: attribute values must be str, int or float")
    text = str(item.value)
    if scan.attr in URL_ATTRS and scan.fresh and not safe_url(text):
        raise UnsafeTemplate(f"{item.expression}: {text[:30]!r} is not an allowed URL for {scan.attr}=")
    scan.fresh = False
    return _html.escape(text, quote=True)


def html(template: Template) -> SafeHTML:
    if not isinstance(template, Template):
        raise TypeError(f"html() needs a t-string, got {type(template).__name__}")
    scan = Scan()
    out = []
    for item in template:
        if isinstance(item, str):
            lowered = item.lower()
            for marker in UNSUPPORTED_MARKUP:
                if marker in lowered:
                    raise UnsafeTemplate(f"static {marker} is not supported in templates")
            out.append(item)
            advance(scan, item)
            continue
        if item.conversion is not None or item.format_spec:
            raise UnsafeTemplate(f"{item.expression}: conversions and format specs are not supported")
        if scan.state == "text":
            out.append(render_content(item.value))
        elif scan.state in ("dq", "sq"):
            out.append(render_attribute(item, scan))
        else:
            raise UnsafeTemplate(
                f"{item.expression}: not allowed in {POSITIONS[scan.state]}; "
                "use element text or a quoted attribute value"
            )
    return SafeHTML("".join(out))

Try it on text and quoted attributes

Save step12_html_text.py and run it:

# step12_html_text.py - html() escapes values for element text, and composes
from thtml import html

name = '<script>alert("hi")</script>'
print(html(t"<p>Hello, {name}!</p>"))

rows = [t"<li>{label}</li>" for label in ("Ada", "<b>Sam</b>")]
print(html(t"<ul>{rows}</ul>"))

badge = html(t"<em>{name}</em>")
print(html(t"<p>{badge}</p>"))
print(type(badge).__name__, "| plain str passed through as data:", html(t"<p>{'<em>x</em>'}</p>"))

title = 'He said "hi" & left'
print(html(t'<a title="{title}">link</a>'))
print(html(t"<a title='{title}'>link</a>"))
print(html(t'<a title="{badge}">link</a>'))

<p>Hello, &lt;script&gt;alert(&quot;hi&quot;)&lt;/script&gt;!</p>
<ul><li>Ada</li><li>&lt;b&gt;Sam&lt;/b&gt;</li></ul>
<p><em>&lt;script&gt;alert(&quot;hi&quot;)&lt;/script&gt;</em></p>
SafeHTML | plain str passed through as data: <p>&lt;em&gt;x&lt;/em&gt;</p>
<a title="He said &quot;hi&quot; &amp; left">link</a>
<a title='He said &quot;hi&quot; &amp; left'>link</a>
<a title="&lt;em&gt;&amp;lt;script&amp;gt;alert(&amp;quot;hi&amp;quot;)&amp;lt;/script&amp;gt;&lt;/em&gt;">link</a>

The script tag in the name came out as inert text (<script> on the page, harmless in a browser). A list of templates is joined into two list items, a nested html() result is not escaped a second time, a plain string that looks like markup is escaped, and quotes inside attributes become " in whichever quote style the template uses. The last line shows the rule from the table: SafeHTML placed in an attribute is escaped again, so its angle brackets cannot reach the attribute’s quote.

Watch an unquoted attribute break out in a real browser

Now the experiment that explains the refusal rule. Save browser.py, a helper that loads a page in headless Edge and returns the page as the browser built it (adjust the EDGE path for Chrome or for another operating system):

# browser.py - load a page in headless Edge and return the DOM it ends up with
import shutil
import subprocess
import tempfile
from pathlib import Path

EDGE = r"C:\Program Files (x86)\Microsoft\Edge\Application\msedge.exe"  # any Chromium-based browser works


def dom_of(page_html: str, budget_ms: int = 4000) -> str:
    folder = Path(tempfile.mkdtemp(prefix="tstring-page-"))
    profile = tempfile.mkdtemp(prefix="tstring-profile-")
    try:
        page = folder / "page.html"
        page.write_text(page_html, encoding="utf-8")
        done = subprocess.run(
            [EDGE, "--headless=new", "--disable-gpu", "--no-first-run", f"--user-data-dir={profile}",
             f"--virtual-time-budget={budget_ms}", "--dump-dom", page.as_uri()],
            capture_output=True, timeout=120,
        )
        return done.stdout.decode("utf-8", "replace")
    finally:
        shutil.rmtree(folder, ignore_errors=True)
        shutil.rmtree(profile, ignore_errors=True)

Then step13_html_browser.py. It builds an image tag two ways, with the payload x onerror=this.dataset.xss=1 as the value of the alt attribute. The first way uses Python’s own html.escape and leaves the attribute unquoted, which is the mistake. The second uses html() with quotes. The browser is asked whether the handler ran: a handler that runs stamps data-xss="1" on the element.

# step13_html_browser.py - does an unquoted attribute really break out in a browser?
import html as stdlib_html
import re

from browser import dom_of
from thtml import UnsafeTemplate, html

payload = "x onerror=this.dataset.xss=1"
STAMP = 'data-xss="1"'


def naive_card(caption):
    return f"<img src=missing.png alt={stdlib_html.escape(caption)}>"


def safe_card(caption):
    return html(t'<img src="missing.png" alt="{caption}">')


def browser_view(fragment):
    dom = dom_of(f"<!doctype html><meta charset=utf-8><body>{fragment}</body>")
    return re.search(r"<img[^>]*>", dom).group(0)


for label, fragment in (("html.escape, unquoted", naive_card(payload)), ("html(), quoted", safe_card(payload))):
    view = browser_view(fragment)
    print(f"{label:22}: {fragment}")
    print(f"{'browser DOM':22}: {view}")
    print(f"{'handler ran':22}: {STAMP in view}")

for label, build in (
    ("unquoted placeholder", lambda: html(t"<img src=missing.png alt={payload}>")),
    ("hostile value in src", lambda: html(t'<img src="{payload}" alt="x">')),
):
    try:
        build()
    except UnsafeTemplate as exc:
        print(f"{label:22}: {exc}")

python step13_html_browser.py

html.escape, unquoted : <img src=missing.png alt=x onerror=this.dataset.xss=1>
browser DOM           : <img src="missing.png" alt="x" onerror="this.dataset.xss=1" data-xss="1">
handler ran           : True
html(), quoted        : <img src="missing.png" alt="x onerror=this.dataset.xss=1">
browser DOM           : <img src="missing.png" alt="x onerror=this.dataset.xss=1">
handler ran           : False
unquoted placeholder  : payload: not allowed in an unquoted attribute value; use element text or a quoted attribute value
hostile value in src  : payload: 'x onerror=this.dataset.xss=1' is not an allowed URL for src=

In the first case html.escape did nothing useful, because the payload contains none of the five characters it changes (the ampersand, the two angle brackets and the two quote characters). Python’s documentation says the quote option “helps for inclusion in an HTML attribute value delimited by quotes”, and this value was not delimited by quotes. The browser split the unquoted value at the space, created a real onerror attribute, the missing image triggered it, and the handler stamped the element: handler ran is True. The HTML specification states the rule that was broken: an unquoted attribute value “must not contain any literal ASCII whitespace”, and the first space is where this one stopped.

In the second case the whole payload stayed inside the alt value and no handler exists: handler ran is False. The last two lines show html() refusing the unquoted template before it renders anything, and refusing the payload as a src value because it does not look like a URL.

Check URL attributes against the browser

Escaping cannot make a dangerous URL safe, because javascript:alert(1) contains nothing to escape. That is why safe_url() allows a short list of prefixes (https://, http://, mailto:, / and #) instead of hunting for bad words. The URL standard shows why hunting fails: the parser must “Remove any leading and trailing C0 control or space from input” and “Remove all ASCII tab or newline from input”, so a scheme can hide behind a control character, a space or a tab. Save step14_urls.py. It asks Edge for the protocol it assigns to each test URL and compares that with safe_url():

# step14_urls.py - which URLs does the browser treat as scripts, and does safe_url() agree?
import json

from browser import dom_of
from thtml import safe_url

URLS = [
    "https://example.org/a",
    "/docs/page",
    "javascript:alert(1)",
    "JaVaScRiPt:alert(1)",
    " javascript:alert(1)",
    "java\tscript:alert(1)",
    "\x01javascript:alert(1)",
    "data:text/html,hello",
]
PAGE = r"""<!doctype html><meta charset=utf-8>
<script type="application/json" id="urls">__URLS__</script>
<pre id="out"></pre>
<script>
const urls = JSON.parse(document.getElementById('urls').textContent);
const lines = [];
for (const u of urls) {
  const a = document.createElement('a');
  a.setAttribute('href', u);
  lines.push(a.protocol);
}
document.getElementById('out').textContent = lines.join('|');
</script>"""

dom = dom_of(PAGE.replace("__URLS__", json.dumps(URLS)))
protocols = dom.split('<pre id="out">')[1].split("</pre>")[0].split("|")
print(f"{'url':26} {'browser protocol':17} safe_url()")
for url, protocol in zip(URLS, protocols):
    print(f"{url!r:26} {protocol:17} {safe_url(url)}")
dangerous = [u for u, p in zip(URLS, protocols) if p in ("javascript:", "data:")]
print("script-like URLs accepted by safe_url():", [u for u in dangerous if safe_url(u)])

python step14_urls.py

url                        browser protocol  safe_url()
'https://example.org/a'    https:            True
'/docs/page'               file:             True
'javascript:alert(1)'      javascript:       False
'JaVaScRiPt:alert(1)'      javascript:       False
' javascript:alert(1)'     javascript:       False
'java\tscript:alert(1)'    javascript:       False
'\x01javascript:alert(1)'  javascript:       False
'data:text/html,hello'     data:             False
script-like URLs accepted by safe_url(): []

Edge treated five of the eight strings as javascript: URLs, including the ones with a leading space, an embedded tab and a leading control character, and a sixth as data:. safe_url() rejected all six, and the final line confirms that no script-like URL was accepted. The relative URL shows file: only because the test page itself was opened from a file.

Test the HTML promises

Save test_thtml.py. It covers escaping, composition, every refusal in the table, twelve URL prefixes, and the two parser details mentioned above.

# test_thtml.py - the promises thtml.py makes, written as tests
import pytest

from thtml import SafeHTML, UnsafeTemplate, html, safe_url


def test_text_is_escaped():
    name = "<script>alert(1)</script>"
    assert html(t"<p>{name}</p>") == "<p>&lt;script&gt;alert(1)&lt;/script&gt;</p>"


def test_safe_html_is_not_escaped_twice():
    inner = html(t"<em>{'a&b'}</em>")
    assert isinstance(inner, SafeHTML)
    assert html(t"<p>{inner}</p>") == "<p><em>a&amp;b</em></p>"


def test_a_plain_str_that_looks_like_markup_is_escaped():
    assert html(t"<p>{'<em>x</em>'}</p>") == "<p>&lt;em&gt;x&lt;/em&gt;</p>"


def test_lists_of_templates_are_joined():
    rows = [t"<li>{label}</li>" for label in ("a", "<b>")]
    assert html(t"<ul>{rows}</ul>") == "<ul><li>a</li><li>&lt;b&gt;</li></ul>"


def test_quoted_attribute_values_are_escaped():
    value = '" onmouseover="alert(1)'
    assert html(t'<a title="{value}">x</a>') == '<a title="&quot; onmouseover=&quot;alert(1)">x</a>'
    assert html(t"<a title='{value}'>x</a>") == "<a title='&quot; onmouseover=&quot;alert(1)'>x</a>"


def test_safe_html_inside_an_attribute_is_escaped_again():
    badge = html(t"<em>x</em>")
    assert html(t'<a title="{badge}">x</a>') == '<a title="&lt;em&gt;x&lt;/em&gt;">x</a>'


@pytest.mark.parametrize(
    "build",
    [
        lambda v: html(t"<img src={v} alt=x>"),
        lambda v: html(t"<img src=x{v}>"),
        lambda v: html(t"<img src = {v}>"),
        lambda v: html(t"<{v}>text</{v}>"),
        lambda v: html(t"<div {v}>text</div>"),
        lambda v: html(t'<div class="a" {v}>text</div>'),
        lambda v: html(t'<input value="a" {v}>'),
    ],
)
def test_unquoted_and_name_positions_are_refused(build):
    with pytest.raises(UnsafeTemplate):
        build("x onerror=alert(1)")


@pytest.mark.parametrize("attribute", ["onclick", "onerror", "style"])
def test_code_attributes_are_refused(attribute):
    value = "alert(1)"
    template = {
        "onclick": lambda: html(t'<button onclick="{value}">x</button>'),
        "onerror": lambda: html(t'<img onerror="{value}">'),
        "style": lambda: html(t'<p style="{value}">x</p>'),
    }[attribute]
    with pytest.raises(UnsafeTemplate):
        template()


@pytest.mark.parametrize("static", ["<script>x()</script>", "<STYLE>p{}</STYLE>", "<!-- note -->"])
def test_scripts_styles_and_comments_are_refused(static):
    from string.templatelib import Template

    with pytest.raises(UnsafeTemplate):
        html(Template(static))


@pytest.mark.parametrize(
    "url, ok",
    [
        ("https://example.org/a", True),
        ("http://example.org", True),
        ("mailto:[email protected]", True),
        ("/docs/page", True),
        ("#top", True),
        ("javascript:alert(1)", False),
        ("JaVaScRiPt:alert(1)", False),
        (" javascript:alert(1)", False),
        ("java\tscript:alert(1)", False),
        ("\x01javascript:alert(1)", False),
        ("data:text/html,hello", False),
        ("vbscript:x", False),
    ],
)
def test_safe_url(url, ok):
    assert safe_url(url) is ok


def test_urls_are_checked_when_the_value_starts_the_attribute():
    with pytest.raises(UnsafeTemplate):
        html(t'<a href="{"javascript:alert(1)"}">x</a>')
    assert html(t'<a href="{"https://example.org/a b"}">x</a>') == '<a href="https://example.org/a b">x</a>'
    assert html(t'<a href="/users/{"javascript:x"}">x</a>') == '<a href="/users/javascript:x">x</a>'


def test_a_greater_than_sign_inside_a_quoted_value_does_not_end_the_tag():
    assert html(t'<a title="a>b" href="{"/x"}">x</a>') == '<a title="a>b" href="/x">x</a>'


def test_spaces_around_the_equals_sign():
    with pytest.raises(UnsafeTemplate):
        html(t'<a href = "{"javascript:alert(1)"}">x</a>')


def test_conversions_and_specs_are_refused():
    value = 1
    with pytest.raises(UnsafeTemplate):
        html(t"<p>{value!r}</p>")
    with pytest.raises(UnsafeTemplate):
        html(t"<p>{value:>5}</p>")


def test_a_str_is_refused():
    with pytest.raises(TypeError):
        html("<p>hi</p>")

python -m pytest -q test_thtml.py

....................................                                                                                                                               [100%]
36 passed in 0.02s

What html() is and is not

This is a guard for small, trusted-structure fragments, not a replacement for a mature template engine. It does not parse CSS or JavaScript, so it refuses those positions instead of encoding for them. A stray < in static text switches its tracker into tag mode and makes any value placed before the next > refuse, which is a safe failure but an annoying one (write < in the static text). Anyone can wrap untrusted text in SafeHTML(...), an escape hatch of the kind escaping libraries commonly provide, so it is worth searching code reviews for that call. For whole pages, use an engine with automatic escaping; How to Build a Static Site Generator in Python From Scratch shows Jinja2’s autoescape default and the bug it prevents. A Content Security Policy, covered in the XSS tutorial linked above, adds a second line of defense in the browser.

Step 13: See the same design in a production driver

You do not have to write the SQL half yourself in production. Psycopg 3.3, the PostgreSQL driver, accepts t-strings directly. Its documentation explains the benefit in the same terms as this tutorial: “t-strings, instead, are evaluated by Psycopg, which has a chance to process query parameters in a safe way.” Install it and save step15_psycopg.py:

python -m pip install "psycopg[binary]==3.3.6"

# step15_psycopg.py - the same idea in a production driver (psycopg 3.3 renders t-strings itself)
import datetime

import psycopg
from psycopg import sql

name = "O'Reilly"
born = datetime.date(1970, 1, 1)
table = "books"
print("psycopg", psycopg.__version__)
print(sql.as_string(t"INSERT INTO tbl VALUES ({name}, {born})"))
print(sql.as_string(t"INSERT INTO {table:i} (title) VALUES ({name})"))
for bad_table in ('books"; DROP TABLE books; --', "my table"):
    print(sql.as_string(t"SELECT * FROM {bad_table:i}"))
channel = "foo.bar"
print(sql.as_string(t"NOTIFY {channel:i}, {name:l}"))

psycopg 3.3.6
INSERT INTO tbl VALUES ('O''Reilly', '1970-01-01'::date)
INSERT INTO "books" (title) VALUES ('O''Reilly')
SELECT * FROM "books""; DROP TABLE books; --"
SELECT * FROM "my table"
NOTIFY "foo.bar", 'O''Reilly'

The first and the last statements reproduce examples from the Psycopg documentation. A value with a quote in it, O'Reilly, comes out correctly quoted as 'O''Reilly', and a date becomes '1970-01-01'::date. The function sql.as_string() is for display and debugging: it merges the values into the text on the client, whereas executing a t-string with a cursor goes through the driver’s normal processing, which the documentation says “might involve preparing the query, sending the parameters separately, etc.” Psycopg’s format specs :i and :l are close cousins of the specs you built: :i quotes an identifier, so the hostile table name books"; DROP TABLE books; -- becomes a single quoted name with its embedded quote doubled, and :l merges a literal value for statements such as NOTIFY that cannot take parameters. Psycopg also offers :q for a verbatim snippet, which you did not build on purpose: nested templates already cover that need without a way to paste raw text.

One difference is worth copying into your own helper. Psycopg’s identifier spec takes a string and quotes it. tsql.py goes further and requires the name to be on an allowlist, because a quoted name is safe from injection but can still point at a column the user should not sort by.

Step 14: Measure what it costs

One more question that always comes up: does converting a template on every query slow things down? Save step16_cost.py and run it:

# step16_cost.py - what does turning a t-string into a Query cost?
import timeit

from data import make_db
from tsql import sql

name, limit = "Python", 1500


def build():
    return sql(t"SELECT title FROM books WHERE hidden = 0 AND title LIKE {name:like} AND price_cents <= {limit}")


conn = make_db()
query = build()
number = 20_000
build_us = min(timeit.repeat(build, number=number, repeat=5)) / number * 1e6
exec_us = min(timeit.repeat(lambda: conn.execute(query.text, query.params).fetchall(), number=number, repeat=5)) / number * 1e6
print(f"sql() on a template with 2 values : {build_us:.1f} microseconds per call")
print(f"executing the finished query      : {exec_us:.1f} microseconds per call (9-row in-memory table)")

sql() on a template with 2 values : 0.6 microseconds per call
executing the finished query      : 2.7 microseconds per call (9-row in-memory table)

On this machine, building the query from a template with two values took about 0.6 microseconds, and executing that query against a nine-row in-memory table took about 2.7 microseconds. Your absolute numbers will differ, so look at the ratio: turning the template into a query cost less than running that query against a nine-row in-memory table. A real query that touches a disk or crosses a network spends its time elsewhere.

Common mistakes and how to check each one

Confirm the whole thing works end to end

1. Run python -m pytest -q test_tsql.py test_thtml.py and expect 85 passed (49 and 36).
2. Run python break_it.py and expect a nonzero count of failing tests on every one of the six lines.
3. Run python no_raw_execute.py bad_fstring.py good_template.py and expect exactly one report, for bad_fstring.py, and exit status 1.
4. Run python -m mypy --python-version 3.15 --no-error-summary slip.py and expect one error, on the f-string call.
5. Run python step04_first_template.py and expect the hostile search to return [], then python step03_fstring_attack.py and expect the unsafe version to return the two unreleased titles.
6. If you have Edge or Chrome, run python step13_html_browser.py and expect handler ran to be True for the unquoted attribute and False for the quoted one.

If those six checks behave as described, you have a SQL helper that cannot be fed an f-string, cannot be given a surprise format spec, takes column names only from an allowlist, and sends every other value as a parameter, with tests that check each claim.

Where to go next

1. Add the other database paramstyle. PostgreSQL drivers such as Psycopg use %s where SQLite uses ?. A paramstyle argument to sql() would let one helper serve both, but check the driver’s documentation first, since Psycopg already accepts templates itself.
2. Pair the helper with database-level isolation. How to Stop Cross-Tenant Data Leaks in a FastAPI App With PostgreSQL Row-Level Security limits what a query can see even if one slips through, and How to Prevent Broken Object Level Authorization (IDOR) in a FastAPI App covers the authorization bugs that injection hardening does not touch.
3. Apply the same refuse-what-you-cannot-explain idea to other input surfaces: path traversal, untrusted XML, file uploads, server-side request forgery and mass assignment each have a tutorial in this series.
4. If you also follow the newest Python releases, the sentinel() builtin and frozendict are two more 3.15 features that tighten everyday code.
5. Extend thtml.py carefully, or replace it. Every position it refuses is a position where a bug can live; add one only with a test and a browser check like the ones in Step 12.

Sources and method

• PEP 750, Template Strings
• Python documentation, string.templatelib
• Python documentation, sqlite3, re and html
• SQLite, SELECT (ORDER BY), SQLite, expressions (LIKE and ESCAPE) and SQLite, limits
• OWASP SQL Injection Prevention Cheat Sheet and OWASP Cross Site Scripting Prevention Cheat Sheet
• Psycopg 3 documentation, Template string queries
• Ruff documentation, hardcoded-sql-expression (S608)
• HTML Standard, syntax and URL Standard

Method. Everything was run on Windows 11 with Python 3.15.0 (the python.org build), SQLite 3.53.4, pytest 9.1.1, mypy 2.4.0, Ruff 0.17.1, Psycopg 3.3.6 and Microsoft Edge 155.0.4283.62 in headless mode. The listings in this article were rebuilt from the page into a clean folder and run again, and every line of output printed above appeared in that run. The timing figures come from one machine, so expect different absolute numbers and a similar ratio. The lab does not test a PostgreSQL server, other database drivers, Linux or macOS, or browsers other than Edge, and thtml.py is a teaching guard for small fragments, not a full HTML parser.