PHP*

Новый класс для MySql: писать запросы к базе теперь быстро, легко и удобно

PravdorubMSK 6 октября в 21:39 7,6k

Проблема


Когда я прихожу на новое место работы, то первым делом я смотрю на то, как организован слой для работы с СУБД. Я не работал с иными фреймворками помимо первого Yii, волею судьбы я попадаю на самописные решения, но везде я вижу практически одно и тоже — просто отвратительная реализация кода доступа к базам данных.

Все знают, что такое подготавливаемые запросы в PHP. Концепция, заложенная в них — это т.н. заполнители, маркеры или псевдопеременные вида "?", которые пишутся в строке запроса вместо реальных значений-аргументов, которые необходимо подставить в запрос:

SELECT * FROM `table` WHERE id = ?

php.net так описывает данный механизм работы:
Во время запуска запроса клиент привязывает к псевдопеременным реальные значения и посылает их на сервер. Сервер, в свою очередь, подставляет их в шаблон и запускает уже готовый запрос на выполнение.
Т.е. сначала уходит запрос, анализируется, и если всё хорошо — уходят наши данные, которыми и заменяются соответствующие метки:

SELECT * FROM `table` WHERE id = 123

Преимущество данного подхода:

  • Безопасность и предотвращение SQL-инъекций
  • Подготовленный запрос можно запускать многократно. Сам текст запроса повторно не анализируется, равно как и не отсылается повторно шаблон запроса.

Однако, пользоваться из «коробки» абстракцией PDO или mysqli адаптером чертовски неудобно, необходимо вызвать как минимум 5 методов и плодить код:

/* создаем подготавливаемый запрос */
$stmt =  $mysqli->stmt_init();
if ($stmt->prepare("SELECT District FROM City WHERE Name=?")) {

    /* привязываем переменные к параметрам */
    $stmt->bind_param("s", $city);

    /* выполняем запрос */
    $stmt->execute();

    /* привязываем результаты к переменным */
    $stmt->bind_result($district);

    /* выбираем данные из результата */
    $stmt->fetch();

    printf("%s находится в округе %s\n", $city, $district);

    /* закрываем запрос */
    $stmt->close();
}

Далее. В больших проектах — большие SQL-выражения. Один раз я пытался разобраться в SQL-запросе на 500 строк кода, который, естественно, формировался «на лету» в зависимости от логики программы. SQL выполнялся как раз то ли через PDO, то ли через mysqli через подготавливаемые запросы. Как только я допускал логическую или синтаксическую ошибку, то приходилось писать на PHP «костыль», в которой я передавал и запрос и значения псевдопеременных для получения конечного, сформированного SQL с уже подставленными в его тело параметрами. Такие извращения я делал лишь для того, что бы взять SQL и запустить его в целях отладки.

Как видим, подготавливаемые запросы в «сыром» виде — неэффективны с точки зрения быстрой и удобной разработки. Они не позволяют получить сформированный SQL-запрос для отладки и имеют весьма неудобную для клиента реализацию. Кроме того, «использование подготавливаемых запросов не всегда приводит к повышению эффективности. Если параметризованный запрос запускается лишь раз, это приводит к большему количеству клиент-серверных обменов данными, нежели при выполнении простого запроса» — php.net.

Решение


Теперь делюсь своим решением — Database — класс для работы с MySql

  • Избавляет от многословности — вместо 3 и более строк кода для исполнения одного запроса при использовании «родной» библиотеки, вы пишите всего 1
  • Экранирует все параметры, идущие в тело запроса, согласно указанному типу заполнителей — надежная защита от SQL-инъекций.
  • Не замещает функциональность «родного» mysqli адаптера, а просто дополняет его.

Прошу заметить — в данном решении я не сделал ничего лишнего. Большинство оберток под различные драйверы баз данных являются нагромождением бесполезного кода с отвратительной архитектурой. Их авторы, сами не понимая практической цели своих оберток, превращают их в подобие построителей запросов (sql builder), ActiveRecord библиотек и прочих ORM-решений. Библиотека Database не является ничем из перечисленных. Это лишь удобный инструмент для работы с обычным SQL в рамках СУБД MySQL — и не более!

