Prepared statements and boring SQL safety

Suppose bfstore, the fictional organisation used throughout this series, is implementing its catalog repository in Go and MySQL.

The catalog service needs to search for products by name.

The first implementation looks pleasantly direct:

func FindProducts(ctx context.Context, search string) ([]Product, error) {
	query := fmt.Sprintf(
		"SELECT id, name, price_pence FROM products WHERE name = '%s'",
		search,
	)

	rows, err := db.QueryContext(ctx, query)
	if err != nil {
		return nil, fmt.Errorf("query products: %w", err)
	}
	defer rows.Close()

	return scanProducts(rows)
}

For ordinary input:

gopher mug

the generated SQL becomes:

SELECT id, name, price_pence
FROM products
WHERE name = 'gopher mug'

The code appears to work.

The danger is that search is not being treated as data. It is inserted into the SQL program before that program reaches the database.

A malicious or simply unexpected value can close the string literal and introduce new syntax:

gopher mug' OR '1' = '1

The database may receive:

SELECT id, name, price_pence
FROM products
WHERE name = 'gopher mug' OR '1' = '1'

The input has changed the meaning of the query.

It did not merely supply a product name.

It became part of the program.

The safe version keeps the SQL structure fixed:

func FindProducts(ctx context.Context, search string) ([]Product, error) {
	const query = `
		SELECT id, name, price_pence
		FROM products
		WHERE name = ?
	`

	rows, err := db.QueryContext(ctx, query, search)
	if err != nil {
		return nil, fmt.Errorf("query products: %w", err)
	}
	defer rows.Close()

	return scanProducts(rows)
}

Now the application supplies two distinct things:

SQL STRUCTURE

SELECT id, name, price_pence
FROM products
WHERE name = ?


PARAMETER VALUE

gopher mug' OR '1' = '1

The database access layer treats the complete parameter as one value. The apostrophes and SQL keywords inside it do not gain authority to rewrite the statement.

The examples in this article use Go’s database/sql package with go-sql-driver/mysql and MySQL-style ? placeholders. Placeholder syntax and some execution details differ between database drivers.

Safe SQL begins by deciding the program before untrusted data enters the room.

That is a wonderfully boring property.

Database code benefits from as much of it as possible.

SQL injection is a boundary failure

SQL is a programming language interpreted by the database.

The application sends an instruction such as:

SELECT id, name
FROM products
WHERE id = ?

The product ID belongs to the data handled by that instruction.

SQL STRUCTURE

SELECT
FROM
WHERE
=
?


DATA

product ID

Injection becomes possible when the application combines those two categories into one undifferentiated string.

UNTRUSTED VALUE
      │
      ▼
STRING CONCATENATION
      │
      ▼
NEW SQL PROGRAM
      │
      ▼
DATABASE INTERPRETER

An untrusted value can arrive from:

an HTTP query parameter
a gRPC request
a message-broker event
a CSV import
a command-line tool
another internal service
an administration interface
previously stored database content
a compromised integration

The source may be another bfstore service on a private network.

That does not make its contents safe to interpret as executable SQL.

INTERNAL SOURCE

does not equal

SAFE TO INTERPRET AS CODE

OWASP’s primary recommendation is to stop constructing dynamic SQL through string concatenation and to use parameter binding. The database API then retains a boundary between statement structure and data values.

The application should not try to recreate that guarantee through careful punctuation.

Parameterised execution is not one implementation mechanism

The phrases parameterised query and prepared statement are often used as though they mean exactly the same thing.

They are related, but there are three distinct layers.

PARAMETERISED API

SQL text and values are supplied
through separate Go arguments

DRIVER EXECUTION STRATEGY

the driver may use the server's
prepared-statement protocol

or safely encode parameters
into one statement

REUSABLE sql.Stmt

the application explicitly prepares
a statement for repeated use

This call uses the parameter API:

