Build Dynamic Queries in Go Without String Concatenation

Search pages, admin tables and report endpoints all share a problem: the query depends on which filters the user supplied. A fixed SQL string cannot express "filter by name if given, by age if given, by country if given". The tempting answer is to build the string by concatenation, which is how SQL injection happens. The safe answer is almost as short. This article builds a dynamic query two ways on Go 1.25.6, by hand and with a builder library, and shows the generated SQL.

The rule

Two kinds of things go into a query.

• Structure: keywords, table and column names, operators, the number of placeholders. These come from your code only, as fixed fragments or from an allowlist.
• Values: whatever the user typed. These go in as parameters, always.

A dynamic query is a sequence of decisions about structure, each driven by which filters are present, and a list of values appended in the same order. The user's input never decides the structure directly. See SQL injection for what goes wrong when it does.

By hand

package main

import (
	"database/sql"
	"fmt"
	"log"
	"strings"

	sq "github.com/Masterminds/squirrel"
	_ "modernc.org/sqlite"
)

type Filter struct {
	Name    string
	MinAge  int
	Country []string
	SortBy  string
}

// build composes WHERE clauses by hand: the SQL text is fixed fragments, the values are arguments.
func build(f Filter) (string, []any) {
	var where []string
	var args []any
	if f.Name != "" {
		where = append(where, "name LIKE ?")
		args = append(args, "%"+f.Name+"%")
	}
	if f.MinAge > 0 {
		where = append(where, "age >= ?")
		args = append(args, f.MinAge)
	}
	if len(f.Country) > 0 {
		where = append(where, "country IN ("+strings.TrimSuffix(strings.Repeat("?,", len(f.Country)), ",")+")")
		for _, c := range f.Country {
			args = append(args, c)
		}
	}
	q := "SELECT name, age, country FROM people"
	if len(where) > 0 {
		q += " WHERE " + strings.Join(where, " AND ")
	}
	order := map[string]string{"": "name", "name": "name", "age": "age DESC"}[f.SortBy]
	if order == "" {
		order = "name"
	}
	return q + " ORDER BY " + order, args
}

func main() {
	db, err := sql.Open("sqlite", "file:lab590?mode=memory&cache=shared")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	_, _ = db.Exec(`create table people (name text, age int, country text);
		insert into people values ('ada', 36, 'UK'), ('grace', 45, 'US'), ('linus', 54, 'FI'), ('ken', 81, 'US')`)

	for _, f := range []Filter{
		{},
		{Name: "a", MinAge: 40},
		{Country: []string{"US", "FI"}, SortBy: "age"},
		{Name: "x'; DROP TABLE people; --"},
	} {
		q, args := build(f)
		fmt.Printf("%-100s %v\n", q, args)
		rows, err := db.Query(q, args...)
		if err != nil {
			log.Fatal(err)
		}
		var names []string
		for rows.Next() {
			var n string
			var a int
			var c string
			_ = rows.Scan(&n, &a, &c)
			names = append(names, n)
		}
		rows.Close()
		fmt.Printf("  -> %v\n", names)
	}

	// The same query with squirrel.
	b := sq.Select("name").From("people").Where(sq.GtOrEq{"age": 40}).Where(sq.Eq{"country": []string{"US", "FI"}}).OrderBy("age DESC")
	sqlText, args, _ := b.ToSql()
	fmt.Printf("squirrel: %s %v\n", sqlText, args)
	pg, _, _ := b.PlaceholderFormat(sq.Dollar).ToSql()
	fmt.Printf("squirrel for Postgres: %s\n", pg)
}

go run . 2>&1

SELECT name, age, country FROM people ORDER BY name                                                  []
  -> [ada grace ken linus]
SELECT name, age, country FROM people WHERE name LIKE ? AND age >= ? ORDER BY name                   [%a% 40]
  -> [grace]
SELECT name, age, country FROM people WHERE country IN (?,?) ORDER BY age DESC                       [US FI]
  -> [ken linus grace]
