SQL-запросы с параметрами

Название заметки теперь выводится в правильном контексте, а браузер получает ограниченную политику ресурсов. Следующая граница находится на сервере: приложение обращается к базе. Если соединить пользовательскую строку с исходником SQL, значение может изменить структуру запроса.

Результатом урока станет параметризованный запрос SQLite и понимание его границ. Мы продолжим использовать заметки 1 и 2, но самостоятельную демонстрацию выполним в базе памяти. В заключительном приложении те же правила будут применены к локальному файлу данных.

Структура и значения

SQL-запрос описывает, какие столбцы и строки нужно выбрать. Пользовательское значение должно занимать предусмотренное место внутри этой структуры. Для поиска названия это строка сравнения, а не кусок программы после WHERE.

Неправильный приём выглядит как сборка команды через f-строку. Даже если сейчас браузер передаёт привычное название, следующий запрос может содержать кавычку. Запрет одного символа плохо выражает задачу: корректное название тоже может его включать, а места сборки команд со временем множатся.

SQLite принимает параметры отдельно от текста SQL. Драйвер отвечает за представление значения. Это не ручное экранирование и не проверка владельца. Параметризация сохраняет структуру запроса; выбор разрешённых строк остаётся обязанностью приложения.

Небольшая база

Ниже полный самостоятельный пример. Он создаёт две прежние записи и выполняет поиск по точному названию. Соединение существует только в памяти текущего процесса.

import sqlite3

db = sqlite3.connect(":memory:")
db.row_factory = sqlite3.Row
db.execute("""
    CREATE TABLE notes (
        id INTEGER PRIMARY KEY,
        owner_id INTEGER NOT NULL,
        title TEXT NOT NULL,
        text TEXT NOT NULL
    )
""")
db.executemany(
    "INSERT INTO notes VALUES (?, ?, ?, ?)",
    [
        (1, 1, "План Markdown", "Отделим текст от шаблона."),
        (2, 2, "Проверка выпуска", "Сверим старый адрес."),
    ],
)

def by_title(value):
    return db.execute(
        "SELECT id, title FROM notes WHERE title = ?",
        (value,),
    ).fetchall()

for row in by_title("План Markdown"):
    print(row["id"], row["title"])
db.close()

Ожидается строка с номером 1 и названием плана. Кортеж из одного значения содержит запятую. Без неё круглые скобки не создают одноэлементный кортеж, и драйвер может получить неподходящий набор параметров.

Метод executemany применяет одинаковую структуру к нескольким наборам данных. Это удобно для учебной подготовки, но не означает, что большие внешние массивы можно принимать без ограничения. Размер входа и число операций также являются ресурсным договором.

Значение с кавычкой

До закрытия соединения вызовите by_title со строкой, содержащей SQL-подобные символы. Для наблюдения достаточно ' OR 1=1 --. В нашем параметризованном варианте это целиком искомое название.

Ожидается отсутствие записей: ни одна из двух заметок так не называется. Строка не превращается в дополнительное условие. Этот результат показывает правильное разделение программы и значения; он не зависит от попытки удалить отдельные слова.

Не добавляйте такой учебный ввод к настоящему внешнему сервису. Здесь поведение рассматривается на известной базе памяти и собственном обработчике. Состав данных и запроса полностью виден читателю.

Назначение placeholders описано в документации sqlite3. В заключительном приложении используем тот же договор для номера заметки, пользователя и сохранённого текста. Различается значение, но не принцип передачи.

Поля сортировки

Параметр не выбирает имя столбца вместо программы. Запрос ORDER BY ? с переданным title не становится правильной сортировкой по этому столбцу. Структурные части нужно выбирать из заранее известных вариантов.

Например, интерфейс может поддерживать порядок по номеру и по названию. Словарь связывает короткое имя режима с постоянным SQL-фрагментом:

ORDERS = {"id": "id ASC", "title": "title ASC, id ASC"}
mode = "title"
order_sql = ORDERS.get(mode)
if order_sql is None:
    raise ValueError("Неизвестный порядок")
rows = db.execute(
    "SELECT id, title FROM notes ORDER BY " + order_sql
).fetchall()

Этот фрагмент выполняется до db.close в предыдущем примере. Пользовательское имя не вставляется напрямую: сначала выбирается известное выражение. Номер в качестве второго ключа делает порядок одинаковых названий устойчивым.

Такая таблица не подходит для произвольного редактора SQL. Наш продукт не предоставляет эту функцию, поэтому диапазон вариантов мал. Появление сложного конструктора потребовало бы отдельного разбора структуры и полномочий.

Поиск с LIKE

Для частичного названия можно использовать LIKE с параметром. При этом процент и подчёркивание внутри значения имеют значение шаблона. Параметризация устраняет изменение SQL-структуры, но не меняет смысл оператора LIKE.

Если интерфейс обещает буквальную подстроку, нужно экранировать специальные символы шаблона и определить ESCAPE. Это отдельное правило поиска. Если продукт специально разрешает шаблон, объясните его пользователю и ограничьте размер запроса. Не смешивайте эту семантику с SQL-инъекцией.

Выбор запросов также влияет на время выполнения. Длинный перебор всех названий может быть дорогим без подходящего индекса. Безопасный по структуре запрос не становится автоматически дешёвым. Позднее ограничим вход и операции сервиса.

Данные и полномочия

Запрос заметки по id с параметром ещё может вернуть чужую запись. В нём отсутствует условие владельца. Поэтому урок об авторизации добавит второе значение — ID текущего пользователя, полученный из проверенной сессии.

Не берите owner_id из формы для определения права изменения. Это утверждение отправителя о самом себе, а не доверенный контекст. База должна выбирать объект по условиям, заданным сервером.

Запись и транзакция

Параметры сохраняют структуру отдельного запроса, но не определяют момент фиксации данных. В заключительном приложении изменение завершается commit только после проверок. Если исключение возникло раньше, соединение не должно оставлять частично принятую операцию как успешную заметку.

Для нескольких связанных SQL-действий нужна известная транзакционная граница. Например, изменение текста и запись служебной версии должны соответствовать одной операции. Параметризация каждого выражения сама по себе не делает две операции согласованными при сбое между ними.

Права соединения и расположение файла базы также имеют значение. Если база лежит в публичной папке, посетитель сможет получить её другим путём без SQL-запроса. Защита программы обращения не ограничивает отдельный HTTP-доступ к файлу.

Наконец, набор возвращаемых столбцов зависит от назначения ответа. Выбор всех полей пользователя удобен внутренней проверке пароля, но его нельзя целиком сериализовать в API. Подготовленный запрос, допустимые строки и допустимое содержание ответа являются тремя разными условиями.

После самостоятельной демонстрации обязательно закройте соединение. В базе памяти это завершает её состояние; в файловой SQLite закрытие относится к ресурсу процесса. Успешное чтение без ошибок не означает, что соединениями можно бесконечно накапливаться.

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