Placeholders and SQL Injection

Pass values as query arguments instead of pasting them into the SQL string, and SQL injection stops being possible.

Syntaxdb.QueryRowContext(ctx, "SELECT id FROM users WHERE email = $1", email)

A query is text. If you build that text by pasting in something a user typed, the user is writing part of your SQL. Give a login form the name '; DROP TABLE users; -- and the database receives two statements and runs both. That is SQL injection, and it is still one of the most common ways services are broken into.

The fix is built into database/sql. You write a placeholder where the value goes and pass the value as an extra argument. The value travels to the database separately from the statement, so it is never parsed as SQL. Quotes, semicolons and comment markers inside it are just characters in a string.

The placeholder is driver-specific

DriverFormExample
MySQL, SQLite?WHERE name = ?
Postgres$1, $2WHERE name = $1
SQL Server@nameWHERE name = @name

? arguments are matched by position. Postgres numbers them, so $1 can appear twice and still mean the first argument. Drivers that support names accept sql.Named("name", value) in place of a bare value.

What a placeholder cannot do

It stands for one value. It cannot name a table or a column, and it cannot hold a list. For a sort column, compare the input against a fixed list of allowed names you wrote yourself. For IN, build the placeholder list from the number of values, then pass the values as arguments; the only thing you are assembling is ?, ?, ?.

There is never a reason to use fmt.Sprintf on a value.

Example

Example · go
package main

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

	_ "modernc.org/sqlite"
)

func main() {
	db, err := sql.Open("sqlite", ":memory:")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	db.SetMaxOpenConns(1) // one connection, so every statement sees the same in-memory database

	if _, err := db.Exec("CREATE TABLE users (name TEXT)"); err != nil {
		log.Fatal(err)
	}
	if _, err := db.Exec("INSERT INTO users VALUES ('ada')"); err != nil {
		log.Fatal(err)
	}

	// Hostile input. Bound as an argument, it is only ever a name to look for.
	input := "ada'; DROP TABLE users; --"

	var matches int
	if err := db.QueryRow("SELECT count(*) FROM users WHERE name = ?", input).Scan(&matches); err != nil {
		log.Fatal(err)
	}
	fmt.Println("matches:", matches)

	// The table is still here: the text was never parsed as SQL.
	var remaining int
	if err := db.QueryRow("SELECT count(*) FROM users").Scan(&remaining); err != nil {
		log.Fatal(err)
	}
	fmt.Println("rows left in users:", remaining)
}

// Output:
// matches: 0
// rows left in users: 1

When to use it

  • Accept a search term from a web form without letting it alter the query.
  • Let an API client choose a sort order from a fixed set of columns safely.
  • Fetch a batch of records by a list of ids that arrives with a different length each time.

More examples

Reusing a numbered placeholder in Postgres

The wildcards are added in SQL with the concatenation operator, so the user's text stays an argument. Writing the pattern as fmt.Sprintf("%%%s%%", q) and pasting it into the statement is the mistake this avoids.

Example · go
package storage

import "context"

// One argument, used twice. With ? placeholders you would have to pass q twice.
const searchSQL = `
SELECT id, name FROM users
WHERE name ILIKE '%' || $1 || '%' OR email ILIKE '%' || $1 || '%'
ORDER BY id
LIMIT $2`

func (s *Store) Search(ctx context.Context, q string, limit int) error {
	rows, err := s.db.QueryContext(ctx, searchSQL, q, limit)
	if err != nil {
		return err
	}
	defer rows.Close()
	// ... scan rows here ...
	return rows.Err()
}

A sort column, checked against a list you wrote

Sorting by a user-chosen column is the common case where a placeholder cannot help. An allowlist map turns untrusted input into one of a few fixed strings before it is anywhere near the query.

Example · go
package storage

import (
	"context"
	"fmt"
)

// Map request values to literal SQL fragments. Anything not in the map is rejected,
// so the only column names that ever reach the database are these three.
var sortColumns = map[string]string{
	"name":    "name ASC",
	"newest":  "created_at DESC",
	"oldest":  "created_at ASC",
}

func (s *Store) ListSorted(ctx context.Context, sort string, limit int) error {
	order, ok := sortColumns[sort]
	if !ok {
		return fmt.Errorf("unknown sort %q", sort)
	}

	// Sprintf is acceptable here only because order came from the map above.
	query := fmt.Sprintf("SELECT id, name FROM users ORDER BY %s LIMIT $1", order)
	rows, err := s.db.QueryContext(ctx, query, limit)
	if err != nil {
		return err
	}
	defer rows.Close()
	return rows.Err()
}

An IN clause with a variable number of values

database/sql has no way to bind a list, so the placeholder list has to be generated. Note what is being built: punctuation derived from a length, never a value. Guard the empty slice, because IN () is a syntax error.

Example · go
package storage

import (
	"context"
	"fmt"
	"strings"
)

func (s *Store) UsersByID(ctx context.Context, ids []int64) error {
	if len(ids) == 0 {
		return nil // "IN ()" is not valid SQL
	}

	// Build only the placeholders: $1, $2, $3. The ids stay arguments.
	placeholders := make([]string, len(ids))
	args := make([]any, len(ids))
	for i, id := range ids {
		placeholders[i] = fmt.Sprintf("$%d", i+1)
		args[i] = id
	}

	query := "SELECT id, name FROM users WHERE id IN (" + strings.Join(placeholders, ", ") + ")"
	rows, err := s.db.QueryContext(ctx, query, args...) // args... spreads the slice
	if err != nil {
		return err
	}
	defer rows.Close()
	return rows.Err()
}

Related

go sql querygo sql basicsgo input safetygo validationgo variadic

Discussion

  • Be the first to comment on this lesson.