Pull to refresh

Отчёт о попытке получить заявленную эффективность от prepared statements

Reading time 4 min
Views 4.9K
Update: из заголовка статьи убрано слово «неудачной». Подробности ниже!

Рассказывая в своей статье о типичных заблуждениях, связанных с защитой от SQL инъекций, среди прочих я отметил тот факт, что серверные подготовленные выражения не работают в PHP по заявленному эффективному сценарию — 1 раз prepare(), потом 1000 раз executе().

Ну, то есть, в теории-то они работают — в пределах одного запуска скрипта. Но много ли вы знаете скриптов (написанных профессиональными программистами), которые выполняют кучу одинаковых запросов? Вот я тоже не знаю. Повторяющихся запросов (каких-нибудь множественных апдейтов) — доли процента, а в массе своей запросы уникальные (в пределах одного скрипта).
Соответственно, для нашего уникального запроса сначала выполняется prepare(), потом — execute(), потом скрипт благополучно умирает, чтобы, запустившись для обработки следующего HTTP запроса, заново выполнять prepare()… Как-то не слишком похоже на оптимизацию. Скорее — наоборот.
Как верно заметили в комментариях, я должен был упомянуть исключения в виде консольных скриптов и демонов, которые долго держат соединение с БД. Однако основная масса PHP скриптов всё же работает на фронтенде, умирая после выполнения пары десятков запросов.

Но неужели нет способа как-то закэшировать подготовленный запрос между запусками?

И тут меня осенила идея!

Но оставались ещё некоторые неувязки, но как раз очень удачно подвернулась конференция HPC, на которой собирались выступать люди, которые могли меня проконсультировать. И ожидания меня не обманули — так что, пользуясь случаем, я хочу высказать огромную благодарность Сергею Аверину и Косте Осипову за живое участие и бесценные консультации.

Перейдём же к содержательной части.

Итак, задача:
Мы хотим получить доступ к подготовленному запросу в новом PHP скрипте.

Основных проблем тут две:
  1. prepared statement живёт только в пределах одного соединения, он так устроен. проблема, на первый взгляд, решаемая.
  2. хэндлер подготовленного выражения реализован в виде объекта. Тут без вариантов — передать объект в другой скрипт невозможно

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

Первая проблема решается понятно как — с помощью persistent connection.
Ведь мы обращаемся к тому же самому соединению, что и предыдущий скрипт. То есть, в теории, должны получить доступ ко всему окружению, которое было создано предыдущими обращениями.

Вторая проблема так же разрешилась, причём очень просто.
Ведь в Mysql есть ДВА варианта подготовленных выражений — условно говоря, «бинарные» и «строковые»!
И если первые нам не подходят, то вторые — это именно то, что надо!

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

А вот «строковые», или «SQL prepared statements» — это обычные SQL запросы, который приводятся в документации в качестве примеров:
PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
SET @a = 3;
SET @b = 4;
EXECUTE stmt1 USING @a, @b;

Обычные SQL запросы!
Что мешает нам выполнить prepare в одном скрипте, а execute — в другом? Сказано — сделано:

mysql_pconnect($host,$user,$pass); //важно - это pconnect!

mysql_query("PREPARE stmt2 FROM 'SELECT ?+? AS sum'") or trigger_error(mysql_error());
mysql_query("SET @a = 6") or trigger_error(mysql_error());
mysql_query("SET @b = 8") or trigger_error(mysql_error());
$res = mysql_query("EXECUTE stmt2 USING @a, @b") or trigger_error(mysql_error());
$row = mysql_fetch_array($res);
var_dump($row);

Затем комментируем строчку с PREPARE, запрашиваем страницу ещё раз… Результат не изменился. Оно работает!

То есть, теоретическая возможность использовать принцип «один prepare() — много execute()» существует.
На практике же, увы, применять его не получится. Причины тут, скорее, идеологического порядка, но реализованные в виде технических ограничений.
Собственно, приведённый выше код я сначала пробовал выполнить, используя mysqli.
Но ничего не работало. Полез в интернет читать, и выяснил, что наличие «мусора» оставшегося после предыдущего обращения к тому же соединению — это серьёзный фейл: незакрытые транзакции, не освобождённые блокировки и прочее. По этой причине в mysqli для очистки соединения применяется функция из C API под названием mysql_change_user(). Соответственно, при повторном обращении к тому же соединению из другого скрипта, ни следа от подготовленного ранее запроса, увы, не остаётся.

Учитывая же такие факты, как
  • идея изначально противоречит идеологии постоянных соединений — «persistent connection служит только для экономии на коннекте, во всех остальных аспектах предоставляя клиенту абсолютно чистое соединение, во всём аналогичное новому»
  • постепенное сползание mysql ext к статусу deprecated — т.е. крайняя нежелательность его использования
  • невозможность использовать mysqli_multi_query для отправки всех запросов с данными одним пакетом
  • а так же то, что по словам Кости Осипова SQL prepared statements носят чисто демонстрационный характер и не рекомендованы к использованию на продакшене
следует признать, что затея потерпела фиаско.

Однако я не считаю время потраченным зря.
Я узнал что-то новое, стал лучше разбираться в механизме работы соединений, как в плане технической реализации, так и в плане идеологии. Пообщался с интересными людьми. Плюс, в конце концов — этот ни с чем не сравнимый восторг исследователя, который разрабатывает новую тему. А отрицательный результат — тоже результат, возможность поделиться им, чтобы другие люди не наступили на те же грабли.

Думаю, стоит также поместить небольшой дисклеймер:
В этой заметке prepared statements рассматривались исключительно с точки зрения потенциального увеличения производительности, а не в качестве защиты от инъекций. Тем более что более корректной будет формулировка «защиту обеспечивает плейсхолдер» (который совсем не обязательно должен быть реализован через native prepard statements), а сделать корректное и безопасное формирование запросов SET было бы совсем несложно.

Очень важный апдейт: Благодаря комментарию BVadim выяснилось, что на описанное выше поведение mysqli влияет флаг MYSQLI_NO_CHANGE_USER_ON_PCONNECT. То есть, если указать его при сборке расширения, то prepared запросы будут сохраняться!
Получается, что практических препятствий к применению данной методики не осталось, только идеологические.
Tags:
Hubs:
+26
Comments 157
Comments Comments 157

Articles