Parameterized Queries and Injection Defense

The user search endpoint builds its SQL like this:

q := fmt.Sprintf("SELECT id, name FROM users WHERE name LIKE '%%%s%%'", search)
rows, err := db.QueryContext(ctx, q)

A searcher typing o'connor gets a syntax error — and a searcher typing x' OR '1'='1 gets every row in the table. A third variant appends ; DROP TABLE users; and the story writes itself. The bug is not that SQL is fragile; it is that SQL is a programming language, and fmt.Sprintf lets the sender write parts of your program.

Read what the string actually becomes when search is x' UNION SELECT id, password_hash FROM admins --:

SELECT id, name FROM users WHERE name LIKE '%x'
UNION SELECT id, password_hash FROM admins --%'

The attacker's bytes did not stay data — they closed the literal, authored a second statement's worth of logic, and commented out the rest. Every string you concatenate is a joint where their code meets yours, and you cannot append-quote your way to safety: SQL string literals interact with encodings, backslash modes, and dialects in ways that have produced injection bugs in "escaping" functions for decades. The parameterized version is not a variation of the same idea — it is a different data path:

rows, err := db.QueryContext(ctx,
	`SELECT id, name FROM users WHERE name LIKE $1`,
	"%"+search+"%",
)
if err != nil {
	return nil, fmt.Errorf("search users: %w", err)
}
defer rows.Close()

Now search never crosses into the SQL text. The client sends the database two distinct things — a query template containing exactly one placeholder, and a value to fill it — and the database parses only the first. '1'='1 arriving through the value channel is seven characters inside a pattern, not a predicate.

The habit to build: format only structure, never data. Columns, operators, and clauses come from your code; every byte that originated from a request, a config file, or another service travels as an argument. This works the same through QueryContext, QueryRowContext, and ExecContext — the args ...any tail exists precisely so no call site has an excuse to interpolate. The nuance arrives when what you must vary is not data at all — when the shape of the query itself is dynamic. Before getting there, it helps to know exactly what the placeholder is doing underneath.

Why does $1 defeat injection when quote-escaping does not? Because they operate at different layers. Escaping tries to make hostile bytes survive parsing — get the quoting right so the lexer sees one literal. Parameters remove the value from parsing altogether.

When the driver runs db.QueryContext(ctx, "... WHERE id = $1", id), the protocol carries the template and the value on separate channels. Postgres's extended protocol does this literally: the Parse message receives the SQL text alone, prepares it, and Bind messages deliver values as typed data the server never re-lexes. Some drivers escape client-side instead, but they do it inside the driver with full knowledge of the connection's encoding and dialect — one audited implementation, not one per call site. Either way, the grammar your string built contains no user bytes.

The placeholder syntax is dialect-specific and fixed by the driver:

// Postgres / pgx
db.QueryContext(ctx, `SELECT * FROM orders WHERE status = $1 AND total_cents > $2`, status, minCents)
// MySQL / SQLite
db.QueryContext(ctx, `SELECT * FROM orders WHERE status = ? AND total_cents > ?`, status, minCents)

Arguments bind positionally, and a missing or extra argument is an error you catch in the first test run. What parameterization does not cover is shapes — placeholders substitute values, not clauses. Two everyday shapes need recipes.

Variable-length IN lists. WHERE id IN ($1, $2, ...) needs one placeholder per element — build both together, never the SQL alone:

func byIDs(ctx context.Context, db *sql.DB, ids []int64) ([]Order, error) {
	if len(ids) == 0 {
		return nil, nil // or ErrNoInput; IN () is a syntax error
	}
	args := make([]any, len(ids))
	ph := make([]string, len(ids))
	for i, id := range ids {
		args[i] = id
		ph[i] = fmt.Sprintf("$%d", i+1) // generated structure, fixed pattern
	}
	q := fmt.Sprintf(`SELECT id, total_cents FROM orders WHERE id IN (%s)`,
		strings.Join(ph, ", "))
	rows, err := db.QueryContext(ctx, q, args...)
	// ... standard rows pattern from database/sql
}

The fmt.Sprintf here is safe because its output is placeholder positions only — the values still ride the argument channel. Drivers with slice support (pgx expands = ANY($1)) do this for you.

Batch inserts. Same discipline: one row's placeholder tuple repeated with running indices, all values as args. The rule that survives every case: if user data appears in it, the format verb can only ever produce a $n. When the format must produce an identifier or keyword instead, you have left value territory — and parameters no longer apply.

The products endpoint lets clients choose the sort: ?sort=price. A placeholder cannot help, because ORDER BY $1 binds a value to compare against — not a column to order by. So the tempting code is fmt.Sprintf("... ORDER BY %s", r.URL.Query().Get("sort")), and the input price; DROP TABLE products closes the loop back into injection. This is the case people get wrong after learning parameters.

The answer is an allow-list map: user input selects from a menu your code authored, and the menu items are strings you wrote yourself.

var orderSorts = map[string]string{
	"price":       "price_cents",
	"newest":      "created_at DESC",
	"name":        "lower(name)",
}
var orderDirs = map[string]string{"asc": "ASC", "desc": "DESC"}

sortExpr, ok := orderSorts[req.Sort] // missing key -> safe default
if !ok {
	sortExpr = orderSorts["newest"]
}
dir, ok := orderDirs[strings.ToLower(req.Direction)]
if !ok {
	dir = "DESC"
}
q := fmt.Sprintf(`SELECT id, name, price_cents FROM products ORDER BY %s %s LIMIT $1`,
	sortExpr, dir) // %s slots only ever receive map values
rows, err := db.QueryContext(ctx, q, limit)

Notice what the map buys: the interpolated text comes exclusively from literal strings in your source. Input chooses which literal, never what it is. A regex validator ([a-z_]+) is weaker for the same job — it proves format, not meaning; an allow-list proves both. And for the parts that can take values — LIMIT $1, LIKE $1 — parameterize them like anything else.

Two adjacent hiding places worth naming. LIKE patterns: the value is still a parameter, but % and _ inside user input act as wildcards — a search for 100% matches everything unless you escape pattern characters before binding. Second-order injection: input that is safely parameterized on insert becomes dangerous when a later query builds SQL from the stored column — the classic site is a sort field or a tenant name saved to a table and concatenated into a query weeks later. The fix never changes: storage does not launder data; it is still attacker-influenced at every future query site and must be bound or allow-listed there.

A defense-in-depth checklist for review: no fmt.Sprintf anywhere near a query unless the verbs can only emit placeholders or map values; every request-sourced string reaches the database as an argument; dynamic identifiers pass through one shared allow-list helper; and the database account itself has only the privileges the app needs — least privilege turns a successful injection from full compromise into a bounded incident. Parameters are the correct default everywhere else.