Fix SQL injection in Go and net/http

1. Find every Query, QueryContext, QueryRow, Exec and Prepare call whose SQL is built with fmt.Sprintf, +, strings.Builder or text/template from request data: r.URL.Query(), r.PathValue(), form values, decoded JSON and anything the data layer they call receives.
2. Make the SQL a constant with placeholders and pass the values as the remaining arguments of QueryContext. The driver sends them apart from the SQL text, so a quote in a value stays a quote.
3. For the sort column and direction, look the request value up in a map[string]string of fixed SQL fragments and return a sentinel error for anything else; the handler checks it with errors.Is and answers 400.
4. Accept each query parameter once. r.URL.Query().Get returns the first of repeated values, so read the slice and reject a missing or repeated value with 400 before calling the database.
5. Log database errors with slog on the server and answer a generic 500; never write a driver error's text into the response.
6. Add regression tests with httptest.NewServer that send this card's payloads next to functional tests for names such as O'Brien and non-ASCII text.

The three implementations share one API and one test suite; only the code below changes. The tests load the one that IMPL names.

func (Vulnerable) OrdersForCustomer(ctx context.Context, db *sql.DB, customer, status string) ([]Order, error) {
	query := fmt.Sprintf(`
		SELECT o.id, o.status, o.total_cents, o.created_at
		FROM orders AS o
		JOIN customers AS c ON c.id = o.customer_id
		WHERE c.name = '%s' AND o.status = '%s'
		ORDER BY o.id`, customer, status)
	rows, err := db.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}
	return scanOrders(rows, false)
}

func (Fixed) OrdersForCustomer(ctx context.Context, db *sql.DB, customer, status string) ([]Order, error) {
	const query = `
		SELECT o.id, o.status, o.total_cents, o.created_at
		FROM orders AS o
		JOIN customers AS c ON c.id = o.customer_id
		WHERE c.name = ? AND o.status = ?
		ORDER BY o.id`
	rows, err := db.QueryContext(ctx, query, customer, status)
	if err != nil {
		return nil, err
	}
	return scanOrders(rows, false)
}

// A placeholder can only stand in for a value. Map the caller's choice onto
// fixed SQL fragments; anything else is ErrBadSort, which the handler turns into 400.
var (
	sortColumns = map[string]string{
		"created_at":  "o.created_at",
		"total_cents": "o.total_cents",
		"customer":    "c.name",
	}
	sortDirections = map[string]string{"asc": "ASC", "desc": "DESC"}
)

func (Fixed) SearchOrders(ctx context.Context, db *sql.DB, status, sort, direction string) ([]Order, error) {
	column, okColumn := sortColumns[sort]
	dir, okDir := sortDirections[direction]
	if !okColumn || !okDir {
		return nil, ErrBadSort
	}
	query := `
		SELECT o.id, c.name AS customer, o.status, o.total_cents, o.created_at
		FROM orders AS o
		JOIN customers AS c ON c.id = o.customer_id
		WHERE o.status = ?
		ORDER BY ` + column + ` ` + dir + `, o.id`
	rows, err := db.QueryContext(ctx, query, status)
	if err != nil {
		return nil, err
	}
	return scanOrders(rows, true)
}

func escapeSQLString(value string) string {
	value = strings.ReplaceAll(value, `\`, `\\`)
	return strings.ReplaceAll(value, `'`, `\'`)
}

func (Wrong) OrdersForCustomer(ctx context.Context, db *sql.DB, customer, status string) ([]Order, error) {
	query := fmt.Sprintf(`
		SELECT o.id, o.status, o.total_cents, o.created_at
		FROM orders AS o
		JOIN customers AS c ON c.id = o.customer_id
		WHERE c.name = '%s' AND o.status = '%s'
		ORDER BY o.id`, escapeSQLString(customer), escapeSQLString(status))
	rows, err := db.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}
	return scanOrders(rows, false)
}

The exploit tests send the attack and check that it fails; functional tests next to them keep ordinary requests working, so a fix that rejects everything fails too.

// Exploit: each payload must stay data and leave the query's meaning unchanged.

func TestExploitTautologyDoesNotReturnOtherCustomersOrders(t *testing.T) {
	srv := newServer(t)
	expectIDs(t, orderIDs(t, srv, "/orders", url.Values{"customer": {"' OR '1'='1"}, "status": {"shipped"}}))
}