SELECT name, age, country FROM people WHERE name LIKE ? ORDER BY name                                [%x'; DROP TABLE people; --%]
  -> []
squirrel: SELECT name FROM people WHERE age >= ? AND country IN (?,?) ORDER BY age DESC [40 US FI]
squirrel for Postgres: SELECT name FROM people WHERE age >= $1 AND country IN ($2,$3) ORDER BY age DESC

build takes a Filter and returns the SQL and the arguments. Read it in five parts.

The base query is a constant. SELECT name, age, country FROM people never changes.

Each filter adds a fragment and an argument. if f.Name != "" appends name LIKE ? to a where slice and "%"+f.Name+"%" to args. The fragment is a literal string in your source. The user's text goes only into args.

An IN list needs a variable number of placeholders. For Country: []string{"US", "FI"}, the code writes IN (?,?) by repeating ?, for each value and trimming the last comma, then appends each country to args. The number of placeholders equals the number of arguments by construction. The case to guard is the empty list: IN () is a syntax error, so the code adds the clause only when the slice is non-empty. Decide what an empty filter means (no filter, or no results) and write it down.

The conditions are joined with AND. strings.Join(where, " AND ") is safe because every element is one of your fragments.

The sort order comes from a map. map[string]string{"": "name", "name": "name", "age": "age DESC"}[f.SortBy] turns the user's sort parameter into one of your constants, and unknown values map to the empty string, which falls back to name. A placeholder cannot carry a column name, so the allowlist is the mechanism.

What the program prints

The output shows four filters going through the function, each with its SQL and arguments, then the names returned by running it.

1. No filters: SELECT name, age, country FROM people ORDER BY name with no arguments returns all four people.
2. Name and minimum age: WHERE name LIKE ? AND age >= ? ORDER BY name with arguments [%a% 40] returns [grace].
3. Countries and sort by age: WHERE country IN (?,?) ORDER BY age DESC with [US FI] returns [ken linus grace], oldest first.
4. The hostile name: x'; DROP TABLE people; --. The SQL is ... WHERE name LIKE ? ORDER BY name and the argument is %x'; DROP TABLE people; --%. It matches nobody and drops nothing. The attack text is just data.

A table in the database lived through the fourth case, which is the point.

With squirrel

Squirrel is a small builder that does this bookkeeping. The program builds a query with it:

b := sq.Select("name").From("people").
	Where(sq.GtOrEq{"age": 40}).
	Where(sq.Eq{"country": []string{"US", "FI"}}).
	OrderBy("age DESC")
sqlText, args, _ := b.ToSql()

The generated SQL is SELECT name FROM people WHERE age >= ? AND country IN (?,?) ORDER BY age DESC with arguments [40 US FI]. sq.Eq with a slice expands to an IN list with the right number of placeholders, and a nil slice would produce a false condition rather than a syntax error. For PostgreSQL, PlaceholderFormat(sq.Dollar) renders $1, $2, $3 instead of question marks, as the last line of the output shows.

The builder still trusts you with structure: OrderBy("age DESC") takes a string, and if that string comes from a user, you have reintroduced the problem. Map it through an allowlist before passing it, exactly as in the hand-written version. Builders prevent mistakes in placeholders and commas, not in what you put into raw string arguments.

Choosing between them

For a handful of filters on one query, the hand-written function is fine and has no dependency. When many queries share the pattern, or when you need inserts, updates and upserts built from maps, a builder saves code. The wider comparison, including goqu and the case for generating code with sqlc, is in query builders compared.

Pagination, counts and other dynamic parts

• Limit and offset are values; pass them as parameters (LIMIT ? OFFSET ?) after converting from strings with strconv.Atoi and clamping to a maximum, so a client cannot ask for a million rows.
• Keyset pagination (WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT ?) is stable under inserts and fast on large tables, and needs no offset.
• A total count for paging uses the same where and args with SELECT count(*). Build the WHERE once and use it twice.
• Full text search has its own operators; pass the search string as a parameter and let the database parse it (plainto_tsquery($1) in PostgreSQL).
• Case-insensitive matching is a database matter: ILIKE in PostgreSQL, a case-insensitive collation in MySQL. Do not lower-case in Go and compare there.

Testing a builder

A dynamic query is a function of its input, so test it as one. A table of filters with the expected SQL text and arguments checks the structure without a database, and a few integration tests run the queries against the real engine. Assert on both the string and the arguments: a bug where the arguments are in the wrong order is silent until the data is wrong. Include the hostile cases and the empty ones: no filters, an empty list, a single value, a value containing %, a quote or a comment marker. For the technique, see table-driven tests.

Keep one rule in mind and most of the rest follows: the only strings that reach the SQL text are strings you typed in the source. The Go team's documentation of database/sql arguments covers placeholders, and the OWASP page on query parameterization gives examples for several languages.