Транзакции и SQL-инъекции: запрос — не строка, которую склеивают

Зачем это нужно

До сих пор запросы в уроках были короткие и с параметрами, и это выглядело как стиль. Это не стиль.

Строка, которую прислал читатель, — не текст, а чужая воля. Если её приклеить к запросу, читатель допишет к вашему запросу свой. Так вынесли не одну базу, и способ, которым это делают, помещается в одну строку формы.

Второе правило про другое. Одно действие в блоге часто состоит из двух запросов: перенести статью и записать это в счётчик, списать деньги и начислить их. Если второй запрос упадёт, первый уже сделан — и база останется в состоянии, которого в жизни не бывает.

Сразу целиком

Новая папка, go mod init sabaq29, драйвер тот же:

package main

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

	_ "modernc.org/sqlite"
)

func main() {
	os.Remove("blog.db")
	db, err := sql.Open("sqlite", "blog.db")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	setup(db)

	fmt.Println("== склейка строк")
	fmt.Println("обычный адрес:", bad(db, "dala"))
	fmt.Println("подстроенный: ", bad(db, "dala' or '1'='1"))

	fmt.Println("== параметр")
	fmt.Println("обычный адрес:", good(db, "dala"))
	fmt.Println("подстроенный: ", good(db, "dala' or '1'='1"))

	fmt.Println("== порядок")
	fmt.Println("order by ?       ->", byParam(db, "title"))
	fmt.Println("по белому списку ->", byAllowed(db, "title"))
	fmt.Println("чужое поле       ->", byAllowed(db, "views; drop table articles"))
}

// bad приклеивает значение к тексту запроса. Так писать нельзя.
func bad(db *sql.DB, slug string) string {
	q := fmt.Sprintf(`select count(*) from articles where slug = '%s'`, slug)
	var n int
	if err := db.QueryRow(q).Scan(&n); err != nil {
		return "ошибка: " + err.Error()
	}
	return fmt.Sprint("строк: ", n)
}

// good отдаёт значение отдельно, как значение.
func good(db *sql.DB, slug string) string {
	var n int
	if err := db.QueryRow(`select count(*) from articles where slug = ?`, slug).Scan(&n); err != nil {
		return "ошибка: " + err.Error()
	}
	return fmt.Sprint("строк: ", n)
}

func byParam(db *sql.DB, col string) string {
	return list(db, `select slug from articles order by ?`, col)
}

// byAllowed сверяет имя колонки со списком, который написали мы. Имени вне
// списка не объясняют ошибку — в нём отказывают.
func byAllowed(db *sql.DB, col string) string {
	allowed := map[string]bool{"slug": true, "title": true, "views": true}
	if !allowed[col] {
		return "отказ: такого поля нет"
	}
	return list(db, `select slug from articles order by `+col)
}

func list(db *sql.DB, q string, args ...any) string {
	rows, err := db.Query(q, args...)
	if err != nil {
		return "ошибка: " + err.Error()
	}
	defer rows.Close()
	var out []string
	for rows.Next() {
		var s string
		if err := rows.Scan(&s); err != nil {
			return "ошибка: " + err.Error()
		}
		out = append(out, s)
	}
	if err := rows.Err(); err != nil {
		return "ошибка: " + err.Error()
	}
	return fmt.Sprint(out)
}

func setup(db *sql.DB) {
	if _, err := db.Exec(`create table articles (
		id    integer primary key,
		slug  text not null unique,
		title text not null,
		views integer not null default 0
	) strict`); err != nil {
		log.Fatal(err)
	}
	for _, a := range [][2]string{{"dala", "Юрта"}, {"salem", "Азбука"}, {"shanyraq", "Мир"}} {
		if _, err := db.Exec(`insert into articles (slug, title) values (?, ?)`, a[0], a[1]); err != nil {
			log.Fatal(err)
		}
	}
}

Вывод:

