Если бы мне пришлось писать книгу о том, как создавать немасштабируемые приложения 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 для профессионалов. — более подробно от суперпрофессионала для просто профессионалов.
Автор: viktorious