row := db.QueryRowContext(
	ctx,
	`SELECT id, status FROM orders WHERE id = ?`,
	orderID,
)

The SQL text and orderID are supplied separately. That is the application-level safety property.

With the default behaviour of go-sql-driver/mysql, an argument-bearing query normally uses prepare, execute and close operations. If the connection string enables interpolateParams=true, the driver instead safely encodes the values into one SQL statement before sending it, reducing network round trips.

The application should not depend on one wire-level strategy for its injection defence.

The durable rule is:

Every variable value must travel through the driver’s parameter API.

Whether the driver preserves that boundary through MySQL’s prepared-statement protocol or safe driver-side encoding is an execution detail that must be understood separately.

Reusable statements are a lifecycle decision

An explicit reusable statement looks like:

stmt, err := db.PrepareContext(
	ctx,
	`SELECT id, status FROM orders WHERE id = ?`,
)
if err != nil {
	return fmt.Errorf("prepare order lookup: %w", err)
}
defer stmt.Close()

Then later:

row := stmt.QueryRowContext(ctx, orderID)

A statement prepared on sql.DB is safe for concurrent use. database/sql manages it across the connection pool and prepares it on additional underlying connections when necessary.

A statement prepared on sql.Tx or sql.Conn is bound to that transaction or reserved connection.

API	Behaviour
`DB.QueryContext` with arguments	Safe parameter API; the driver chooses the execution strategy
`DB.PrepareContext`	Reusable through the connection pool
`Tx.PrepareContext`	Bound to the transaction’s connection
`Conn.PrepareContext`	Bound to the reserved connection

A reusable sql.Stmt is normally prepared during repository or application initialisation and closed during shutdown. Preparing and closing the same statement inside every request may add lifecycle complexity without meaningful reuse.

Explicit preparation can reduce repeated parsing overhead for frequently executed operations.

Performance is a useful passenger.

It is not the reason parameter binding is mandatory.

The placeholder must not be quoted

A placeholder represents a complete value.

For MySQL, the usual marker is:

This is correct:

SELECT id, name
FROM products
WHERE name = ?

This is not:

SELECT id, name
FROM products
WHERE name = '?'

In the second statement, the question mark is inside a SQL string literal. It represents the literal character ?, not a parameter position.

The driver is responsible for sending the actual value with the appropriate type and representation:

const query = `
	SELECT id, name
	FROM products
	WHERE name = ?
`

row := db.QueryRowContext(ctx, query, productName)

The same mechanism handles ordinary values of several types:

_, err := db.ExecContext(
	ctx,
	`
		INSERT INTO products (
			id,
			name,
			price_pence,
			active,
			created_at
		)
		VALUES (?, ?, ?, ?, ?)
	`,
	product.ID,
	product.Name,
	product.PricePence,
	product.Active,
	product.CreatedAt,
)

The values remain values whether they contain apostrophes, Unicode, line breaks, punctuation or convincing-looking SQL.

The application should validate whether those values make sense for the domain.

It should not manually convert them into SQL literals.

Escaping is not the everyday defence

An alternative approach tries to make string construction safe by escaping input:

safeName := escapeSQL(productName)

query := fmt.Sprintf(
	"SELECT id FROM products WHERE name = '%s'",
	safeName,
)

Escaping remains fragile because correctness depends on the dialect, character set, SQL mode, literal context and driver behaviour. The application also has to choose the right escaping rule at every call site.

Parameter binding removes that responsibility for values.

A useful hierarchy is:

1. FIXED SQL STRUCTURE

2. PARAMETER BINDING FOR VALUES

3. CONTROLLED APPLICATION CHOICES
   WHERE STRUCTURE MUST VARY

4. CONTEXTUAL ESCAPING ONLY
   WHEN A SAFE PARAMETER API
   TRULY CANNOT BE USED

The first two should handle nearly all bfstore runtime queries.

Escaping belongs in specialised compatibility code and carefully reviewed infrastructure libraries. It should not be the normal application programming model.

