SQL injection and
parameterized queries.

export type Query = { text: string; values: (string | number)[] };

// Intentionally vulnerable. Use only with the disposable lesson fixture.
export function vulnerableQuery(tenant: string, title: string): Query {
	return {
		text: `SELECT id, tenant_id, title FROM tickets WHERE tenant_id = '${tenant}' AND title = '${title}' ORDER BY id`,
		values: []
	};
}

export function parameterizedQuery(tenant: string, title: string): Query {
	return {
		text: 'SELECT id, tenant_id, title FROM tickets WHERE tenant_id = ? AND title = ? ORDER BY id',
		values: [tenant, title]
	};
}

export function sortedQuery(tenant: string, title: string, sort: string): Query {
	// Only these application-owned fragments may become SQL syntax.
	let order: string;
	switch (sort) {
		case 'oldest':
			order = 'id ASC';
			break;
		case 'newest':
			order = 'id DESC';
			break;
		default:
			throw new Error('Unsupported sort');
	}
	return {
		text: `SELECT id, tenant_id, title FROM tickets WHERE tenant_id = ? AND title = ? ORDER BY ${order}`,
		values: [tenant, title]
	};
}

import { DatabaseSync } from 'node:sqlite';
import { parameterizedQuery, vulnerableQuery, sortedQuery, type Query } from './queries';

export type Ticket = { id: number; tenant_id: string; title: string };

export function fixture(): DatabaseSync {
	const db = new DatabaseSync(':memory:');
	db.exec(`CREATE TABLE tickets (id INTEGER PRIMARY KEY, tenant_id TEXT NOT NULL, title TEXT NOT NULL);
		INSERT INTO tickets VALUES (1, 'juniper', 'Printer'), (2, 'juniper', 'Printer'),
		(3, 'kestrel', 'Payroll'), (4, 'juniper', 'O''Brien'), (5, 'juniper', '100%_done');`);
	return db;
}

export function execute(db: DatabaseSync, query: Query): Ticket[] {
	return db.prepare(query.text).all(...query.values) as Ticket[];
}

// Intentionally vulnerable: preparing already-concatenated SQL does not repair it.
export function findUnsafe(db: DatabaseSync, tenant: string, title: string): Ticket[] {
	const query = vulnerableQuery(tenant, title);
	return db.prepare(query.text).all() as Ticket[];
}

export function findSafe(db: DatabaseSync, tenant: string, title: string): Ticket[] {
	const query = parameterizedQuery(tenant, title);
	return db.prepare(query.text).all(...query.values) as Ticket[];
}

export function findSorted(
	db: DatabaseSync,
	tenant: string,
	title: string,
	sort: string
): Ticket[] {
	return execute(db, sortedQuery(tenant, title, sort));
}

import { describe, expect, it } from 'vitest';
import cases from './cases.json';
import { fixture, findSafe, findUnsafe, findSorted } from './tickets';

describe('ticket lookup on real, disposable SQLite databases', () => {
	for (const c of cases) {
		it(c.name, () => {
			const db = fixture();
			try {
				const rows = findSafe(db, c.tenant, c.title);
				expect(rows.map((row) => row.id)).toEqual(c.ids);
				expect(rows.every((row) => row.tenant_id === c.tenant && row.title === c.title)).toBe(true);
				expect(findSafe(db, 'juniper', 'Printer').map((row) => row.id)).toEqual([1, 2]);
			} finally {
				db.close();
			}
		});
	}

	it('the same input crosses the tenant boundary only in the vulnerable lookup', () => {
		const db = fixture();
		try {
			const input = "' OR 1=1 -- ";
			expect(findUnsafe(db, 'juniper', input).map((row) => row.id)).toEqual([1, 2, 3, 4, 5]);
			expect(findSafe(db, 'juniper', input)).toEqual([]);
			expect(findSafe(db, 'juniper', "O'Brien").map((row) => row.id)).toEqual([4]);
		} finally {
			db.close();
		}
	});

	it('accepts only named sort choices and preserves the tenant boundary', () => {
		const db = fixture();
		try {
			expect(findSorted(db, 'juniper', 'Printer', 'newest').map((row) => row.id)).toEqual([2, 1]);
			expect(findSorted(db, 'juniper', 'Printer', 'oldest').map((row) => row.id)).toEqual([1, 2]);
			expect(() => findSorted(db, 'juniper', 'Printer', 'id; DROP TABLE tickets')).toThrow(
				'Unsupported sort'
			);
		} finally {
			db.close();
		}
	});
});

[
	{ "name": "ordinary title", "tenant": "juniper", "title": "Printer", "ids": [1, 2] },
	{ "name": "another tenant's title", "tenant": "juniper", "title": "Payroll", "ids": [] },
	{ "name": "legitimate apostrophe", "tenant": "juniper", "title": "O'Brien", "ids": [4] },
	{ "name": "predicate injection", "tenant": "juniper", "title": "' OR 1=1 -- ", "ids": [] },
	{
		"name": "statement-shaped input",
		"tenant": "juniper",
		"title": "'; DROP TABLE tickets; --",
		"ids": []
	},
	{ "name": "literal wildcard characters", "tenant": "juniper", "title": "100%_done", "ids": [5] },
	{ "name": "empty title", "tenant": "juniper", "title": "", "ids": [] },
	{ "name": "other tenant's own search", "tenant": "kestrel", "title": "Payroll", "ids": [3] }
]