== склейка строк
обычный адрес: строк: 1
подстроенный:  строк: 3
== параметр
обычный адрес: строк: 1
подстроенный:  строк: 0
== порядок
order by ?       -> [dala salem shanyraq]
по белому списку -> [salem shanyraq dala]
чужое поле       -> отказ: такого поля нет

Разбор

Три строки вместо одной

Смотрите на первые две строки вывода. Адрес dala нашёл одну статью — правильно. Адрес dala' or '1'='1 нашёл три, то есть все.

Что произошло, видно, если собрать запрос руками:

select count(*) from articles where slug = 'dala' or '1'='1'

Кавычка закрыла адрес, а дальше пошло условие, которое верно всегда. Проверка адреса просто перестала существовать. Ровно так же дописывают or 1=1 в форму входа, чтобы войти без пароля, — а ; позволяет дописать не условие, а целый второй запрос.

Образ. Заполненный бланк, куда вписали лишнюю строчку — и она читается как продолжение самого бланка. Не приписка на полях, а такой же пункт, как напечатанные.

? — не подстановка, а отдельная посылка

Третья и четвёртая строки вывода: тот же подстроенный адрес через ? даёт ноль.

Ноль, а не ошибку — и это важно понять правильно. База получила запрос и значение порознь. Текст запроса разобран до того, как значение до него добралось, поэтому кавычки внутри значения ничего не закрывают: это просто буквы. База честно поискала статью с адресом dala' or '1'='1 и не нашла ни одной.

Отсюда правило без исключений: всё, что пришло снаружи, идёт через ?. Не «если похоже на опасное», не «если это строка» — всё. Экранировать кавычки руками не нужно и не надо: это работа драйвера, и он её делает лучше.

В Postgres места для значений пишут иначе — $1, $2 — но смысл тот же.

Что через ? подставить нельзя

Пятая строка вывода — самая коварная во всём уроке:

order by ?       -> [dala salem shanyraq]

Ошибки нет. Сортировки тоже нет: адреса вышли в том порядке, в каком лежали. По белому списку тот же title даёт другой порядок — [salem shanyraq dala], — и вот он-то правильный: «Азбука», «Мир», «Юрта».

Причина в том же, в чём защита. Значение — это данные, а имя колонки — часть текста запроса. База получила order by 'title' — сортировку по строке-константе, одинаковой для всех строк. Сортировать по ней бессмысленно, и порядок остался прежним.

Кстати о сортировке: SQLite по умолчанию сравнивает строки по кодам символов, а не по алфавиту языка. Для латиницы это почти одно и то же, а для казахского нет: Қ и Ә стоят в кодировке далеко от того места, где им положено быть в казахском алфавите. Правильный порядок для человека — отдельная работа, и в базе она называется collation.

Через ? нельзя подставить ни имя таблицы, ни имя колонки, ни asc/desc, ни limit в некоторых базах. Именно здесь и появляются инъекции у людей, которые «везде используют параметры».

Белый список вместо изобретательности

Если имя колонки всё-таки приходит снаружи — из ссылки ?sort=title, — его нельзя ни экранировать, ни почистить. Его можно только сверить со списком, который написали вы:

allowed := map[string]bool{"slug": true, "title": true, "views": true}
if !allowed[col] {
	return "отказ: такого поля нет"
}

Последняя строка вывода показывает отказ на views; drop table articles. Заметьте, чего в этой проверке нет: попыток угадать, что опасно. Разрешено перечисленное, остальное — нет. Список из трёх слов надёжнее любой чистки, потому что не зависит от вашей фантазии о том, что придумает чужой человек.

Транзакция: всё или ничего

Второе правило урока. Возьмём действие из двух запросов: переселить статью на новый адрес и отметить это в счётчике.

func move(db *sql.DB, from, to string) error {
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	if _, err := tx.Exec(`update articles set views = views + 1 where slug = 'dala'`); err != nil {
		return err
	}
	if _, err := tx.Exec(`update articles set slug = ? where slug = ?`, to, from); err != nil {
		return err
	}
	return tx.Commit()
}