Parameters represent values, not SQL grammar

A parameter can stand where SQL expects a data value.

It cannot normally stand in for:

table names
column names
SQL keywords
operators
sort direction

This does not mean what its author hopes:

SELECT id, name, price_pence
FROM products
ORDER BY ? ?

with:

price_pence

DESC

MySQL parameter markers are used where values belong, not where identifiers or keywords belong.

VALUE

'pink gopher mug'
8042
true
2026-06-21


STRUCTURE

products
price_pence
DESC
>=
JOIN

Parameter binding protects values.

Structural variation must be designed in application code.

This is where an otherwise parameterised query can quietly reopen the injection boundary.

Controlled structural variation

Suppose the catalog API supports:

sort=name

sort=price

sort=newest

The unsafe version inserts the supplied value directly:

query := fmt.Sprintf(`
	SELECT id, name, price_pence
	FROM products
	ORDER BY %s
`, requestedSort)

A safer design maps public choices to fixed SQL fragments.

type ProductSort string

const (
	ProductSortName       ProductSort = "name"
	ProductSortPrice      ProductSort = "price"
	ProductSortNewest     ProductSort = "newest"
	ProductSortPopularity ProductSort = "popularity"
)

func productOrderBy(sort ProductSort) (string, error) {
	switch sort {
	case ProductSortName:
		return "name ASC, id ASC", nil
	case ProductSortPrice:
		return "price_pence ASC, id ASC", nil
	case ProductSortNewest:
		return "created_at DESC, id DESC", nil
	case ProductSortPopularity:
		return "purchase_count DESC, id ASC", nil
	default:
		return "", fmt.Errorf("unsupported product sort %q", sort)
	}
}

The query then combines only the fixed fragment returned by controlled code:

orderBy, err := productOrderBy(requestedSort)
if err != nil {
	return nil, err
}

query := `
	SELECT id, name, price_pence
	FROM products
	WHERE active = ?
	ORDER BY ` + orderBy + `
	LIMIT ?
`

rows, err := db.QueryContext(ctx, query, true, limit)

A string is still being combined into the SQL.

The difference is its origin.

UNSAFE

request value
    → SQL fragment


CONTROLLED

request value
    → enum decision
    → fixed code-owned fragment

The same rule applies to sort direction. Map the public choice to the fixed fragments ASC or DESC; never append the request value itself.

The caller may choose between behaviours the application already owns. It may not supply arbitrary SQL after ORDER BY.

Dynamic identifiers deserve suspicion

Suppose an administration report accepts:

environment=production

and builds:

query := fmt.Sprintf(
	"SELECT count(*) FROM %s_orders",
	environment,
)

This is unsafe if the request controls the identifier.

It may also indicate that the schema has encoded a business dimension through table names.

A more durable model may be:

SELECT COUNT(*)
FROM orders
WHERE environment = ?

The variable has moved from SQL structure into data, where it can be parameterised naturally.

When dynamic identifiers are genuinely required, map a closed application enum to fixed, code-owned identifier fragments. Then review why the schema needs that variation.

A placeholder’s refusal to become a table name is often useful design feedback.

Variable-shape queries still bind every value

A basket request may need several product records:

bf-product-101
bf-product-204
bf-product-811

A single placeholder does not become a list.

This is incorrect:

SELECT id, name
FROM products
WHERE id IN (?)

with one comma-separated string argument. The database sees one value.

The safe approach creates one placeholder for each value:

func placeholders(count int) (string, error) {
	if count <= 0 {
		return "", errors.New("placeholder count must be positive")
	}

	return strings.TrimSuffix(
		strings.Repeat("?,", count),
		",",
	), nil
}

The repository builds only punctuation and placeholder positions:

const maxProductIDsPerQuery = 500