func TestExploitUnionCannotReadAnotherCustomersOrders(t *testing.T) {
	srv := newServer(t)
	customer := "' UNION SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = 2 --"
	expectIDs(t, orderIDs(t, srv, "/orders", url.Values{"customer": {customer}, "status": {"shipped"}}))
}

func TestExploitCommentCannotCutOffTheStatusFilter(t *testing.T) {
	srv := newServer(t)
	expectIDs(t, orderIDs(t, srv, "/orders", url.Values{"customer": {"Alice Martin'--"}, "status": {"shipped"}}))
}

func TestExploitSortExpressionIsRejectedNotUsedAsAnOracle(t *testing.T) {
	srv := newServer(t)
	// Row order would reveal whether each customer's hidden email starts with "b".
	sort := "CASE WHEN substr(c.email, 1, 1) = char(98) THEN 0 ELSE 1 END"
	res := get(t, srv, "/admin/orders", url.Values{"status": {"shipped"}, "sort": {sort}, "dir": {"asc"}})
	if res.StatusCode != http.StatusBadRequest {
		t.Errorf("expected 400, got %d", res.StatusCode)
	}
}

func TestExploitDirectionCannotAddASecondSortExpression(t *testing.T) {
	srv := newServer(t)
	dir := "ASC, CASE WHEN substr(c.email, 1, 1) = char(98) THEN 0 ELSE 1 END"
	res := get(t, srv, "/admin/orders", url.Values{"status": {"shipped"}, "sort": {"created_at"}, "dir": {dir}})
	if res.StatusCode != http.StatusBadRequest {
		t.Errorf("expected 400, got %d", res.StatusCode)
	}
}

• Placeholder syntax belongs to the driver, not to database/sql, which passes the SQL through unchanged: ? for modernc.org/sqlite and go-sql-driver/mysql, $1, $2 for pgx and lib/pq.
• fmt.Sprintf with %q is not SQL quoting. It writes a Go string literal: double quotes, which SQLite and standard SQL read as an identifier, and backslash escapes, which they do not understand.
• A placeholder can only stand in for a value: ORDER BY ? binds a constant and SQLite silently sorts by nothing. Identifiers need the allowlist, and the allowlist should hold only columns the caller may see, because sorting by a hidden column such as email still leaks its order.
• database/sql does not expand a slice into an IN list. Build one placeholder per value with strings.Repeat and pass the values as arguments; never join the values into the SQL.
• LIKE: a bound % still matches every row. Escape % and _ and add an ESCAPE clause when the caller's text should match literally.
• ORMs have the same hole: GORM documents Order, Group, Select, Raw, Joins and string conditions in Where as methods that put their string argument into the SQL without escaping.
• The allowlisted ORDER BY is still string concatenation, so gosql-sqli reports it. Keep the fragments in one map next to the query so a reviewer can see that only constants reach the SQL.

1. Run the tests in all three modes: IMPL=fixed must pass; IMPL=vulnerable and IMPL=wrong must fail (node ../../run-all.mjs sql-injection/go-nethttp does this for you, in Docker if you do not have Go).
2. Run gosec ./... and semgrep --config r/go.lang.security.audit.database.string-formatted-query.string-formatted-query .; expect findings only in impl/vulnerable.go and impl/wrong.go.
3. Against your own endpoint, send customer=' OR '1'='1 and expect an empty list, and send a CASE expression as the sort and expect 400.

What each rule reported when it was run on this card's code, or what its source shows it covers where it was not run. A rule that exists for the weakness does not always catch it.

Download the project, install its dependencies once (this step needs the network), then run the tests against each implementation from the sql-injection/go-nethttpfolder.

go mod download
go build ./... tool

# Expected: every test passes
IMPL=fixed go tool gotestsum --junitfile reports/junit.xml -- -count=1 ./...

# Expected: an exploit test fails
IMPL=vulnerable go tool gotestsum --junitfile reports/junit.xml -- -count=1 ./...

# Expected: a test fails
IMPL=wrong go tool gotestsum --junitfile reports/junit.xml -- -count=1 ./...

Without Go installed, run the same commands with Docker in the image the card was verified in, pinned by its digest. The named volumes keep the downloaded dependencies and the build cache between runs, so the test runs need no network.