В чем же преимущество данного решения? Давайте попробуем немного попрограммировать:

Экранирование


<?php
$db = Database_Mysql::create("localhost", "root", "password")
      // Выбор базы данных
      ->setDatabaseName("test")
      // Выбор кодировки
      ->setCharset("utf8");

// Получение объекта результата Database_Mysql_Statement
// Database_Mysql_Statement - "обертка" над "родным" объектом mysqli_result
$result = $db->query("SELECT `age` FROM `users` WHERE `name` = '?s'", "Петя");

// Получаем данные (в виде ассоциативного массива, например)
$data = $result->getOne();

В данном примере анализатор:

  1. Определяет маркер ?s как маркер строкового типа (s — string)
  2. Делает экранирование строкового параметра «Петя» посредством метода mysqli::real_escape_string
  3. Если больше не найдено маркеров и их количество соответствует количеству аргументов метода — выполняет запрос.

Для наглядности приведу пример, демонстрирующий экранирование строковых параметров с символом, который необходимо экранировать в SQL:

$db->query("SELECT '?s'", "- Милая хозяюшка, - сказал д'Артаньян");

SQL, который будет выполнен:

SELECT '- Милая хозяюшка, - сказал д\'Артаньян'

Предусмотрен маркер и для экранирования данных при LIKE-поиске:

$db->query("SELECT `m` from `t` WHERE `m` LIKE '?S'", "Привет, %_username_%");

SQL-запрос после преобразования шаблона:

SELECT `advert_text` from `advert` WHERE `advert_text` LIKE 'Привет, \%\_username\_\%'

Режимы работы библиотеки


Существует два режима работы библиотеки:

  • Database_Mysql::MODE_STRICT — строгий режим соответствия типа заполнителя и типа аргумента. В режиме MODE_STRICT аргументы должны соответствовать типу заполнителя. Например, попытка передать в качестве аргумента значение 55.5 или '55.5' для заполнителя целочисленного типа ?i приведет к выбросу исключения:

    // это выражение не будет исполнено, будет выброшено исключение:
    // Попытка указать для заполнителя типа int значение типа double в шаблоне запроса SELECT ?i
    $db->query('SELECT ?i', 55.5);
  • Database_Mysql::MODE_TRANSFORM — режим преобразования аргумента к типу заполнителя при несовпадении типа заполнителя и типа аргумента. Режим MODE_TRANSFORM установлен по-умолчанию и является «толерантным» режимом — при несоответствии типа заполнителя и типа аргумента не генерирует исключение, а пытается преобразовать аргумент к нужному типу заполнителя посредством самого языка PHP:

     $db->query("SELECT ?i", '123.7'); 

    SQL-запрос после преобразования шаблона:

    SELECT 123

По-умолчанию стоит режим Database_Mysql::MODE_TRANSFORM.

Допускаются следующие преобразования:

  • К типу int (заполнитель ?i) приводятся
    • числа с плавающей точкой, представленные как string или тип double
    • bool TRUE преобразуется в int(1), FALSE преобразуется в int(0)
    • null преобразуется в int(0)
  • К типу double (заполнитель ?d) приводятся
    • целые числа, представленные как string или тип int
    • bool TRUE преобразуется в float(1), FALSE преобразуется в float(0)
    • null преобразуется в float(0)
  • К типу string (заполнитель ?s) приводятся
    • bool TRUE преобразуется в string(1) "1", FALSE преобразуется в string(1) "0". Это поведение отличается от приведения типа bool к int в PHP, т.к. зачастую, на практике, булев тип записывается в MySql именно как число.
    • значение типа numeric преобразуется в строку согласно правилам преобразования PHP
    • NULL преобразуется в string(0) ""
  • К типу null (заполнитель ?n) приводятся
    • любые аргументы