func (r *ProductRepository) FindByIDs(
	ctx context.Context,
	ids []ProductID,
) ([]Product, error) {
	if len(ids) == 0 {
		return []Product{}, nil
	}

	if len(ids) > maxProductIDsPerQuery {
		return nil, fmt.Errorf(
			"too many product IDs: maximum is %d",
			maxProductIDsPerQuery,
		)
	}

	markers, err := placeholders(len(ids))
	if err != nil {
		return nil, err
	}

	query := `
		SELECT id, name, price_pence
		FROM products
		WHERE id IN (` + markers + `)
	`

	args := make([]any, 0, len(ids))
	for _, id := range ids {
		args = append(args, id.String())
	}

	rows, err := r.db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("query products by IDs: %w", err)
	}
	defer rows.Close()

	return scanProducts(rows)
}

The generated structure may contain:

?,?,?

The actual IDs remain argument values.

The empty-list case is handled explicitly rather than generating invalid SQL such as:

WHERE id IN ()

Bulk inserts use the same principle

A bulk insert varies the number of value groups:

func buildProductInsert(products []Product) (string, []any, error) {
	if len(products) == 0 {
		return "", nil, errors.New("no products supplied")
	}

	const maxProductsPerInsert = 500
	if len(products) > maxProductsPerInsert {
		return "", nil, fmt.Errorf(
			"too many products: maximum is %d",
			maxProductsPerInsert,
		)
	}

	var query strings.Builder

	query.WriteString(`
		INSERT INTO products (
			id,
			name,
			price_pence
		)
		VALUES
	`)

	args := make([]any, 0, len(products)*3)

	for i, product := range products {
		if i > 0 {
			query.WriteString(",")
		}

		query.WriteString("(?, ?, ?)")

		args = append(
			args,
			product.ID.String(),
			product.Name,
			product.PricePence,
		)
	}

	return query.String(), args, nil
}

The program generates commas, parentheses and placeholder positions.

It does not embed product data into SQL text.

Batch limits solve a separate problem:

PLACEHOLDER GENERATION

prevents values becoming syntax

BATCH LIMITS

prevent safe syntax becoming
an unsafe amount of work

Large legitimate workloads should be split into controlled batches or use a purpose-designed ingestion mechanism. This protects statement size, memory allocation, parse time and packet limits.

`LIKE` patterns are data with operator semantics

Suppose the catalog performs substring search:

pattern := "%" + search + "%"

rows, err := db.QueryContext(
	ctx,
	`
		SELECT id, name
		FROM products
		WHERE name LIKE ?
	`,
	pattern,
)

This is injection-safe.

A search value containing an apostrophe cannot rewrite the query.

However, LIKE assigns special meaning to:

%

_

A user searching for:

100%

may unintentionally request every string beginning with 100.

That is not SQL injection. The value has remained data.

It is a search-semantics problem.

If % and _ should be literal, define an escape character that does not depend on MySQL’s backslash string rules:

func escapeLike(value string) string {
	replacer := strings.NewReplacer(
		"!", "!!",
		"%", "!%",
		"_", "!_",
	)

	return replacer.Replace(value)
}

The query declares that escape character:

SELECT id, name
FROM products
WHERE name LIKE ? ESCAPE '!'

The final pattern remains a parameter:

pattern := "%" + escapeLike(search) + "%"

Keep the distinction clear:

SQL INJECTION SAFETY

Can the value alter SQL structure?

SEARCH SEMANTICS

What does the value mean
inside this operator?

Parameterisation solves the first.

The application still defines the second.

Validation and authorisation answer different questions

Suppose an API accepts a page size:

rows, err := db.QueryContext(
	ctx,
	`
		SELECT id, name
		FROM products
		ORDER BY id
		LIMIT ?
	`,
	limit,
)

MySQL supports parameter markers in LIMIT positions.

The value still needs validation:

func validatePageSize(size int) error {
	if size < 1 || size > 100 {
		return fmt.Errorf("page size must be between 1 and 100")
	}

	return nil
}

