Немного о связываемых переменных (prepared statements) / Habr

Если бы мне пришлось писать книгу о том, как создавать немасштабируемые приложения Oracle, первая и единственная ее глава называлась бы «Не используйте связываемые переменные».
Том Кайт, вице-президент Oracle

Недавно на Хабре появилась статья от AlexanderPHP «SQL injection для начинающих. Часть 1». По ее содержимому и комментарием к ней может создаться впечатление, что у многих разработчиков нет понятия, что такое связываемые переменные, зачем ими следует пользоваться и какие преимущества они дают. Попытаюсь в данной статье пролить небольшой свет на данные вопросы.

Определение связываемых переменных

Связываемые переменные, они же prepared statements, они же подготовленные выражения (четко устоявшегося перевода обнаружить не удалось; будем использовать и тот, и тот) — это часть функциональности SQL-баз данных, предназначенная для отделения данных запроса и собственно выполняемого SQL-запроса. Например, у нас есть запрос:
insert into someTable(name) values(‘Вася’);
Что мы можем заметить, просто посмотрев на него? Во-первых, сам запрос insert обычно статичен и не меняется в разных запросах, в 90% случаев просто жестко вбит в коде или генерируется при помощи некоторого ORM; значение данных (в данном случае 'Вася') меняется постоянно и задается извне — из ввода пользователя или из других источников. Связываемые переменные позволяют задать запрос отдельно, а потом передавать данные в него отдельно, приблизительно так (псевдокод):

$request = sql_prepare('insert into table(name) values(:1)');
/*также можно insert into someTable(name) values(?);*/
sql_execute($request, Array('Вася'));

Так мы отдельно задаем запрос, вместо данных подставляя в него номера связываемых переменных (:1, :2,...) или просто вопросительные знаки. Далее вызываем запрос, указывая, какие именно данные надо подставить вместо указанных переменных.
Результат выполнения этого кода полностью аналогичен результату выполнения запроса insert into someTable(name) values(‘Вася’);, но есть несколько важных отличий, которые будут рассмотрены далее.

Преимущества и особенности связываемых переменных

При использовании связываемых переменных есть несколько преимуществ:
1. Очевидное преимущество — один и тот же подготовленный запрос можно использовать несколько раз для разных данных, тем самым сокращая код.
2. З��просы со связываемыми переменными лучше кэшируются сервером, сокращая время синтаксического разбора.
3. Запросы со связываемыми переменными обладают готовой встроенной защитой от SQL-инъекций.
Рассмотрим каждый пункт подробнее.
Первый пункт очевиден — при наборе данных можно использовать одно и тоже подготовленное выражение несколько раз:

$request = sql_prepare('insert into table(name) values(:1)');
sql_execute($request, Array('Вася'));
sql_execute($request, Array('Петя'));
sql_execute($request, Array('Коля'));
sql_execute($request, Array('Иван'));

Код генерации SQL-запроса сокращается, а вам любой разработчик скажет, что сокращение объемов кода — это сокращение вероятности ошибки в нем.

Для пояснения второго пункта следует рассказать подробнее, как именно сервер баз данных обрабатывает SQL-запрос. Первейшим этапом выполнения запроса является синтаксический разбор самого запроса, то есть сервер переводит запрос из SQL-языка в какой-то свой внутренний формат, чтобы определить, что именно хочет от сервера клиент. За синтаксическим разбором следует собственно выполнение — составление плана запроса, формирование индексов, сканирование таблиц и множество других неинтересных вещей. Надо отметить, что сам по себе синтаксический разбор — операция довольно «тяжелая» по времени выполнения (хотя бы по сравнению с поиском по индексу, например). Подавляющее большинство современных систем управления базами данных (увы, насколько я знаю, MySQL в данном случае к таковым не относится), «умеют» кэшировать результаты синтаксического разбора и заново использовать их. В этом случае становится очень выгодным, если есть возможность повторять один и тот же SQL-запрос не один раз — будет использоваться синтаксический кэш. Обратимся к примеру в пункте 1 — очевидно, что в данном случае синтаксический разбор выполняется один раз, хотя сам запрос — четыре раза. Если бы мы писали:

sql_execute("insert into table(name) values('Вася')");
sql_execute("insert into table(name) values('Петя')");
sql_execute("insert into table(name) values('Коля')");
sql_execute("insert into table(name) values('Иван')");

то в этом случае каждый раз запрос для сервера был бы новым (потому что анализируется полный текст запроса), и синтаксический разбор пришлось бы выполнить четырежды. Это еще не говоря о том, что такие запросы забивают «мусором» описанный синтаксический кэш.

Перейдем к третьему пункту. Почему же связываемые переменные — это гарантированная защита от SQL-инъекций (по крайней мере, того типа, который рассматривается в упомянутой статье)? Существует заблуждение (у меня оно точно было), что prepared statements – это просто синтаксическая «нашлепка» на команду sql_execute (mysql_real_query, например), которая просто
экранирует все указанные переменные, собирает в одну строку и просто вызывает команду sql_execute, избавляя программиста от некоторого ручного труда. Это не так. На самом деле prepared statement – отдельная возможность в любой вменяемой СУБД. Для этой возможности есть отдельные функции в библиотеке, отдельные места в бинарном протоколе между клиентской и серверной частью СУБД. Более того, собственно подготовленный запрос и данные, которые в нем используются, передаются на сервер отдельно. В клиентских библиотеках есть отдельные команды подготовки выражений (для примера можно посмотреть документацию MySQL C API, PostgreSQL C library).

Примечание: есть исключение — в PHP PDO связываемые переменные по умолчанию эмулируются именно описанным методом, то есть конструированием SQL-команды на клиентской стороне. Это лучше отключать (взято со StackOverflow):
$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');
$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Из этого следует важный вывод — поскольку данные передаются полностью отдельно от запроса, у этих данных нет никаких возможностей модифицировать запрос. Нет возможностей. Вообще никаких. (Экзотические атаки типа переполнения буфера здесь мы не рассматриваем — это совсем другой класс атак).
Данные не нужно экранировать, преобразовывать или как-то менять; они идут в базу данных в точно том виде, в каком нужны нам. Если нам передали строку Robert');drop table students;, не надо заботиться об экранировании, надо просто передать ее как связываемую переменную — ничего она нам не сделает, а так и будет просто лежать в базе данных, как самая обычная строка.

Комментарии к комментариям

В заключение рассмотрим несколько комментариев из уже упомянутой статьи и разберем, что же в них не так:

Rhaps107
а в чем проблема с mysql_real_escape_string? В нём есть какие-то известные уязвимости?

Это мы уже разобрали — проблема с функцией mysql_real_escape_string в том, что ей вообще пользуются. Со связываемыми переменными ей не надо пользоваться. Это экономия на клиентской части (представьте, что функции надо «шерстить» мегабайтную строку, чтобы найти места, где все-таки поставить обратный слэш), а остальные преимущества уже расписаны в статье.

@m_z21
PDO и ORM не панацея. И с использованием pdo можно наделать подобных дыр, если нет понимания как работают sql-инъекции.

Каким боком сюда приплели ORM – непонятно. А вот PDO (и MySQLi) как раз панацея, поскольку SQL injection при их грамотном использовании невозможны, как уже и было описано.

@VolCh21
Выигрыш по потребляемым ресурсам (скорости, памяти), т. к. mysql_* является по сути просто биндингами к libmysql, а mysqli/pdo создают ненужный во многих случаях объектный слой?

И это фактическая ошибка. Команды типа mysqli::prepare — это тоже всего лишь биндинги к соответствующим функциям клиентской библиотеки MySQL. Если желаете убедиться, то можете сами посмотреть на исходные коды PHP. Соответственно, расходы на (якобы ненужный) объектный слой даже если и есть, то они минимальные. Да и экономия на объектном слое уж очень сильно напоминает «экономию на спичках».

Заключение

Надеюсь, мне удалось прояснить для кого-то такую несомненно важную тему, как связываемые переменные (prepared statements). Надеюсь, что многие хотя бы задумаются над тем, чтобы всегда использовать связываемые переменные при работе с БД. Я не претендую на абсолютную полноту и точность изложения, так что буду только рад, если у кого-то найдется что добавить, убавить или откорректировать в написанном.

1. http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php — очень полезные ответы по теме на StackOverflow.
2. https://www.owasp.org/index.php/Preventing_SQL_Injection_in_Java — все о том же в контексте Java.
3. Том Кайт. Oracle для профессионалов. — более подробно от суперпрофессионала для просто профессионалов.