Filtering and Sorting in Go APIs Without SQL Injection
Build filter and sort parameters for a Go API from an allowlist: bound values, mapped columns, escaped LIKE wildcards and a blind injection through ORDER BY.
Filter and sort parameters are the part of a list endpoint where the database meets raw user text. ?status=open&sort=-price looks harmless, and it is the most common place a Go service builds SQL by concatenation, because the usual defence, placeholders, does not cover half of what these parameters control. A value can be bound. A column name in an ORDER BY cannot.
Tested with Go 1.25.6 and modernc.org/sqlite v1.50.0, macOS on Apple Silicon (arm64)
This article builds the parameter parsing for a list endpoint in a way that cannot be injected, because nothing from the request ever becomes SQL text: field names, operators and sort keys go through an allowlist, and values are bound. It then shows, with a running example, the attack the usual advice misses. It sits beside pagination, which needs the stable ordering built here, and the JSON API cornerstone. Everything ran on Go 1.25.6 against SQLite through modernc.org/sqlite v1.50.0.
The rule
There are two kinds of thing in a query, and they need different treatment.
Values (open, 4900, pro) are data. They go into the query as placeholders, WHERE status = ?, with the value passed separately to db.Query. The driver sends them out of band, so no value can change the structure of the statement. This is the advice everyone knows, and it is right.
Identifiers and keywords (price_cents, DESC, <=, LIKE) are structure. A placeholder cannot stand in for them, so the temptation is to paste them in. If the text came from the request, you have handed the client a way to write SQL.
The only safe approach is that structure never comes from the request. It comes from a table in your code, keyed by what the client is allowed to say. The client asks for price; your table says price means the column price_cents; the SQL contains price_cents. A client who asks for price; DROP TABLE items is asking for a key that does not exist, and gets a 400.
The query builder
package main
import (
"errors"
"fmt"
"net/url"
"strings"
)
// Column describes one field a client may filter or sort on. Anything not listed here does not exist to the client.
type Column struct {
SQL string // the real column, never taken from the request
Ops []string // allowed filter operators
Sortable bool
}
var columns = map[string]Column{
"status": {SQL: "status", Ops: []string{"eq", "in"}, Sortable: true},
"price": {SQL: "price_cents", Ops: []string{"eq", "gte", "lte"}, Sortable: true},
"name": {SQL: "name", Ops: []string{"eq", "contains"}, Sortable: true},
}
var sqlOps = map[string]string{"eq": "=", "gte": ">=", "lte": "<="}
type Query struct {
Where string
Order string
Args []any
}
var ErrBadQuery = errors.New("invalid query")
func bad(format string, a ...any) error {
return fmt.Errorf("%w: "+format, append([]any{ErrBadQuery}, a...)...)
}
// Build turns query-string parameters into SQL text built only from the allowlist plus placeholders for values.
// Filters look like status=open, price[gte]=1000, name[contains]=pro. Sort looks like sort=-price,name.
func Build(q url.Values) (Query, error) {
var out Query
var wheres []string
for _, key := range sortedKeys(q) {
if key == "sort" || key == "limit" || key == "cursor" {
continue
}
field, op, _ := strings.Cut(strings.TrimSuffix(key, "]"), "[")
if op == "" {
op = "eq"
}
col, ok := columns[field]
if !ok {
return out, bad("unknown filter %q", field)
}
if !contains(col.Ops, op) && !(op == "eq" && contains(col.Ops, "in")) {
return out, bad("%s does not support %q", field, op)
}
for _, raw := range q[key] {
switch {
case op == "contains":
wheres = append(wheres, col.SQL+` LIKE ? ESCAPE '\'`)
out.Args = append(out.Args, "%"+likeEscape(raw)+"%")
case contains(col.Ops, "in") && strings.Contains(raw, ","):
vals := strings.Split(raw, ",")
wheres = append(wheres, col.SQL+" IN ("+strings.TrimSuffix(strings.Repeat("?,", len(vals)), ",")+")")
for _, v := range vals {
out.Args = append(out.Args, v)
}
default:
wheres = append(wheres, col.SQL+" "+sqlOps[op]+" ?")
out.Args = append(out.Args, raw)
}
}
}
if len(wheres) > 0 {
out.Where = " WHERE " + strings.Join(wheres, " AND ")
}
order, err := buildOrder(q.Get("sort"))
out.Order = order
return out, err
}
func buildOrder(sort string) (string, error) {
var parts []string
if sort != "" {
for _, f := range strings.Split(sort, ",") {
dir, name := "ASC", f
if strings.HasPrefix(f, "-") {
dir, name = "DESC", f[1:]
}
col, ok := columns[name]
if !ok || !col.Sortable {
return "", bad("cannot sort by %q", f)
}
parts = append(parts, col.SQL+" "+dir)
}
}
parts = append(parts, "id ASC") // a total order, so pages are stable
return " ORDER BY " + strings.Join(parts, ", "), nil
}
// likeEscape neutralises the LIKE wildcards in user text.
func likeEscape(s string) string {
return strings.NewReplacer(`\`, `\\`, `%`, `\%`, `_`, `\_`).Replace(s)
}
func contains(list []string, s string) bool {
for _, x := range list {
if x == s {
return true
}
}
return false
}
func sortedKeys(q url.Values) []string {
keys := make([]string, 0, len(q))
for k := range q {
keys = append(keys, k)
}
for i := 1; i < len(keys); i++ {
for j := i; j > 0 && keys[j] < keys[j-1]; j-- {
keys[j], keys[j-1] = keys[j-1], keys[j]
}
}
return keys
}The columns map is the allowlist and the public contract in one place. Each entry names the real column, the operators allowed on it and whether it can be sorted. The API field price maps to price_cents, which also means the schema can change without breaking clients. A column that is not in the map, such as owner_id or a hashed password, cannot be filtered or sorted on, even by accident, which also stops clients using a sort order to learn about hidden data.
Build reads the parameters in sorted key order, so the generated SQL is deterministic and cacheable. For each filter key it splits price[gte] into a field and an operator, looks the field up, checks the operator is allowed, and appends a WHERE fragment made only of the mapped column, an operator from a second small map, and ?. The values go to Args. Three shapes are supported: equality, ranges with gte and lte, and a comma list that becomes IN (?,?) with one placeholder per value. The number of placeholders depends on the input, but the count is the only thing that does, and it is bounded by the URL length.
buildOrder does the same for sort=-price,name: split on commas, strip the leading minus, look each name up, require Sortable, and emit the column and ASC or DESC from constants. It always appends id ASC. A sort by status alone leaves many rows tied, and without a tie-breaker the database may return them in a different order on different requests, which makes offset pages repeat or skip rows and breaks the keyset pagination from the previous article. A total order costs one more column and removes a whole category of intermittent bug.
likeEscape handles a case people forget. In a LIKE pattern, % and _ are wildcards. A search for 100% that passes the text straight through matches everything beginning with 100, and a search for f_c matches f plus any character plus c. The helper escapes both, and the clause declares the backslash as the escape character with an ESCAPE clause. This is not an injection issue, since the value is still bound, but it is the difference between a search that finds what was typed and one that surprises,.
Running it
The program sets up a table of five items and a second table with a secret in it, then sends eleven requests through Build and the real database.
package main
import (
"database/sql"
"fmt"
"net/url"
"strings"
_ "modernc.org/sqlite"
)
func setup() *sql.DB {
db, _ := sql.Open("sqlite", "file:fs?mode=memory&cache=shared")
db.SetMaxOpenConns(1)
for _, q := range []string{
`CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT, status TEXT, price_cents INTEGER)`,
`CREATE TABLE secrets (token TEXT)`,
`INSERT INTO secrets VALUES ('s3cr3t')`,
`INSERT INTO items (name, status, price_cents) VALUES
('Pro plan', 'open', 4900), ('Team plan', 'open', 9900), ('Free tier', 'closed', 0),
('100% organic', 'open', 1500), ('Proof_of_concept', 'closed', 2500)`,
} {
if _, err := db.Exec(q); err != nil {
panic(err)
}
}
return db
}
func run(db *sql.DB, label, raw string) {
vals, _ := url.ParseQuery(raw)
q, err := Build(vals)
if err != nil {
fmt.Printf("%-24s -> 400 %v\n", label, err)
return
}
rows, err := db.Query("SELECT name FROM items"+q.Where+q.Order, q.Args...)
if err != nil {
fmt.Printf("%-24s -> sql error %v\n", label, err)
return
}
defer rows.Close()
var names []string
for rows.Next() {
var n string
rows.Scan(&n)
names = append(names, n)
}
fmt.Printf("%-24s -> %s%s args=%v %v\n", label, "…"+q.Where, q.Order, q.Args, names)
}
func main() {
db := setup()
run(db, "status filter", "status=open&sort=-price")
run(db, "range", "price[gte]=1500&price[lte]=5000&sort=name")
run(db, "in list", "status=open,closed&sort=price")
run(db, "contains", "name[contains]=pro&sort=name")
run(db, "literal percent", "name[contains]=100%25")
run(db, "literal underscore", "name[contains]=f_c")
run(db, "unknown field", "owner=1")
run(db, "bad operator", "name[gte]=a")
run(db, "injection in value", "status="+url.QueryEscape("x' OR '1'='1"))
run(db, "injection in sort", "sort="+url.QueryEscape("price; DROP TABLE items"))
run(db, "subquery in sort", "sort="+url.QueryEscape("(SELECT token FROM secrets)"))
// Why the sort parameter is the dangerous one: values can be bound, identifiers cannot.
fmt.Println("\nthe naive handler: ORDER BY <whatever the client sent>")
for _, guess := range []string{"s", "x"} {
sortExpr := fmt.Sprintf("CASE WHEN (SELECT substr(token,1,1) FROM secrets)='%s' THEN price_cents ELSE name END", guess)
rows, err := db.Query("SELECT name FROM items ORDER BY " + sortExpr)
if err != nil {
panic(err)
}
var first string
rows.Next()
rows.Scan(&first)
rows.Close()
fmt.Printf("guess %q -> first row %-14s %s\n", guess, first, map[bool]string{true: "(order changed: the guess is right)", false: "(order unchanged)"}[strings.HasPrefix(first, "Free")])
}
}status filter -> … WHERE status = ? ORDER BY price_cents DESC, id ASC args=[open] [Team plan Pro plan 100% organic]
range -> … WHERE price_cents >= ? AND price_cents <= ? ORDER BY name ASC, id ASC args=[1500 5000] [100% organic Pro plan Proof_of_concept]
in list -> … WHERE status IN (?,?) ORDER BY price_cents ASC, id ASC args=[open closed] [Free tier 100% organic Proof_of_concept Pro plan Team plan]
contains -> … WHERE name LIKE ? ESCAPE '\' ORDER BY name ASC, id ASC args=[%pro%] [Pro plan Proof_of_concept]
literal percent -> … WHERE name LIKE ? ESCAPE '\' ORDER BY id ASC args=[%100\%%] [100% organic]
literal underscore -> … WHERE name LIKE ? ESCAPE '\' ORDER BY id ASC args=[%f\_c%] [Proof_of_concept]
unknown field -> 400 invalid query: unknown filter "owner"
bad operator -> 400 invalid query: name does not support "gte"
injection in value -> … WHERE status = ? ORDER BY id ASC args=[x' OR '1'='1] []
injection in sort -> 400 invalid query: cannot sort by "price; DROP TABLE items"
subquery in sort -> 400 invalid query: cannot sort by "(SELECT token FROM secrets)"
the naive handler: ORDER BY <whatever the client sent>
guess "s" -> first row Free tier (order changed: the guess is right)
guess "x" -> first row 100% organic (order unchanged)The first six lines are the normal cases. Each prints the generated WHERE and ORDER BY, the bound arguments and the rows that came back. Every one of them contains only constants and question marks: status = ?, price_cents >= ? AND price_cents <= ?, status IN (?,?). The contains request finds Pro plan and Proof_of_concept. The two literal tests show the escaping working: name[contains]=100%25 (a literal 100%) returns only 100% organic, and f_c returns only Proof_of_concept, whose name contains the literal f_c, rather than anything with an f, a character and a c.
The next lines are the rejected ones. An unknown field returns a 400 saying so, and an operator not allowed on a column, name[gte], also does. The value injection attempt, status=x' OR '1'='1, is not rejected: it is accepted as the string it is, bound as a single argument, and matches no rows. That is the correct result. The value is data and cannot be anything else. The sort injection attempts (price; DROP TABLE items and a subquery) are rejected with a 400, because neither is a sortable column name.
The attack the placeholder advice misses
The last block of output is the reason for this article. It simulates a handler that binds all values correctly but pastes the sort parameter into the query, which is the shape of a lot of real code: "... ORDER BY " + r.URL.Query().Get("sort").
The attacker sends a sort expression that depends on a secret:
CASE WHEN (SELECT substr(token,1,1) FROM secrets)='s' THEN price_cents ELSE name ENDIf the guess about the first character of the token is right, rows are ordered by price and the first row is Free tier. If the guess is wrong, they are ordered by name and the first row is 100% organic. The two outputs in the log differ, so the response order tells the attacker whether the guess was right, one bit per request. Repeated over each position and each candidate character, that reads the secret out of a table the API never exposes. It is a blind injection, there is no error and no data in the response, and it works through nothing but the order of the rows. Bound values do not stop it because the expression is not a value.
Nothing in the allowlist version can express this: the string CASE WHEN ... is not a key in columns, so the request is a 400 before any SQL exists. A defence that relies on the database account having limited privileges helps (the application user should not be able to read tables it does not need), but it is a second layer, not a substitute.
Tests
package main
import (
"net/url"
"strings"
"testing"
)
func TestHostileNamesAreRejectedAsSortAndAsFilter(t *testing.T) {
hostile := []string{"'; DROP TABLE items; --", "price; DROP", "1=1", "(SELECT 1)", "name--", `" OR "1"="1`}
for _, h := range hostile {
for _, v := range []url.Values{{"sort": {h}}, {"sort": {"-" + h}}, {h: {"x"}}} {
if q, err := Build(v); err == nil {
t.Errorf("%v was accepted: %s%s", v, q.Where, q.Order)
}
}
}
}
func TestValuesAreBoundNotInlined(t *testing.T) {
v := url.Values{"status": {"x' OR '1'='1"}}
q, err := Build(v)
if err != nil || strings.Contains(q.Where, "OR") || len(q.Args) != 1 {
t.Fatalf("where=%q args=%v err=%v", q.Where, q.Args, err)
}
}
func TestEveryOrderEndsWithTheTieBreaker(t *testing.T) {
for _, s := range []string{"", "price", "-name,price"} {
q, err := Build(url.Values{"sort": {s}})
if err != nil || !strings.HasSuffix(q.Order, "id ASC") {
t.Errorf("sort=%q -> %q %v", s, q.Order, err)
}
}
}=== RUN TestHostileNamesAreRejectedAsSortAndAsFilter
--- PASS: TestHostileNamesAreRejectedAsSortAndAsFilter (0.00s)
=== RUN TestValuesAreBoundNotInlined
--- PASS: TestValuesAreBoundNotInlined (0.00s)
=== RUN TestEveryOrderEndsWithTheTieBreaker
--- PASS: TestEveryOrderEndsWithTheTieBreaker (0.00s)
PASS
ok example.test/lab 1.318sThe first test throws hostile strings at the builder as a sort key, as a negated sort key and as a filter field, and requires every one to be rejected. The second asserts that a hostile value produces no trace in the SQL text and exactly one bound argument. The third checks the tie-breaker. These are properties, not examples, so they keep holding when someone adds a column to the map. Add a fuzz test with go test -fuzz over the sort and filter strings if the grammar grows; the invariant to check is that Where and Order contain only characters from your constant tables.
Design notes
Keep the grammar boring. field=value, field[op]=value and sort=-field,field are enough for most APIs and map easily to documentation. Resist building a query language. Each new operator is a new surface to test, and a client who needs OR across fields usually needs a dedicated endpoint or a search feature.
Limit the work a request can ask for. Cap the number of filters, the length of an IN list, the number of sort keys, and the page size. Without caps a single request can become a very large query. Return a 400 when a cap is exceeded.
Make sure sorts and filters have indexes. Allowing sort on a column makes that column a query you must serve. An index on every sortable column plus the tie-breaker, or an explicit decision that some columns are not sortable, is part of the contract. A contains filter with a leading wildcard cannot use a normal index; for real text search use full-text indexing (site search with FTS5).
Do not reflect the allowlist loosely. It is tempting to generate the map from struct tags with reflection. That can work, but a field added to a struct then becomes filterable automatically, which is exactly the accident the allowlist exists to prevent. An explicit map is a few lines per field and each addition is a deliberate act.
Keep the same discipline everywhere. The same rule applies to GROUP BY, to SELECT field lists (?fields=name,price), to table names in multi-tenant code and to JSON path expressions. Anything that is a name rather than a value comes from a table in code. The OWASP SQL injection prevention cheat sheet lists the same defences, and the database/sql documentation on DB.Query describes how arguments are passed. For input that gets validated before it reaches this point, see request validation, and for the wider checklist of API risks, the API security checklist.