package tickets

import (
	"context"
	"database/sql"
	"fmt"
	_ "github.com/mattn/go-sqlite3"
)

type Ticket struct {
	ID            int
	Tenant, Title string
}

func Fixture() (*sql.DB, error) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(1)
	_, err = db.Exec(`CREATE TABLE tickets (id INTEGER PRIMARY KEY, tenant_id TEXT NOT NULL, title TEXT NOT NULL);
		INSERT INTO tickets VALUES (1, 'juniper', 'Printer'), (2, 'juniper', 'Printer'),
		(3, 'kestrel', 'Payroll'), (4, 'juniper', 'O''Brien'), (5, 'juniper', '100%_done');`)
	if err != nil {
		db.Close()
		return nil, err
	}
	return db, nil
}

// Intentionally vulnerable. Use only with the disposable lesson fixture.
func FindUnsafe(ctx context.Context, db *sql.DB, tenant, title string) ([]Ticket, error) {
	query := fmt.Sprintf("SELECT id, tenant_id, title FROM tickets WHERE tenant_id = '%s' AND title = '%s' ORDER BY id", tenant, title)
	return read(ctx, db, query)
}


func FindSafe(ctx context.Context, db *sql.DB, tenant, title string) ([]Ticket, error) {
	return read(ctx, db,
		"SELECT id, tenant_id, title FROM tickets WHERE tenant_id = ? AND title = ? ORDER BY id",
		tenant, title)
}

func read(ctx context.Context, db *sql.DB, query string, args ...any) ([]Ticket, error) {
	rows, err := db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	tickets := []Ticket{}
	for rows.Next() {
		var t Ticket
		if err := rows.Scan(&t.ID, &t.Tenant, &t.Title); err != nil {
			return nil, err
		}
		tickets = append(tickets, t)
	}
	return tickets, rows.Err()
}


func FindSorted(ctx context.Context, db *sql.DB, tenant, title, sort string) ([]Ticket, error) {
	var order string
	switch sort {
	case "oldest":
		order = "id ASC"
	case "newest":
		order = "id DESC"
	default:
		return nil, fmt.Errorf("unsupported sort")
	}
	return read(ctx, db,
		"SELECT id, tenant_id, title FROM tickets WHERE tenant_id = ? AND title = ? ORDER BY "+order,
		tenant, title)
}

package tickets

import (
	"context"
	"encoding/json"
	"os"
	"reflect"
	"testing"
)

func ids(rows []Ticket) []int {
	result := []int{}
	for _, row := range rows {
		result = append(result, row.ID)
	}
	return result
}

func TestSharedCases(t *testing.T) {
	data, err := os.ReadFile("../cases.json")
	if err != nil {
		t.Fatal(err)
	}
	var cases []struct {
		Name, Tenant, Title string
		IDs                 []int
	}
	if err := json.Unmarshal(data, &cases); err != nil {
		t.Fatal(err)
	}
	for _, c := range cases {
		t.Run(c.Name, func(t *testing.T) {
			db, err := Fixture()
			if err != nil {
				t.Fatal(err)
			}
			defer db.Close()
			rows, err := FindSafe(context.Background(), db, c.Tenant, c.Title)
			if err != nil {
				t.Fatal(err)
			}
			if !reflect.DeepEqual(ids(rows), c.IDs) {
				t.Fatalf("got %v, want %v", ids(rows), c.IDs)
			}
			for _, row := range rows {
				if row.Tenant != c.Tenant || row.Title != c.Title {
					t.Fatalf("boundary crossed: %+v", row)
				}
			}
			remaining, err := FindSafe(context.Background(), db, "juniper", "Printer")
			if err != nil || !reflect.DeepEqual(ids(remaining), []int{1, 2}) {
				t.Fatalf("fixture changed: %v %v", remaining, err)
			}
		})
	}
}

func TestRegression(t *testing.T) {
	db, err := Fixture()
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()
	ctx := context.Background()
	input := "' OR 1=1 -- "
	unsafe, err := FindUnsafe(ctx, db, "juniper", input)
	if err != nil || !reflect.DeepEqual(ids(unsafe), []int{1, 2, 3, 4, 5}) {
		t.Fatalf("reproduction: %v %v", unsafe, err)
	}
	safe, err := FindSafe(ctx, db, "juniper", input)
	if err != nil || len(safe) != 0 {
		t.Fatalf("injection: %v %v", safe, err)
	}
	ordinary, err := FindSafe(ctx, db, "juniper", "O'Brien")
	if err != nil || !reflect.DeepEqual(ids(ordinary), []int{4}) {
		t.Fatalf("apostrophe: %v %v", ordinary, err)
	}
}


func TestSort(t *testing.T) {
	db, err := Fixture()
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()
	for sort, want := range map[string][]int{"oldest": {1, 2}, "newest": {2, 1}} {
		rows, err := FindSorted(context.Background(), db, "juniper", "Printer", sort)
		if err != nil || !reflect.DeepEqual(ids(rows), want) {
			t.Fatalf("sort %s: %v %v", sort, rows, err)
		}
	}
	if _, err := FindSorted(context.Background(), db, "juniper", "Printer", "id; DROP TABLE tickets"); err == nil {
		t.Fatal("accepted SQL as a sort choice")
	}
}

module heyrian.dev/lessons/sql-injection

go 1.24

require github.com/mattn/go-sqlite3 v1.14.33