Доступ к данным на основе хранимых процедур в веб-приложениях

 АНТОН ГРИШАН

Доступ к данным
на основе хранимых процедур в веб-приложениях

Большинство приложений вынуждено работать с базами данных, общаясь с СУБД на языке SQL-запросов. Иными словами, одни программы на языках высокого уровня составляют другие программы на SQL. Это выглядит привычным – поэтому кажется логичным и удобным, но так ли это на самом деле?

Рассмотрим два способа взаимодействия приложения и базы данных. Первый и на данный момент более популярный способ – генерация SQL-запроса в теле скрипта (здесь и далее приводятся примеры для PHP5 + MySQL 5 с установленным расширением mysqli):

/* Выбрать все города в стране с заданным кодом, динамическая генерация запроса */

$countryCode = 'RU';

$result = mysqli_query ($db, "SELECT Cities.name FROM Countries, Cities WHERE Cities.countryId = Countries.id \

AND Countries.code = '".mysqli_real_escape_string($db, $countryCode)."'");

Второй способ – доступ к данным через хранимые процедуры. Хранимая процедура – объект базы данных, представляющий собой набор скомпилированных SQL-инструкций. Скрипт вызывает процедуру со списком параметров и обрабатывает полученный результат(ы).

Приведенный выше запрос (SELECT Cities.name FROM Countries, Cities WHERE Cities.countryId = Countries.id AND Countries.code = 'RU') можно сохранить в виде процедуры – getCities, с одним входным параметром – код страны (countryCode char(2)).

Приведу пример создания хранимой процедуры:

DELIMITER $$

CREATE PROCEDURE `getCities`(countryCode char(2))

BEGIN

SELECT Cities.name FROM Countries, Cities WHERE Cities.countryId = Countries.id AND Countries.code = countryCode;

END$$

DELIMITER ;

В момент вызова хранимой процедуры СУБД подставляет значение параметра в тело запроса и возвращает результат выборки данных.

Для вызова хранимой процедуры ‘getCities’ на стороне сервера БД необходимо выполнить следующую команду:

CALL `getCities`('RU');

Вызвать процедуру getCities из PHP-приложения можно следующим образом:

/* Выбрать все города в стране с заданным кодом, вызов хранимой процедуры */

$countryCode = 'RU';

mysqli_multi_query($db, "CALL getCities('".mysqli_real_escape_string($db, $countryCode)."');");

 Работа с БД через хранимые процедуры в настоящий момент встречается реже, чем генерация SQL-запроса в теле программы. На мой взгляд, это происходит по следующим причинам:

• наиболее популярная связка для написания веб-приложений – PHP + MySQL, однако только в MySQL 5 стало возможно использовать хранимые процедуры;
• в большинстве пособий по программированию приведены примеры работы с БД, основанные на генерации запроса в теле скрипта, и начинающим программистам сложно отойти от книжных примеров;
• написание приложения с использованием хранимых процедур подразумевает умение программистов работать с хранимыми процедурами или наличие времени для изучения данной технологии (что не всегда допустимо в рамках конкретного проекта).

Преимущества использования хранимых процедур

• Повышение скорости работы БД. Процедуры хранятся в скомпилированном виде, а значит, СУБД не тратит время на компиляцию запроса при каждом его исполнении. Приложению не требуется тратить время на генерацию запроса. Команда для вызова хранимой процедуры значительно короче, чем запрос, содержащийся в теле процедуры, поэтому требуется меньше времени и трафика на передачу команд на сервер БД.
• Большая степень свободы. Хранимые процедуры поддерживают: входные и выходные параметры, локальные переменные, операторы условного ветвления, циклы, вызовы встроенных команд и других процедур, исполнение DDL-операторов. Во многом хранимые процедуры похожи на процедуры языков программирования высокого уровня.
• Упрощение кода приложения. В приложении нет SQL-запросов, а значит, программисту не нужно писать код для их генерации. Для вызова хранимой процедуры необходимо знать только имя и список параметров (аналогично вызову обычных функций/методов в теле приложения). Такой подход сокращает размер кода и улучшает его читабельность, что положительно влияет на качество конечного продукта.
• Защита приложения от изменений структуры БД. В процессе развития проекта может возникнуть необходимость в изменении структуры БД, например, добавить/удалить/переименовать таблицу или столбец. Если приложение генерирует SQL-запросы, то необходимо внести изменения во все фрагменты кода, отвечающие за генерацию запросов. Организация доступа через хранимые процедуры не требует внесения изменений в код приложения до тех пор, пока имя хранимой процедуры и список параметров (а также ожидаемый результат) остаются прежними.
• Снижение количества ошибок и упрощение отладки. Чаще всего ошибки в работе приложения с БД возникают по следующим причинам:
  • приложение использует некорректные значения для генерации SQL-запроса;
  • SQL-выражение некорректно описывает ожидаемый результат (т.е. ошибка в запросе);
  • фрагмент кода приложения, отвечающий за генерацию SQL-запроса, содержит ошибку и не способен правильно построить нужный запрос.
  Если доступ к БД построен на основе хранимых процедур, то:
  • легко узнать, какие значения попадают в хранимую процедуру, достаточно распечатать список аргументов в момент вызова хранимой процедуры;
  • вам не нужно гадать по коду приложения, какой именно запрос должен получиться в том или ином месте программы, достаточно посмотреть на тело хранимой процедуры, что значительно упрощает процесс отладки;
  • этой ошибки возникнуть не может, так как приложение вообще не генерирует SQL-запросов, все запросы находятся в БД в виде хранимых процедур.
• Безопасность. Использование хранимых процедур позволяет значительно снизить угрозу возникновения уязвимости типа SQL-injection. Кроме того, можно устанавливать права доступа к объектам базы данных для каждой хранимой процедуры, что также способствует повышению уровня безопасности приложения.

Трудности работы с хранимыми процедурами

Существует масса преимуществ, говорящих за использование хранимых процедур. Однако не стоит думать, что хранимые процедуры, безусловно, оптимальное решение для любого проекта. Чтобы сделать осмысленный выбор, давайте рассмотрим отрицательные стороны данного метода.

• Проблема совместимости. Если необходимо обеспечить легкую переносимость приложения на максимальное количество СУБД, то, вероятно, стоит отдать предпочтение динамической генерации запросов, так как хранимые процедуры поддерживаются не всеми СУБД.
• Сложность внедрения хранимых процедур в существующий проект. Внедрение хранимых процедур в приложение, использующее динамическую генерацию запросов, приведет к полной реорганизации кода работы с БД. Необходимость такой реорганизации не всегда просто объяснить заказчику.
• Передача сложных типов данных. Иногда в качестве аргумента процедуре требуется передать не просто строку или число, а массив данных (или более сложный объект). В этом случае данные необходимо преобразовать в строку и в таком виде передавать хранимой процедуре, внутри которой происходит обратное преобразование. Построение запроса в теле приложения в этом случае проще.

Особенности работы с хранимыми процедурами

• Хранимые процедуры служат только для доступа к данным (извлечение/обновление/удаление) и ни для чего больше. Использование процедур в иных целях (проверка данных или генерация HTML) является ошибкой.
• Процедура может вернуть более одного результата. В коде вызова хранимой процедуры необходимо делать итерацию по всем возвращаемым результатам и обрабатывать каждый из них в отдельности.
• Достаточно сложно передать в хранимую процедуру массив значений. Наиболее популярным решением является передача массива в хранимую процедуру в виде строки, содержащей элементы массива, разделенные специальным символом (вертикальная черта – «|»), далее параметр анализируется в теле хранимой процедуры.
• Если в теле хранимой процедуры необходимо динамически генерировать SQL-запрос (старайтесь всеми возможными способами избегать написания подобных процедур), не забывайте экранировать кавычки и спецсимволы во всех переданных в процедуру параметрах, участвующих в построении запроса, иначе процедура будет содержать потенциальную уязвимость типа SQL-injection.

Пример класса для работы с БД через хранимые процедуры

Как мы уже заметили, хранимые процедуры очень похожи на функции, наш класс будет реализовывать прозрачную работу с процедурами таким образом, чтобы с точки зрения приложения не было разницы между обычными функциями и хранимыми процедурами.

Чтобы стало понятно, о чем идет речь, начнем с примера:

<?php

include 'DBaccess.php';

// Создаем объект доступа к БД

$db = new DBaccess('main_db' , 'localhost', 3306, 'login', 'password', 'utf8');

// Вызываем хранимую процедуру GetCities и передаем в качестве параметра RU

$result = $db->getCities('RU');

// Обрабатываем полученный результат (массив данных)

var_dump($result);

?>

В приведенном выше примере для вызова хранимой процедуры getCities с параметром RU (код страны) используется объект класса DBaccess. С точки зрения приложения такой вызов процедуры выглядит как вызов обыкновенного метода, что позволяет отказаться от логики написания программы, которая в свою очередь составляет другую программу на SQL. Приведу далее код класса DBaccess (для работы класса требуется PHP5 c mysqli расширением, MySQL 5):

<?php

class DBConnection {

    private $db = null;

    public function __construct($dbName, $host, $port, $login, $password, $charset) {

        $this->db = new mysqli($host, $login, $password, $dbName, $port);

        $this->db->set_charset($charset);

    }

    public function __call($storeProcedureName, $params) {

        $quotedParams = array();

        foreach($params as $param)    {

           array_push($quotedParams, $param === null ? 'NULL' : '\''.$this->db->escape_string($param).'\'');

           }

        $sql = 'CALL `'.$storeProcedureName.'`('.implode(',', $quotedParams).');';

        $this->db->multi_query($sql);

        $results = array();

           do {

           if ($result = $this->db->store_result()) {

               $rows = array();

               while ($row = $result->fetch_assoc()) {

                 array_push($rows, $row);

               }

               $result->close();

               array_push($results, $rows);

                 }

        } while ($this->db->more_results() && $this->db->next_result());

    return($results);

    }

}

?>

Внутри класса DBaccess не объявлен метод getCities, однако мы можем вызывать хранимую процедуру как «$db->getCities(‘RU’);».

Это достигается за счет использования магического метода __call() (данная возможность появилась в PHP5), который работает следующим образом: при вызове метода, не объявленного в классе, имя вызываемого метода и список аргументов передается в __call($metodName, $params) для обработки (если метод объявлен).

Таким образом, мы можем вызывать любую хранимую процедуру на сервере, например «$db->loadUser($email, $password);».

В приведенном выше коде класса DBaccess отсутствуют необходимые проверки на ошибки, возможность работы с несколькими БД, проверка стабильности соединения с сервером, автоматическое переподключение и множество других полезных функций. Это сделано намеренно, дабы проиллюстрировать основную идею с использованием минимального количества кода.

Скачать полнофункциональную версию класса для изучения и использования можно здесь: http://www.vipidn.com/dbaccess.zip.

1. Полнофункциональная версия класса «DBaccess» – http://www.vipidn.com/dbaccess.zip.
2. Описание магического метода __call() – http://ru2.php.net/manual/ru/language.oop5.overloading.php.
3. Описание расширения mysqli – http://ru2.php.net/manual/ru/ref.mysqli.php.
4. Информация к размышлению – «Good and Evil in the Garden of Stored Procedures» (Jeremy D. Miller) – http://codebetter.com/blogs/jeremy.miller/archive/2005/07/05/130093.aspx.

Комментарии могут оставлять только зарегистрированные пользователи