Параметризованные запросы
До сих пор входные значения были записаны прямо в учебном SQL. В веб-приложении slug приходит от пользователя, поэтому нельзя вставлять его символы в исходник запроса. Нужно сохранить заранее выбранную структуру SQL и передать искомое значение отдельно. Параметризация решает именно эту задачу; она не выбирает за приложение, какие записи разрешено показывать.
В снимке catalog-db/lesson-09 из архива серии находится небольшой клиент Python. Зафиксированы Python 3.12, Psycopg 3.3.6 и PostgreSQL 17.11. Зависимость записана в отдельном requirements.txt. Программа, установка пакета и SQL не выполнялись. Если захотите повторить пример позже, сначала восстановите отдельную учебную базу по README, затем передайте её параметры через PROFESSORWEB_LAB_DSN.
Текст запроса и значение
В Psycopg обычный маркер значения записывается как %s. Он одинаков для строки и числа: тип значения передаётся драйверу вместе с объектом Python. Маркер не нужно заключать в SQL-кавычки и нельзя заполнять Python-операцией %. Правила приведены в документации передачи параметров.
Вот полный файл read_catalog.py:
import os
import psycopg
slug = input('Slug курса: ').strip()
query = """
SELECT id, slug, title
FROM catalog.courses
WHERE slug = %s AND status = %s
"""
with psycopg.connect(os.environ['PROFESSORWEB_LAB_DSN'], autocommit=True) as conn:
if conn.execute('SELECT current_database()').fetchone()[0] != 'professorweb_lab':
raise RuntimeError('Expected isolated professorweb_lab database')
rows = conn.execute(query, (slug, 'published')).fetchall()
if not rows:
print('Опубликованный курс не найден')
for course_id, code, title in rows:
print(course_id, code, title)При вводе js-browser ожидается строка с ключом два, кодом и названием «JavaScript в браузере». При вводе web-performance ожидается сообщение об отсутствии опубликованного курса. Запись существует, но условие статуса её не допускает в этот результат. Такая проверка основана на принятом договоре публичного каталога, а не на попытке определить публикацию по заполненности названия.
strip() удаляет окружающие пробелы пользовательского ввода, но не является защитой структуры SQL. За структуру отвечает отдельная передача параметров. Можно удалить эту нормализацию, и значение с пробелами просто не совпадёт со slug; оно всё равно останется значением. Различайте удобство ввода и механизм обработки запроса, чтобы случайно не заменить одно другим.
Переменная окружения содержит личную строку соединения. Её не печатают в вывод и не включают в архив. Проверка имени базы добавляет ограничение, однако, как объяснялось в уроке подключения, не отличает два сервера с одинаковой базой автоматически. Изоляция экземпляра остаётся предварительным условием ручного повторения.
Значение, похожее на SQL
Теперь рассмотрим ввод js-browser' OR true --. В таблице нет такого полного кода. Ожидается сообщение «Опубликованный курс не найден». Кавычка, условие и комментарий не меняют заранее заданный текст запроса: они входят в искомое значение. Мы не удаляли отдельные слова и не пытались самостоятельно угадывать все способы записи SQL.
Если бы приложение собрало запрос f-строкой, значение оказалось бы внутри программы. Тогда граница между данными и операторами зависела бы от переданных символов. Исправление не сводится к запрету одной кавычки: легальные данные тоже способны содержать специальные знаки. Правильный договор — передавать значения через предусмотренный механизм драйвера.
Это также не обещает, что произвольный запрос автоматически становится разумным. Пользователь может передать очень длинную строку, приложение может выбрать слишком широкую выборку или не проверить владельца объекта. Параметризация сохраняет структуру команды, а ресурсные ограничения и доступ обсуждаются независимо. В этом клиенте читается не более одного курса благодаря уникальному slug.
Два значения упакованы в кортеж. Для одного параметра понадобилась бы запись (slug,) с запятой: круглые скобки сами по себе не превращают строку в одноэлементный кортеж. Такие небольшие детали важны, потому что драйвер должен получить последовательность значений, соответствующую числу маркеров. Ошибка упаковки не означает поломку базы или неправильный slug.
Параметр не обозначает колонку
Если пользователь выбирает сортировку, нельзя заменить имя колонки маркером %s. Маркер обозначает значение, а имя SQL-объекта принадлежит структуре запроса. Для сортировки удобнее разрешить несколько заранее выбранных вариантов и собрать структуру средствами драйвера.
Следующий фрагмент — отдельный вариант построения запроса, не замена работающего точного поиска в файле:
from psycopg import sql
allowed = {'name': 'title', 'code': 'slug'}
choice = 'name'
column = allowed[choice]
ordered_query = sql.SQL(
'SELECT id, slug, title FROM catalog.courses '
'WHERE status = %s ORDER BY {}, id'
).format(sql.Identifier(column))Значение choice должно пройти выбор из разрешённого набора приложения. Identifier корректно представляет имя колонки, а статус по-прежнему передаётся отдельным параметром при выполнении. Название и код являются разрешёнными признаками сортировки; пользователь не получает возможность дописать произвольный оператор. Механизм композиции описан в документации Psycopg SQL.
Отдельный второй ключ id задаёт определённый порядок при одинаковых названиях. Направление сортировки здесь фиксировано, потому что оно тоже относится к структуре. Если добавить выбор направления, его следует представить отдельным разрешённым вариантом, а не приклеивать необработанную пользовательскую строку к SQL.
Жизнь небольшого клиента
Контекст соединения закрывает его после выхода из блока. В данном чтении включён autocommit=True, чтобы простая выборка не оставляла открытой обычную транзакцию до последующего решения приложения. Такой выбор подходит этому маленькому клиенту с независимыми чтениями; для связанных изменений понадобятся явные границы транзакции. Поведение контекста и соединения описано в базовом руководстве Psycopg.
Программа не является production-обработчиком запросов. При отсутствии переменной окружения или ошибке подключения она завершится исключением. В реальном приложении нужно определить понятный ответ, журналирование без секретов и корректный жизненный цикл ресурсов. Добавлять такое окружение в первый пример параметров было бы отдельной задачей, поэтому его границы обозначены явно.
Параметр и значение оператора
Точная параметризация не делает все операторы одинаковыми. В нашем запросе используется равенство, поэтому строка целиком сравнивается со slug. Если заменить его на LIKE, символы % и _ внутри параметра получат значение шаблона поиска. Это не изменение структуры SQL, но уже иной договор сопоставления. Для поиска буквального процента нужно отдельно определить представление шаблона и его экранирование по правилам оператора.
Такой пример помогает не смешивать SQL-инъекцию и слишком широкую выборку. Параметр остаётся данными, однако приложение могло законно попросить все строки по шаблону. Если пользовательский поиск должен быть ограничен, проверяют длину ввода, доступные операторы и размер ответа. Безопасная передача значения не выбирает эти границы самостоятельно.
В текущем read_catalog.py уникальный slug и равенство ограничивают результат одной записью. Пустой ввод после strip не совпадёт ни с одним допустимым кодом. Можно заранее показать подсказку «Введите код курса», чтобы избежать лишнего обмена; это улучшение пользовательского сценария, а не необходимая часть параметризации.
Зависимость Psycopg устанавливается только в отдельное окружение будущего ручного примера. Она не добавляется в зависимости сборки статического ProfessorWeb: генератор страниц не начинает обращаться к PostgreSQL после появления этой статьи. Различайте инструменты учебного клиента и инструменты самого сайта, иначе пример незаметно превратится в ненужную инфраструктурную зависимость.
В библиотеке уже есть урок параметров для SQLite. Идея разделения структуры и значения общая, но маркеры драйверов отличаются: знак вопроса из SQLite нельзя механически перенести в обычный Psycopg. Теперь вы можете объяснить не только безопасную передачу slug, но и причину, по которой выбор поля сортировки требует другого представления.