Методы выборки


Что касательно методов выборки: существует два основных метода — query() и queryArguments(). Первый метод, как мы уже видели выше, принимает строку SQL-запроса и неограниченное количество параметров — аргументов для подстановки в маркеры запроса. Второй метод принимает строку SQL-запроса и массив параметров:

$sql = 'SELECT * FROM `users` WHERE `name` = "?s" OR `name` = "?s"';
$arguments[] = "Петр Первый";
$arguments[] = "Д'Артаньян";
$result = $db->queryArguments($sql, $arguments);
// Получим количество рядов в результате
$result->getNumRows(); // 2

SQL-запрос после преобразования шаблона:

SELECT * FROM `users` WHERE `name` = "Петр Первый" OR `name` = "Д\'Артаньян"

Методы получения результата


Методы аналогичны методам получения результата из объекта mysqli_stmt:

$result = $db->query('SELECT * FROM `t` LIMIT 0, ?i', 10);
while ($data = $result->fetch_assoc()) {
    print_r($data);
}

а можно и всё сразу:

$result = $db->query('SELECT * FROM `t` LIMIT 0, ?i', 10);
$data = $result->fetch_assoc_array();
print_r($data);

а можно и одно значение:

$result = $db->query('SELECT 1 + ?i', 10);
echo $result->getOne(); // 11

Подсчитаем кол-во рядов в запросе:

$result = $db->query('SELECT * FROM `t`');
echo $result->getNumRows(); // 100500

Отладка запросов


Отлаживать неработающие запросы с помощью библиотеки одно удовольствие:

$result = $db->query('SELECT * FROM `t` WHERE a = ?i AND ', 100);

в исключении будет строка вида:


SQL: SELECT * FROM `t` WHERE a = 100 AND 

История запросов


Вам доступна история всех запросов текущего соединения (рекомендуется выключать на боевом):

$db->query('SELECT 1 + ?i', 1);
$db->query('SELECT 1 + ?i', 2);
$db->query('SELECT 1 + ?i', 3);
print_r($db->getQueries());

Array
(
    [SELECT 1 + 1] => SELECT 1 + ?i
    [SELECT 1 + 2] => SELECT 1 + ?i
    [SELECT 1 + 3] => SELECT 1 + ?i
)

Можно получить SQL до и после преобразования


$db->query('SELECT "?s"', 'Привет, Петя!');
echo $db->getQueryString(); // SELECT "Привет, Петя!"
echo $db->getOriginalQueryString(); // SELECT "?s"

Примеры работы с маркерами (их много, см. документацию)


Простая вставка данных через заполнители разных типов:

$db->query("INSERT INTO `users` VALUES (?n, '?s', ?i)", null, 'Иоанн Грозный', '54');

SQL-запрос после преобразования шаблона:


INSERT INTO `users` VALUES (NULL, 'Иоанн Грозный', 54)

Вставка значений через заполнитель ассоциативного множества типа string:

$user = array('name' => 'Пётр', 'age' => '30', 'adress' => "ООО 'Рога и Копыта'");
$db->query('INSERT INTO `users` SET ?As', $user);

SQL-запрос после преобразования шаблона:


INSERT INTO `users` SET `name` = "Пётр", `age` = "30", `adress` = "ООО \'Рога и Копыта\'"

Вставка значений через заполнитель ассоциативного множества с явным указанием типа и количества аргументов:

$user = array('name' => "Д'Артаньян", 'age' => '19', 'adress' => 'замок Кастельмор');
$db->query('INSERT INTO `users` SET ?A["?s", ?i, "?s"]', $user);

SQL-запрос после преобразования шаблона:


INSERT INTO `users` SET `name` = "Д\'Артаньян",`age` = 19,`adress` = "замок Кастельмор"

Это примерный список возможностей данного решения. Остальные возможности библиотеки вы найдете в документации.
Проголосовать:
–24
Сохранить: