---
tags: [практикум, слушателю, БРПО, SQL, SQLite, CWE-89]
курс: М БРПО — Специалист по процессам разработки безопасного ПО
занятие: Безопасный реестр программных компонентов
---

# Практические задания

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

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

В этом тексте нет готовой реализации `step3_student.py`: критерии поведения открыты, но выбор конструкции и написание кода остаются за вами. Публичный архив для самостоятельного повторения содержит `step3_student_solution.py`; открывайте его только **после собственной попытки**, зафиксировав исходный RED и свой результат. В оцениваемом занятии преподаватель выдаёт комплект без файла решения до завершения задания.

## Правила безопасной работы

1. Используйте только вымышленные учебные данные из комплекта.
2. Не вводите реальные пароли, токены, персональные данные и сведения о компонентах закрытого продукта.
3. `demo_unsafe_query.py` намеренно содержит уязвимый шаблон. Запускайте его только как локальную демонстрацию и не переносите код из него в проекты.
4. В самостоятельной части изменяйте только `step3_student.py`.
5. Не изменяйте `schema.sql`, `seed.sql` и `test_student.py`: иначе результат тестирования перестанет быть независимым свидетельством.
6. Не удаляйте существующую базу автоматически. `step1_schema.py` не перезаписывает файл; для повторного запуска выберите другое имя.

## Подготовка рабочего места

Все команды ниже приведены для PowerShell и выполняются из корня комплекта.

```powershell
cd code
py --version
py -c "import sqlite3; print(sqlite3.sqlite_version)"
Get-ChildItem .\schema.sql,.\seed.sql,.\step1_schema.py,.\demo_unsafe_query.py,.\step2_secure_queries.py,.\step3_student.py,.\test_student.py
```

Требуется Python 3.11 или новее со SQLite, поддерживающим таблицы `STRICT`. Дополнительные пакеты не нужны: используются стандартные модули `sqlite3`, `pathlib` и `unittest`.

## Практика 1. Схема данных как мера безопасности

**Время:** 13:30–15:00, 90 минут.

**Цель:** связать требования к реестру с ограничениями SQLite и получить воспроизводимую учебную базу.

## План времени

| Минуты | Работа |
|---|---|
| 0–10 | проверка окружения и состава файлов |
| 10–30 | чтение требований и модели данных |
| 30–50 | разбор `schema.sql` и `seed.sql` |
| 50–65 | создание базы и проверка результата |
| 65–80 | мини-модель угроз и анализ ограничений |
| 80–90 | фиксация выводов и контрольная точка |

## Шаг 1.1. Прочитайте доверенные SQL-файлы

```powershell
Get-Content .\schema.sql
Get-Content .\seed.sql
```

Для каждой таблицы выпишите:

- назначение;
- первичный ключ;
- обязательные поля;
- ограничения множества значений;
- правило уникальности;
- внешние ключи;
- поведение при удалении связанной записи.

Заполните таблицу:

| Элемент схемы | Какое нежелательное состояние предотвращает | Что дополнительно должен проверить код |
|---|---|---|
| `components` | | |
| `audit_events` | | |
| `NOT NULL` | | |
| `CHECK` | | |
| `UNIQUE` | | |
| `FOREIGN KEY` | | |
| `ON DELETE RESTRICT` | | |
| `STRICT` | | |

## Шаг 1.2. Создайте базу

Первый запуск:

```powershell
py .\step1_schema.py
```

Если `components.db` уже существует, не удаляйте его вслепую. Создайте новый файл:

```powershell
py .\step1_schema.py components-02.db
```

Зафиксируйте фактический путь, количество загруженных записей и сообщения программы.

## Шаг 1.3. Разберите границы доверия

Нарисуйте поток:

```text
пользователь → Python API → sqlite3 → SQLite → файл БД
```

Отметьте:

- где впервые появляются внешние данные;
- где они должны проверяться;
- где данные превращаются в параметры SQL;
- какой слой обеспечивает уникальность;
- какой слой обеспечивает ссылочную целостность;
- где меняются полномочия чтения и записи.

## Шаг 1.4. Сформулируйте требования

Сформулируйте не менее пяти проверяемых требований в виде:

```text
R-__: система должна / не должна ...;
проверка: ...;
артефакт: ... .
```

Обязательно охватите:

- допустимую критичность;
- защиту от дубликатов;
- связь событий аудита с компонентом;
- невозможность незаметного частичного изменения;
- минимальные полномочия для операции чтения.

## Контрольная точка практики 1

- [ ] окружение и версия SQLite записаны;
- [ ] `schema.sql` и `seed.sql` прочитаны;
- [ ] база создана командой `py step1_schema.py`;
- [ ] для каждого ограничения описан защищаемый инвариант;
- [ ] отмечены границы доверия;
- [ ] сформулированы минимум пять проверяемых требований;
- [ ] исходные SQL-файлы не изменены.

## Практика 2. От уязвимого запроса к безопасному API

**Время:** 15:10–16:40, 90 минут.

**Цель:** увидеть CWE-89 на изолированном примере и различить защиту значений, идентификаторов и шаблонов `LIKE`.

## План времени

| Минуты | Работа |
|---|---|
| 0–15 | прогноз поведения и запуск уязвимой демонстрации |
| 15–35 | разбор границы «SQL-код — данные» |
| 35–55 | параметризованный поиск |
| 55–70 | allowlist для сортировки и семантика `LIKE` |
| 70–82 | эксперименты с безопасной версией |
| 82–90 | фиксация требований и выводов |

## Шаг 2.1. Сначала сделайте прогноз

Откройте `demo_unsafe_query.py`, но пока не запускайте:

```powershell
Get-Content .\demo_unsafe_query.py
```

Запишите:

1. где внешнее значение становится частью SQL;
2. какой результат вы ожидаете от уязвимого варианта;
3. какой результат вы ожидаете от параметризованного варианта;
4. почему одинаковая строка данных получает разный смысл.

## Шаг 2.2. Запустите изолированную демонстрацию

```powershell
py .\demo_unsafe_query.py
```

Сопоставьте наблюдение со своим прогнозом. Демонстрация использует фиксированную учебную строку и базу в памяти. Не расширяйте её для работы с произвольным SQL и не подключайте к реальным данным.

Заполните:

| Наблюдение | Нарушенное свойство | CWE | Основная мера |
|---|---|---|---|
| | | | |

## Шаг 2.3. Изучите безопасные операции

```powershell
Get-Content .\step2_secure_queries.py
py .\step2_secure_queries.py
```

Найдите в коде:

- placeholder для значения;
- кортеж параметров;
- ограничение длины и `limit`;
- обработку метасимволов `LIKE`;
- allowlist столбцов сортировки;
- место, где после проверки формируется SQL-идентификатор.

Для каждого элемента ответьте:

1. защищает ли он синтаксис SQL, бизнес-правило или доступность;
2. может ли его заменить параметризация;
3. что произойдёт, если удалить только этот слой.

## Шаг 2.4. Выполните безопасные эксперименты

Обычный поиск и выбор разрешённой сортировки:

```powershell
py .\step2_secure_queries.py --term json --sort license_spdx
```

Строка, похожая на SQL-фрагмент, должна обрабатываться как данные:

```powershell
py .\step2_secure_queries.py --term "' OR 1=1 --" --sort name
```

Метасимвол `LIKE`:

```powershell
py .\step2_secure_queries.py --term "100%" --sort name
```

Для каждого запуска запишите:

| Вход | Ожидаемое свойство безопасности | Фактическое наблюдение | Вывод |
|---|---|---|---|
| `json` | | | |
| строка с `OR 1=1` | | | |
| строка с `%` | | | |

## Шаг 2.5. Разделите три задачи

Заполните матрицу, не копируя готовый код:

| Задача | Пример внешнего ввода | Подход | Почему соседнего подхода недостаточно |
|---|---|---|---|
| передать значение | | | |
| выбрать столбец сортировки | | | |
| искать `%` как обычный символ | | | |
| ограничить объём результата | | | |

## Контрольная точка практики 2

- [ ] прогноз сделан до запуска;
- [ ] `demo_unsafe_query.py` запущен только локально;
- [ ] причина CWE-89 сформулирована через смешение кода и данных;
- [ ] `step2_secure_queries.py` запущен с тремя вариантами ввода;
- [ ] различены параметры, allowlist и экранирование `LIKE`;
- [ ] уязвимый фрагмент не скопирован в рабочий файл.

## Практика 3. Самостоятельная реализация и регрессионные тесты

**Время:** 16:50–18:20, 90 минут.

**Рабочий файл:** `step3_student.py`.

**Проверка:** `test_student.py`.

**Цель:** реализовать операции реестра по открытому контракту, не изменяя критерии приёмки.

## План времени

| Минуты | Работа |
|---|---|
| 0–10 | первый запуск тестов и фиксация RED |
| 10–25 | чтение контракта и план реализации |
| 25–65 | самостоятельная реализация малыми шагами |
| 65–78 | полный прогон и разбор отказов |
| 78–86 | проверка границ и просмотр изменений |
| 86–90 | фиксация GREEN и рефлексия |

## Шаг 3.1. Получите исходную контрольную точку

```powershell
py -m unittest -v test_student.py
```

Не исправляйте тесты под текущую реализацию. Запишите:

- сколько проверок обнаружено;
- какие функции ещё не реализованы;
- какие свойства безопасности проверяются негативными сценариями.

## Шаг 3.2. Изучите контракт

Откройте:

```powershell
Get-Content .\step3_student.py
Get-Content .\test_student.py
```

Реализовать нужно пять операций.

Во всех контрактах «отвергнуть» означает выбросить исключение оператором `raise`, а не вернуть `None` и не напечатать сообщение: возврат `None` или `print` тесты не увидят, и результат останется красным. Тесты требуют конкретные типы исключений:

- `ValueError` — для недопустимых аргументов: пустое имя, неизвестная критичность, недопустимое значение `limit` (допустимый диапазон — от 1 до 100 включительно), неизвестный `sort_by`;
- `LookupError` — для отсутствующего компонента в `change_license`.

Конструкция `with self.assertRaises(ValueError):` в тесте означает: проверка засчитывается, только если код внутри блока `with` выбросил именно `ValueError` или его подкласс. Если исключение не возникло или его тип другой, тест остаётся красным.

Ошибки самого драйвера `sqlite3` (например, если база отклонила запись) не перехватывайте и не заменяйте собственными типами: тесты ожидают, что такое исключение дойдёт до вызывающего кода как есть. Оборачивать нужно только проверки аргументов, перечисленные в контрактах.

### `add_component`

Контракт:

- сохранить все переданные поля;
- вернуть идентификатор добавленной записи;
- отвергнуть пустое имя до выполнения SQL;
- отвергнуть неизвестную критичность;
- воспринимать SQL-подобную строку имени как обычные данные.

### `search_components`

Контракт:

- выполнять регистронезависимый поиск по фрагменту имени (речь о латинице: учебные данные состоят из латинских имён; для кириллицы `LIKE` в SQLite по умолчанию регистрозависим);
- не интерпретировать SQL-подобный текст как команду;
- воспринимать `%` в поисковом терме как обычный символ;
- ограничивать допустимое значение `limit`.

### `list_components`

Контракт:

- сортировать только по явно разрешённым столбцам;
- отвергать неизвестный или SQL-подобный идентификатор сортировки;
- не изменять данные при ошибочном вводе.

### `change_license`

Контракт:

- изменить лицензию существующего компонента;
- записать связанное событие аудита;
- выполнить обе записи атомарно;
- при отсутствии компонента не оставлять событие аудита.