docker run --rm --init \
  -v "$PWD":/card -w /card \
  -v fix-go-gomod:/go/pkg/mod \
  -v fix-go-gocache:/root/.cache/go-build \
  golang:1.27.2-trixie@sha256:e58d6f83b3416618d8bcac2b3dde1b7f7e3c4a77d25e88637f8bbae81536c48d \
  sh -c 'go mod download && go build ./... tool'

# Expected: every test passes
docker run --rm --init --network=none \
  -v "$PWD":/card -w /card \
  -v fix-go-gomod:/go/pkg/mod \
  -v fix-go-gocache:/root/.cache/go-build \
  -e IMPL=fixed \
  golang:1.27.2-trixie@sha256:e58d6f83b3416618d8bcac2b3dde1b7f7e3c4a77d25e88637f8bbae81536c48d \
  sh -c 'go tool gotestsum --junitfile reports/junit.xml -- -count=1 ./...'

# Expected: an exploit test fails
docker run --rm --init --network=none \
  -v "$PWD":/card -w /card \
  -v fix-go-gomod:/go/pkg/mod \
  -v fix-go-gocache:/root/.cache/go-build \
  -e IMPL=vulnerable \
  golang:1.27.2-trixie@sha256:e58d6f83b3416618d8bcac2b3dde1b7f7e3c4a77d25e88637f8bbae81536c48d \
  sh -c 'go tool gotestsum --junitfile reports/junit.xml -- -count=1 ./...'

# Expected: a test fails
docker run --rm --init --network=none \
  -v "$PWD":/card -w /card \
  -v fix-go-gomod:/go/pkg/mod \
  -v fix-go-gocache:/root/.cache/go-build \
  -e IMPL=wrong \
  golang:1.27.2-trixie@sha256:e58d6f83b3416618d8bcac2b3dde1b7f7e3c4a77d25e88637f8bbae81536c48d \
  sh -c 'go tool gotestsum --junitfile reports/junit.xml -- -count=1 ./...'

Each run writes a JUnit report to reports/junit.xml. From the top folder of the download, node run-all.mjs sql-injection/go-nethttp runs all three modes and checks the expected results, in the container, so it needs Docker and Node.js but not the toolchain.

Download the project ZIP, 24 KB

Verified

Machine

macOS 27.0 (Darwin 27.0.0, arm64); tests in Docker Desktop 4.73.0 (Engine 29.4.3), linux/arm64

Toolchain

• go 1.27.2 (golang:1.27.2-trixie image); also run on 1.26.9 (golang:1.26.9-trixie)
• modernc.org/sqlite 1.60.1 (SQLite 3.53.4)
• gotestsum 1.13.0
• semgrep 1.180.0
• gosec 2.29.0
• govulncheck 1.8.0
• codeql CLI 2.27.1, codeql/go-queries 1.6.12

Matrix

fixed: 11 of 11 tests pass; vulnerable: 7 of 11 tests fail (5 exploit tests); wrong: 6 of 11 tests fail (4 exploit tests)

Test results

fixed 11/11 pass; vulnerable 7/11 fail (5 exploit tests); wrong 6/11 fail (4 exploit tests), with --network=none after install; the same on Go 1.26.9

Dependency freshness

modernc.org/sqlite 1.60.1 was published on 2026-09-29 and gotestsum 1.13.0 on 2025-09-11. gotestsum asks for golang.org/x/text 0.17.0, which GO-2026-5970 affects in a package gotestsum builds (x/text/unicode/norm), so go.mod raises it to 0.42.0 (2026-09-08). Go 1.27.2 (2026-10-08) is under seven days old but is a security release. govulncheck 1.8.0 found no vulnerabilities in the module or the standard library.

Versions and results hold for the toolchain above. The card's tests are the check: run them with your own versions before relying on the fix.

References

1. Go: Avoiding SQL injection risk
2. Go: Querying for data (database/sql)
3. Go standard library: database/sql DB.QueryContext
4. Go standard library: net/http ServeMux patterns
5. modernc.org/sqlite package documentation
6. GORM: Security (SQL injection methods)
7. OWASP SQL Injection Prevention Cheat Sheet
8. CodeQL Go model of database/sql (query sinks)
9. gosec 2.29.0: rule documentation

Attack patterns:

• CAPEC-66 at MITRE