Сначала посмотрим, что будет без Begin — теми же двумя db.Exec подряд. Переселяем на занятый адрес, потом на свободный:

до: dala=0 shanyraq=0
перенос: constraint failed: UNIQUE constraint failed: articles.slug (2067)
после: dala=1 shanyraq=0
перенос: <nil>
после: dala=2 koktem=0

Счётчик вырос до 1 за переезд, которого не было: первый запрос прошёл, второй упал. К концу — 2 при одном настоящем переезде. База не сломана, она хуже: она врёт.

Теперь с транзакцией, тот же порядок действий:

до: dala=0 shanyraq=0
перенос: constraint failed: UNIQUE constraint failed: articles.slug (2067)
после: dala=0 shanyraq=0
перенос: <nil>
после: dala=1 koktem=0

После неудачи dala=0 — счётчик не тронут, статья на месте. После удачи dala=1. Транзакция — это обещание базы: либо оба запроса, либо ни одного.

defer tx.Rollback() — одна строка на все выходы

Из функции можно выйти в четырёх местах, и в трёх из них транзакцию надо откатить. Писать tx.Rollback() перед каждым return — верный способ однажды забыть.

Поэтому пишут одну строку сразу после Begin. Работает это потому, что откат после успешного Commit ничего не делает: транзакция уже завершена, Rollback вернёт sql.ErrTxDone, и это не ошибка, а сообщение «поздно, всё уже кончилось». Значение мы не проверяем сознательно.

И то, о чём легко забыть: транзакция занимает одно соединение из пула на всё время жизни. Внутри move нельзя обращаться к db — только к tx; запрос через db уйдёт по другому соединению, не увидит незавершённых изменений и может встать в очередь за самим собой. Долгая транзакция держит соединение и не отдаёт его никому.

Два пишущих: database is locked

У SQLite писатель может быть только один. Проверим: две транзакции с разных подключений к одному файлу.

первая транзакция пишет: ок
вторая транзакция пишет: database is locked (5) (SQLITE_BUSY)
итог: 2

Вторая не стала ждать, а сразу получила отказ. Первая спокойно записала своё, +100 от второй потерялось — и правильно, её никто не подтверждал.

Читателей при этом может быть сколько угодно. Для блога, где пишет один автор, это не ограничение вовсе; когда пишущих станет много, это будет причина переехать на Postgres, где транзакции не мешают друг другу так грубо.

Карта урока

Скажите своими словами

Не подглядывая, ответьте вслух или на бумаге. Ответы — в конце урока.

1. Почему ? защищает от инъекции, хотя ничего не экранирует в вашем коде?
2. Что вернёт order by ? и почему это опаснее ошибки?
3. Зачем defer tx.Rollback() ставят даже там, где дальше будет Commit?

Задание

Обязательное. Найдите в своём блоге все места, где значение попадает в запрос через fmt.Sprintf или +, и переведите их на ?. Затем сделайте список статей сортируемым: /?sort=title и /?sort=updated, имя колонки — через белый список, неизвестное значение молча даёт сортировку по умолчанию.

Сортировка уже сделана в step-17 — сверьтесь с ним после того, как напишете сами.

По желанию.

• Заведите таблицу deletions и удаляйте статью вместе с записью в неё — одной транзакцией. Сломайте вторую половину нарочно и убедитесь, что статья осталась.
• Напишите тест, который передаёт в Get адрес x' or '1'='1 и требует ErrNotFound. Такой тест ловит возврат склейки при следующей правке.
• Замерьте, что будет, если внутри транзакции обратиться к db вместо tx.

Куда это встанет в блоге

В блоге склеек нет — параметры стояли с самого первого запроса. Зато появляется сортировка списка, и это первое место, где имя колонки приходит снаружи.

Долги. Транзакции пока некуда приложить всерьёз: по-настоящему они понадобятся в уроке про теги, где одна статья пишется сразу в две таблицы. И Update по-прежнему пишет все поля разом.

Ответы

Источники

• SQLite: SQL-инъекции и параметры
• Go: пакет database/sql