### `open_read_only`

Контракт:

- открыть существующую базу для чтения;
- позволить выполнить `SELECT`;
- не позволить изменить данные через это соединение.

## Шаг 3.3. Работайте короткими циклами

Рекомендуемый цикл:

```text
одна функция → один тематический набор тестов → разбор причины → следующее изменение
```

Команды тематических прогонов:

```powershell
py -m unittest -v test_student.AddComponentTests
py -m unittest -v test_student.SearchTests
py -m unittest -v test_student.OrderingTests
py -m unittest -v test_student.TransactionTests
py -m unittest -v test_student.LeastPrivilegeTests
```

После каждого изменения задавайте себе четыре вопроса:

1. Отделены ли значения от SQL-команды?
2. Если часть SQL должна быть динамической, выбирается ли она из allowlist?
3. Может ли операция оставить частичный результат?
4. Можно ли уменьшить полномочия соединения?

## Шаг 3.4. Выполните полный прогон

```powershell
py -m unittest -v test_student.py
```

Если тест не проходит:

1. прочитайте имя и сообщение теста;
2. воспроизведите только соответствующий тематический набор;
3. сопоставьте отказ с требованием;
4. исправьте причину в `step3_student.py`;
5. снова запустите тематический и затем полный набор.

Не добавляйте специальные ветви, распознающие только тестовые строки. Реализация должна обеспечивать общее свойство.

## Шаг 3.5. Проверьте границы изменений

Если каталог находится под Git:

```powershell
git diff -- .\step3_student.py
git status --short
```

Убедитесь, что вы не изменили:

- `schema.sql`;
- `seed.sql`;
- `demo_unsafe_query.py`;
- `step2_secure_queries.py`;
- `test_student.py`.

Файл локальной базы `components.db` является результатом запуска, а не исходным кодом решения.

## Шаг 3.6. Подготовьте краткое обоснование

Заполните без вставки исходного кода:

| Решение | Какой риск снижает | Где проверяется | Связь с процессом |
|---|---|---|---|
| проверка входных полей | | | |
| параметры SQL | | | |
| allowlist сортировки | | | |
| обработка `LIKE` | | | |
| транзакция | | | |
| режим только для чтения | | | |

Для столбца «Связь с процессом» используйте применимые номера: 5.6, 5.7, 5.8, 5.10, 5.18. Один элемент может относиться к нескольким процессам, но связь нужно объяснить.

## Итоговая контрольная точка практики 3

- [ ] первый результат RED зафиксирован;
- [ ] изменялся только `step3_student.py`;
- [ ] все пять операций реализованы по контракту;
- [ ] тематические наборы тестов пройдены;
- [ ] полный `py -m unittest -v test_student.py` завершён успешно;
- [ ] SQL-подобные строки остаются данными;
- [ ] связанное изменение и аудит атомарны;
- [ ] соединение только для чтения действительно не записывает;
- [ ] таблица обоснования заполнена;
- [ ] готовое решение не взято из преподавательских материалов.

## Если что-то пошло не так

Ниже собраны сообщения, которые встречаются на этом практикуме чаще всего. Ищите свой случай по тексту сообщения: большинство ситуаций — не поломка комплекта, а ожидаемое поведение.

### Команда `py` не найдена или открывается Microsoft Store

Признак: PowerShell сообщает, что имя `py` не распознано, либо вместо запуска Python открывается окно Microsoft Store.

```text
py : Имя "py" не распознано как имя командлета, функции, файла сценария
или выполняемой программы.
```

Причина: у старых пакетов Python из Microsoft Store (3.13 и старше) нет лаунчера `py`, на котором построены команды комплекта; современный Python Install Manager (Python 3.14 и новее) устанавливает обе команды. Проверьте обе команды: `py --version` и `python --version` — работайте той, которая отвечает. Все команды дня можно выполнять и через `python`: например, `python .\step1_schema.py` и `python -m unittest -v test_student.py`. Надёжнее установить свежий Python с python.org — порядок описан в файле `ЧИТАТЬ-ПЕРВЫМ.md`, раздел «Если Python ещё не установлен».

### `sqlite3.OperationalError: near "STRICT": syntax error`

```text
sqlite3.OperationalError: near "STRICT": syntax error
```

Причина: подключённая библиотека SQLite не знает ключевого слова `STRICT` — оно появилось только в SQLite 3.37.0. Так бывает при Python ниже версии 3.11. Проверьте обе версии:

```powershell
py --version
py -c "import sqlite3; print(sqlite3.sqlite_version)"
```

Если вторая команда печатает версию ниже 3.37.0 (например, `3.35.5`), установите Python 3.11 или новее с python.org — вместе с ним обновится и встроенный SQLite.

### `sqlite3.IntegrityError: CHECK constraint failed` при экспериментах

```text
sqlite3.IntegrityError: CHECK constraint failed: ...
```

Это не поломка, а штатная работа ограничения схемы: вы попытались записать значение, которое `CHECK` запрещает, например критичность вне множества `low`, `medium`, `high`. Текст после двоеточия подсказывает, какое ограничение сработало: в новых версиях SQLite это само выражение `CHECK`, в более старых — имя таблицы. Запись в базу не попала — именно это нежелательное состояние ограничение и предотвращает. Исправьте значение и повторите операцию.

### `NotImplementedError` при первом прогоне `test_student.py`

```text
ERROR: test_add_component_persists_all_fields (test_student.AddComponentTests.test_add_component_persists_all_fields)
...
NotImplementedError: Реализуйте add_component
...
Ran 14 tests in 0.05s

FAILED (errors=14)
```

Это ожидаемая стартовая точка RED: функции в `step3_student.py` ещё не реализованы и намеренно выбрасывают `NotImplementedError`. 14 ошибок при первом запуске — норма. Зафиксируйте этот результат как исходную контрольную точку и двигайтесь по циклу из шага 3.3.

### FAIL и ERROR в выводе unittest — разные события

- `FAIL` — тест выполнился до конца, но проверка не сошлась: код вернул не тот результат, который требует контракт;
- `ERROR` — код упал с исключением раньше, чем проверка успела выполниться.

Мини-пример вывода:

```text
FAIL: test_search_treats_percent_as_literal_character (test_student.SearchTests.test_search_treats_percent_as_literal_character)
AssertionError: Lists differ: ['codec100%', 'codec1000'] != ['codec100%']

ERROR: test_change_license_updates_component_and_adds_audit_event (test_student.TransactionTests.test_change_license_updates_component_and_adds_audit_event)
NotImplementedError: Реализуйте change_license
```

В первом случае функция работает, но возвращает лишнюю запись; во втором — функция ещё не написана или падает с исключением.

### `sqlite3.ProgrammingError: Incorrect number of bindings`

```text
sqlite3.ProgrammingError: Incorrect number of bindings supplied.
The current statement uses 1, and there are 11 supplied.
```

Типичная причина — забытая запятая в кортеже из одного элемента. Запись `(value)` без запятой — это не кортеж, а просто значение в скобках: драйвер получает строку, воспринимает каждый её символ как отдельный параметр и сообщает о неверном числе параметров. Правильная форма — `(value,)` с запятой.

## Итог дня

Ответьте письменно:

1. Какое решение сегодняшнего дня относится к архитектуре, а не только к кодированию?
2. Почему ограничение схемы не отменяет проверку в приложении?
3. Почему параметризовать `ORDER BY ?` нельзя и какой подход используется вместо этого?
4. Чем тест на SQL-подобный ввод отличается от статического анализа исходного кода?
5. Какие дополнительные меры потребуются, чтобы превратить учебный реестр в промышленный контур SCA/SBOM?
6. Какие артефакты сегодняшней работы можно предъявить по процессам 5.6, 5.7, 5.8, 5.10 и 5.18, а каких ещё не хватает?

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