Parameterisation answers:

Can this value become SQL syntax?

Validation answers:

Is this value permitted
by the application contract?

A parameterised limit of 100000000 is not injection. It may still create an expensive query or denial-of-service opportunity.

Authorisation is another boundary.

This query is safely parameterised:

SELECT id, customer_id, status, total_pence
FROM orders
WHERE id = ?

A caller can still supply another customer’s valid order ID.

The ownership rule should appear in the operation:

SELECT id, customer_id, status, total_pence
FROM orders
WHERE id = ?
  AND customer_id = ?

row := db.QueryRowContext(
	ctx,
	query,
	orderID,
	authenticatedCustomerID,
)

The two questions remain separate:

INJECTION

Can orderID rewrite the SQL?

AUTHORISATION

May this customer read
the selected order?

A perfectly parameterised query can still implement an insecure direct object reference.

For bfstore, repository signatures should make the business scope visible:

FindCustomerOrder(
	ctx context.Context,
	customerID CustomerID,
	orderID OrderID,
) (Order, error)

rather than asking every caller to remember a separate ownership check.

The repository owns the SQL contract

A repository should expose domain operations rather than arbitrary SQL construction:

type ProductRepository interface {
	FindByID(
		ctx context.Context,
		id ProductID,
	) (Product, error)

	FindActive(
		ctx context.Context,
		filter ProductFilter,
	) ([]Product, error)

	Create(
		ctx context.Context,
		product Product,
	) error

	UpdatePrice(
		ctx context.Context,
		id ProductID,
		price Money,
	) error
}

The filter uses controlled application types:

type ProductFilter struct {
	Query     string
	Category  *CategoryID
	Sort      ProductSort
	PageSize  int
	PageAfter *ProductID
}

The boundary is:

CALLER OWNS

product ID
search value
category
requested sort choice
page size


REPOSITORY OWNS

table names
column names
SQL operators
join structure
sort expressions
parameter order
projections
transaction use

That gives identifier control, validation, pagination, authorisation and query shape one well-lit home.

It also provides a practical route towards database least privilege.

Least privilege should match the repository

Assume an injection vulnerability survives somewhere.

Its impact depends partly on what the runtime database identity can do.

A catalog read path should not connect as:

root

The runtime account should not ordinarily be able to:

create users
grant privileges
drop schemas
modify migration history
access another service’s database
read administrative tables
write server-side files

A bfstore service should use distinct identities for distinct jobs:

BOOTSTRAP ACCOUNT

creates schemas and initial accounts
short-lived operational use

MIGRATOR ACCOUNT

applies approved schema migrations
DDL permissions where required

RUNTIME ACCOUNT

only the DML operations
required by the service

READ-ONLY ACCOUNT

SELECT on approved reporting objects

A complete illustrative MySQL role flow is:

CREATE ROLE 'catalog_runtime';

GRANT
	SELECT,
	INSERT,
	UPDATE,
	DELETE
ON bfstore_catalog.*
TO 'catalog_runtime';

GRANT 'catalog_runtime'
TO 'catalog_app'@'%';

SET DEFAULT ROLE 'catalog_runtime'
TO 'catalog_app'@'%';

A real deployment should narrow the allowed host and privileges further.

Creating a role does not automatically make it active for an application account. The role must be granted and made active through a default role, session activation or another deliberate configuration.

The repository and database role should tell the same story:

REPOSITORY METHODS
        │
        ▼
REQUIRED SQL OPERATIONS
        │
        ▼
DATABASE ROLE

A mismatch is useful evidence.

If the runtime role requires schema-creation privileges, migration work may be using the wrong identity.

If the catalog role requires order-table access, a service boundary may be leaking.

One service should not query another service’s tables

The catalog service owns catalog data.

The order service owns orders.

The payment service owns payment state.

A convenient cross-schema join can introduce:

broader credentials
coupled migrations
unclear ownership
hidden runtime dependencies
greater injection impact

A safer architecture may use:

a service API
a domain event
a reporting projection
an analytics pipeline
an explicitly owned read model

Parameter binding protects the syntax boundary.

Service-owned databases protect the ownership boundary.

Transactions preserve integrity, not query safety

Creating an order may require:

inserting the order
inserting its order lines
recording an outbox event

Those changes should succeed or fail together:

func (r *OrderRepository) Create(
	ctx context.Context,
	order Order,
	event OutboxEvent,
) error {
	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("begin order transaction: %w", err)
	}
	defer tx.Rollback()

	if err := insertOrder(ctx, tx, order); err != nil {
		return err
	}

	if err := insertOrderLines(ctx, tx, order.Lines); err != nil {
		return err
	}

	if err := insertOutboxEvent(ctx, tx, event); err != nil {
		return err
	}

	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit order transaction: %w", err)
	}

	return nil
}

Each statement must still bind its values:

_, err := tx.ExecContext(
	ctx,
	`
		INSERT INTO orders (
			id,
			customer_id,
			status,
			total_pence
		)
		VALUES (?, ?, ?, ?)
	`,
	order.ID,
	order.CustomerID,
	order.Status,
	order.TotalPence,
)

A transaction answers:

Should these changes become visible
as one atomic unit?

Parameterisation answers:

Can these values alter
the SQL program?

The context supplied to BeginTx remains associated with the transaction until commit or rollback. If that context is cancelled, database/sql rolls the transaction back and Commit returns an error.

Keep all transactional operations on sql.Tx. A call made directly through sql.DB may use another connection and execute outside the transaction.

Context limits work, but cancellation has a cost

A parameterised query can still wait on:

a lock
an overloaded database
a poor query plan
a large result set
a failed network path

Repository operations should accept context.Context and use context-aware methods:

QueryContext
QueryRowContext
ExecContext
PrepareContext
BeginTx

A caller can establish an operation-specific budget:

const findProductTimeout = 750 * time.Millisecond

ctx, cancel := context.WithTimeout(ctx, findProductTimeout)
defer cancel()

product, err := repository.FindByID(ctx, productID)

The duration is illustrative. A real deadline should reflect the service budget, expected database latency, retry policy and connection-pool behaviour.

With go-sql-driver/mysql, cancellation or timeout before the result is received causes the underlying connection to be closed. The pool may then need to create a replacement.

Cancellation is therefore not free bookkeeping.

PARAMETERS

protect query structure

CONTEXT

limits how long work
may occupy resources

QUERY DESIGN

limits how much work
the database must perform

POOL SETTINGS

limit how many database
connections may be occupied

An arbitrarily tiny timeout copied into every repository method can turn latency into connection churn.

Result handling and projections should be explicit

A multi-row query should:

check the query error
close the returned rows
scan every expected column
check rows.Err() after iteration

func scanProducts(rows *sql.Rows) ([]Product, error) {
	products := make([]Product, 0)

	for rows.Next() {
		var product Product

		if err := rows.Scan(
			&product.ID,
			&product.Name,
			&product.PricePence,
		); err != nil {
			return nil, fmt.Errorf("scan product: %w", err)
		}

		products = append(products, product)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate products: %w", err)
	}

	return products, nil
}

A single-row lookup reports its query and scan errors through Scan:

err := db.QueryRowContext(
	ctx,
	query,
	productID,
).Scan(
	&product.ID,
	&product.Name,
	&product.PricePence,
)

Translate sql.ErrNoRows into a domain result rather than exposing a storage detail:

if errors.Is(err, sql.ErrNoRows) {
	return Product{}, ErrProductNotFound
}

Explicit projections also matter.

This is injection-safe:

SELECT *
FROM products
WHERE id = ?

It is still weaker than:

SELECT
	id,
	name,
	description,
	price_pence,
	active
FROM products
WHERE id = ?

The explicit list shows which data the operation needs and prevents a future sensitive column from entering the result merely because it was added to the table.

Parameterisation protects the parser.

Explicit projections protect the data contract.

Stored procedures, ORMs and builders keep the same boundary

A stored procedure can expose a parameterised interface:

CALL get_customer_order(?, ?)

That is safe only if the procedure itself uses fixed or safely parameterised SQL.

APPLICATION

passes value safely
      │
      ▼
STORED PROCEDURE

concatenates it into SQL
      │
      ▼
INJECTION RETURNS

The same principle applies to:

ORM query languages
query builders
database functions
reporting tools
migration frameworks

An ORM may provide a safe expression API:

query.Where("customer_id = ?", customerID)

and an unsafe structural escape hatch:

query.Raw(userSuppliedSQL)

A builder may bind values automatically while accepting an uncontrolled identifier:

builder.OrderBy(requestedSort)

The review questions remain:

Where did this SQL fragment originate?

Does this API bind a value
or insert a string?

Can the caller choose identifiers?

Does a raw-query path exist?

Does the generated SQL preserve
the same authorisation scope?

The rule should not be:

ORM equals safe.

It should be:

Every value is bound.

Every structural choice
comes from controlled code.

Query logging should not expose parameter values

Operational logs should identify the database operation without recording:

passwords
access tokens
payment data
personal information
complete order contents
private customer attributes

This is risky:

logger.Info(
	"executing database operation",
	"query", query,
	"args", args,
)

The SQL may be harmless to record in some environments, but args can contain the sensitive material.

A custom debug helper that reconstructs the statement is worse:

logger.Info(
	"executing SQL",
	"rendered_query", interpolateForDebug(query, args),
)

It recreates a giant string containing both program and data, and may misrepresent the actual driver encoding.

Prefer stable operation evidence:

logger.Info(
	"database operation",
	"operation", "catalog.find_products",
	"product_count", len(ids),
	"duration_ms", elapsed.Milliseconds(),
)

For failures:

logger.Error(
	"database operation failed",
	"operation", "orders.create",
	"error", err,
)

Useful evidence can include:

operation name
duration
row count
error class
trace ID
database instance
transaction outcome

Complete parameter values should not enter ordinary logs by default.

Security sometimes means preventing the database from interpreting data as code.

It also means preventing the logging system from keeping that data as a souvenir.

Tests should prove that values remain values

A repository test should not attempt to catalogue every historical payload.

It should prove the architectural property:

Values travel through parameters
and remain literal values.

Useful fixtures include:

O'Reilly gopher mug

"quoted" product

100% cotton

underscore_product

'; DROP TABLE products; --

gopher' OR '1'='1

A complete integration test should arrange its own data:

func TestRepositoryFindByNameTreatsInputAsValue(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	hostileName := "gopher' OR '1'='1"
	controlName := "ordinary gopher"

	hostile := Product{
		ID:   NewProductID(),
		Name: hostileName,
	}

	control := Product{
		ID:   NewProductID(),
		Name: controlName,
	}

	require.NoError(t, repository.Create(ctx, hostile))
	require.NoError(t, repository.Create(ctx, control))

	t.Cleanup(func() {
		_ = repository.Delete(ctx, hostile.ID)
		_ = repository.Delete(ctx, control.ID)
	})

	products, err := repository.FindByName(ctx, hostileName)
	require.NoError(t, err)

	require.Len(t, products, 1)
	assert.Equal(t, hostile.ID, products[0].ID)
	assert.Equal(t, hostileName, products[0].Name)
}

The test should prove that the exact value survives, unrelated rows are not returned and authorisation remains intact.

Multi-statement execution is only one possible injection outcome. The common Go MySQL driver disables multiple statements by default, but unsafe input can still alter one statement’s predicate, projection, join or operation.

A mock can verify that SQL and arguments were supplied separately. An integration test proves how the driver and MySQL behave together.

Static review searches also help:

fmt.Sprintf("SELECT

fmt.Sprintf("INSERT

fmt.Sprintf("UPDATE

fmt.Sprintf("DELETE

+ request.Sort

+ userInput

.Raw(

A text search is not a complete security scanner.

It is an excellent detector of database code beginning to develop theatrical punctuation.

The review table I use

Area	Question
Structure	Is SQL grammar controlled by application code?
Values	Does every variable value travel through the driver’s parameter API?
Identifiers	Do table, column and sort choices come from a closed mapping?
Variable shape	Are placeholder lists generated without embedding values and with a maximum size?
Semantics	Are `LIKE`, ranges and pagination rules defined separately from injection safety?
Validation	Are domain and workload limits enforced?
Authorisation	Does the operation constrain data by actor, tenant or ownership?
Privilege	Can the runtime identity perform only the required operations?
Ownership	Does the service access only its own schema and approved projections?
Transactions	Do related changes use one `sql.Tx` and one context?
Cancellation	Do deadlines reflect real service budgets and driver behaviour?
Results	Are rows closed, scans checked and iteration errors handled?
Logging	Are parameter values excluded from ordinary operational logs?
Testing	Do integration tests round-trip hostile-looking literal values?
Abstractions	Do procedures, ORMs and builders preserve the same boundary?

This turns SQL safety into a repeatable engineering habit rather than a code-review mood.

The mental model I am keeping

My earlier model was:

SQL SAFETY

escape apostrophes

validate the form

hope the query remains intact

The stronger model is:

                       DATABASE OPERATION
                                │
                ┌───────────────┴───────────────┐
                │                               │
                ▼                               ▼
           SQL STRUCTURE                    DATA VALUES
                │                               │
                ▼                               ▼
        CONTROLLED APPLICATION             PARAMETERS
               CODE                             │
                │                               │
        ┌───────┼────────┐                      │
        │       │        │                      │
        ▼       ▼        ▼                      ▼
      TABLE   COLUMN   OPERATOR          DRIVER PARAMETER API
      NAMES   NAMES    AND ORDER                │
        │       │        │                      │
        └───────┼────────┘                      │
                │                               │
                └───────────────┬───────────────┘
                                ▼
                         DATABASE ENGINE
                                │
          ┌─────────────────────┼─────────────────────┐
          │                     │                     │
          ▼                     ▼                     ▼
    AUTHORISATION        LEAST PRIVILEGE        TRANSACTION
          │                     │                     │
          └─────────────────────┼─────────────────────┘
                                ▼
                     BORING DATABASE SAFETY

The parameter API answers:

Can this value alter
the SQL structure?

Controlled identifiers answer:

Which application-owned
query structures may vary?

Validation answers:

Is this value acceptable
to the domain and workload budget?

Authorisation answers:

May this actor access
the selected data?

Least privilege answers:

What is the greatest damage
this database identity can cause?

Transactions answer:

Which changes must succeed
or fail together?

Contexts answer:

How long may the operation
occupy resources?

Explicit projections answer:

Which data did this operation
intend to retrieve?

And tests answer:

Do hostile-looking values
remain ordinary values?

Prepared statements are not a complete database security programme. They implement one essential boundary:

The caller may supply data. The caller may not edit the program that interprets it.

A product name may contain an apostrophe.

A search may contain a percentage sign.

A customer may paste something that resembles a DROP TABLE statement.

The database should receive all of it with the emotional response of a filing cabinet.

Dynamic identifiers come from controlled code. Authorisation remains visible, runtime accounts stay narrow, transactions preserve integrity, deadlines limit abandoned work and tests prove that strange values remain harmlessly strange.

This is not glamorous security.

Nobody unveils a conference keynote called We Passed the String as an Argument.

That is precisely why I like it.

The safest query is rarely the cleverest one.

It is the one whose structure was decided before untrusted data entered the room.