Database

Database copied to clipboard

Получение библиотеки

Вы можете скачать её архивом, клонировать с данного сайта или загрузить через composer (ссылка на packagist.org):

composer require krugozor/database

Изменения

v2.0 - 25.02.2021

• Поддержка sleep/wakeup
• Изменены названия методов в классе Statement (старые методы назывались не в CamelCase стиле по историческим причинам)
• Изменено пространство имён

v1.0

• Больше не поддерживается, советую переходить на релиз v2.0

Что такое Database?

Database — библиотека классов на PHP >= 5.3 для простой, удобной, быстрой и безопасной работы с базой данных MySql, использующая расширение PHP mysqli.

Зачем нужен самописный класс для MySql, если в PHP есть абстракция PDO и расширение mysqli?

Основные недостатки всех библиотек для работы с базой в PHP это:

• Многословность
  • Что бы предотвратить SQL-инъекции, у разработчиков есть два пути:
    • Использовать подготавливаемые запросы (prepared statements).
    • Вручную экранировать параметры идущие в тело SQL-запроса. Строковые параметры прогонять через mysqli_real_escape_string(), а ожидаемые числовые параметры приводить к соответствующим типам — int и float.
  • Оба подхода имеют колоссальные недостатки:
    • Подготавливаемые запросы ужасно многословны. Пользоваться "из коробки" абстракцией PDO или расширением mysqli, без агрегирования всех методов для получения данных из СУБД просто невозможно — что бы получить значение из таблицы необходимо написать минимум 5 строк кода! И так на каждый запрос!
    • Экранирование вручную параметров, идущих в тело SQL-запроса — даже не обсуждается. Хороший программист — ленивый программист. Всё должно быть максимально автоматизировано.
• Невозможность получить SQL запрос для отладки
  • Что бы понять, почему в программе не работает SQL-запрос, его нужно отладить — найти либо логическую, либо синтаксическую ошибку. Что бы найти ошибку, необходимо "видеть" сам SQL-запрос, на который "ругнулась" база, с подставленными в его тело параметрами. Т.е. иметь сформированный полноценный SQL. Если разработчик использует PDO, с подготавливаемыми запросами, то это сделать... НЕВОЗМОЖНО! Никаких максимально удобных механизмов для этого в родных библиотеках НЕ ПРЕДУСМОТРЕНО. Остается либо извращаться, либо лезть в лог базы данных.

Решение: Database — класс для работы с MySql

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

Чем НЕ является библиотека Database?

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

Библиотека Database не является ничем из перечисленных. Это лишь удобный инструмент для работы с обычным SQL в рамках СУБД MySQL — и не более!

Что такое placeholders (заполнители)?

Placeholders (англ. — заполнители) — специальные типизированные маркеры, которые пишутся в строке SQL запроса вместо явных значений (параметров запроса). А сами значения передаются "позже", в качестве последующих аргументов основного метода, выполняющего SQL-запрос:

<?php
// Предположим, что установили библиотеку через composer 
require  './vendor/autoload.php';

use Krugozor\Database\Mysql;

// Соединение с СУБД и получение объекта-"обертки" над "родным" mysqli
$db = Mysql::create("localhost", "root", "password")
      // Выбор базы данных
      ->setDatabaseName("test")
      // Выбор кодировки
      ->setCharset("utf8");

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

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

// Не работает запрос? Не проблема - выведите его на печать:
echo $db->getQueryString();

Параметры SQL-запроса, прошедшие через систему placeholders, обрабатываются специальными механизмами экранирования, в зависимости от типа заполнителей. Т.е. вам теперь нет необходимости заключать переменные в функции экранирования типа mysqli_real_escape_string() или приводить их к числовому типу, как это было раньше:

<?php
// Раньше перед каждым запросом в СУБД мы делали
// примерно это (а многие и до сих пор `это` не делают):
$id = (int) $_POST['id'];
$value = mysql_real_escape_string($_POST['value'], $link);
$result = mysql_query("SELECT * FROM `t` WHERE `f1` = '$value' AND `f2` = $id", $link);

Теперь запросы стало писать легко, быстро, а главное библиотека Database полностью предотвращает любые возможные SQL-инъекции.

Типы заполнителей и типы параметров SQL-запроса

Типы заполнителей и их предназначение описываются ниже. Прежде чем знакомиться с типами заполнителей, необходимо понять как работает механизм библиотеки Database.

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

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

SELECT 123

В процессе исполнения этой команды библиотека проверяет, является ли аргумент 123 целочисленным значением. Заполнитель ?i представляет собой символ ? (знак вопроса) и первую букву слова integer. Если аргумент действительно представляет собой целочисленный тип данных, то в шаблоне SQL-запроса заполнитель ?i заменяется на значение 123 и SQL передается на исполнение.

Поскольку PHP слаботипизированный язык, то вышеописанное выражение эквивалентно нижеописанному:

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

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

SELECT 123

т.е. числа (целые и с плавающей точкой) представленные как в своем типе, так и в виде string — равнозначны с точки зрения библиотеки.

Приведение к типу заполнителя

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

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

SELECT 123

В данном примере заполнитель целочисленного типа данных ожидает значение типа integer, а передается double. По-умолчанию библиотека работает в режиме приведения типов, что дало в итоге приведение типа double к int.

Режимы работы библиотеки и принудительное приведение типов

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

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

// устанавливаем строгий режим работы
$db->setTypeMode(Mysql::MODE_STRICT);
// это выражение не будет исполнено, будет выброшено исключение:
// Попытка указать для заполнителя типа int значение типа double в шаблоне запроса SELECT ?i
$db->query('SELECT ?i', 55.5);

• Mysql::MODE_TRANSFORM — режим преобразования аргумента к типу заполнителя при несовпадении типа заполнителя и типа аргумента. Режим MODE_TRANSFORM установлен по-умолчанию и является "толерантным" режимом — при несоответствии типа заполнителя и типа аргумента не генерирует исключение, а пытается преобразовать аргумент к нужному типу заполнителя посредством самого языка PHP. К слову сказать, я, как автор библиотеки, всегда использую именно этот режим, строгий режим (Mysql::MODE_STRICT) я сделал чисто "по фану" и в реальной работе никогда не использовал.

Допускаются следующие преобразования в режиме 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) приводятся
  • любые аргументы.
• Для массивов, объектов и ресурсов преобразования не допускаются.

Какие типы заполнителей представлены в библиотеке Database?

?i — заполнитель целого числа

$db->query('SELECT * FROM `users` WHERE `id` = ?i', $value); 

ВНИМАНИЕ! Если вы оперируете числами, выходящими за пределы PHP_INT_MAX, то:

• Оперируйте ими исключительно как строками в своих программах.
• Не используйте данный заполнитель, используйте заполнитель строки ?s (см. ниже). Дело в том, что числа, выходящие за пределы PHP_INT_MAX, PHP интерпретирует как числа с плавающей точкой. Парсер библиотеки постарается преобразовать параметр к типу int, в итоге «результат будет неопределенным, так как float не имеет достаточной точности, чтобы вернуть верный результат. В этом случае не будет выведено ни предупреждения, ни даже замечания!» — php.net.

?d — заполнитель числа с плавающей точкой

$db->query('SELECT * FROM `prices` WHERE `cost` = ?d', $value); 

ВНИМАНИЕ! Если вы используете библиотеку для работы с типом данных double, установите соответствующую локаль, что бы разделитель целой и дробной части был одинаков как на уровне PHP, так и на уровне СУБД.

?s — заполнитель строкового типа

Значение аргументов экранируются с помощью функции PHP mysqli_real_escape_string():

 $db->query('SELECT "?s"', "Вы все пидарасы, а я - Д'Артаньян!");

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

SELECT "Вы все пидарасы, а я - Д\'Артаньян!"

?S — заполнитель строкового типа для подстановки в SQL-оператор LIKE

Значение аргументов экранируются с помощью функции PHP mysqli_real_escape_string() + экранирование спецсимволов, используемых в операторе LIKE (% и _):

 $db->query('SELECT "?S"', '% _'); 

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

SELECT "\% \_"

?n — заполнитель NULL типа

Значение любых аргументов игнорируются, заполнители заменяются на строку NULL в SQL запросе:

 $db->query('SELECT ?n', 123); 

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

SELECT NULL

?A* — заполнитель ассоциативного множества из ассоциативного массива, генерирующий последовательность пар ключ = значение

Пример: "key_1" = "val_1", "key_2" = "val_2", ..., "key_N" = "val_N"

где * после заполнителя — один из типов:

• i (заполнитель целого числа)
• d (заполнитель числа с плавающей точкой)
• s (заполнитель строкового типа)

правила преобразования и экранирования такие же, как и для одиночных скалярных типов, описанных выше. Пример:

$db->query('INSERT INTO `test` SET ?Ai', ['first' => 123, 'second' => 1.99]);

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

INSERT INTO `test` SET `first` = "123", `second` = "1"

?a* — заполнитель множества из простого (или также ассоциативного) массива, генерирующий последовательность значений

Пример: "val_1", "val_2", ..., "val_N"

где * после заполнителя — один из типов:

• i (заполнитель целого числа)
• d (заполнитель числа с плавающей точкой)
• s (заполнитель строкового типа)

правила преобразования и экранирования такие же, как и для одиночных скалярных типов, описанных выше. Пример:

 $db->query('SELECT * FROM `test` WHERE `id` IN (?ai)', [123, 1.99]);

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

 SELECT * FROM `test` WHERE `id` IN ("123", "1")

?A[?n, ?s, ?i, ...] — заполнитель ассоциативного множества с явным указанием типа и количества аргументов, генерирующий последовательность пар ключ = значение

Пример:

 $db->query('INSERT INTO `test` SET ?A[?i, "?s"]', ['first' => 1.3, 'second' => "Д'Артаньян"]);

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

 INSERT INTO `test` SET `first` = 1,`second` = "Д\'Артаньян"

?a[?n, ?s, ?i] — заполнитель множества с явным указанием типа и количества аргументов, генерирующий последовательность значений

 $db->query('SELECT * FROM `test` WHERE `value` IN (?a[?i, "?s"])', [1.3, "Д'Артаньян"]);

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

 SELECT * FROM `test` WHERE `value` IN (1, "Д\'Артаньян")

?f — заполнитель имени таблицы или поля

Данный заполнитель предназначен для случаев, когда имя таблицы или поля передается в запросе через параметр. Имена полей и таблиц обрамляется символом апостроф:

 $db->query('SELECT ?f FROM ?f', 'name', 'database.table_name');

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

 SELECT `name` FROM `database`.`table_name`

Ограничивающие кавычки

Библиотека требует от программиста соблюдения синтаксиса SQL. Это значит, что следующий запрос работать не будет:

$db->query('SELECT CONCAT("Hello, ", ?s, "!")', 'world');

— заполнитель ?s необходимо взять в одинарные или двойные кавычки:

$db->query('SELECT concat("Hello, ", "?s", "!")', 'world');

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

SELECT concat("Hello, ", "world", "!")

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

Примеры работы с библиотекой

// Предположим, что установили библиотеку через composer 
require  './vendor/autoload.php';

use Krugozor\Database\Mysql;

// Подключение к СУБД, выбор кодировки и базы данных.
$db = Mysql::create('localhost', 'root', '')
           ->setCharset('utf8')
           ->setDatabaseName('test');

// Создаем таблицу пользователей с полями:
// Первичный ключ, имя пользователя, возраст, адрес
$db->query('
    CREATE TABLE IF NOT EXISTS users(
        id int unsigned not null primary key auto_increment,
        name varchar(255),
        age tinyint,
        adress varchar(255)
    )
');

Примеры для понимания сути заполнителей

Различные варианты INSERT:

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

$db->query("INSERT INTO `users` VALUES (?n, '?s', ?i, '?s')", 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` = "замок Кастельмор"

Различные варианты SELECT

Укажем некорректный числовой параметр - значение типа double:

$db->query('SELECT * FROM `users` WHERE `id` = ?i', '1.00');

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

SELECT * FROM `users` WHERE `id` = 1

---

 $db->query(
    'SELECT id, adress FROM `users` WHERE `name` IN (?a["?s", "?s", "?s"])',
    array('Василий', 'Иван', "Д'Артаньян")
); 

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

SELECT id, adress FROM `users` WHERE `name` IN ("Василий", "Иван", "Д\'Артаньян")

Имя базы данных, таблицы и поля передаются также, как и аргументы запроса. Не удивляйтесь имени поля '.users.name' - это допустимый для MySql синтаксис:

$db->query(
    'SELECT * FROM ?f WHERE ?f IN (?as) OR `id` IN (?ai)',
    '.users', '.users.name', array('Василий'), array('2', 3.000)
);

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

SELECT * FROM .`users` WHERE .`users`.`name` IN ("Василий") OR `id` IN ("2", "3")

Некоторые возможности API

Применение метода queryArguments() - аргументы передаются в виде массива. Это второй, после метода query(), метод запросов в базу:

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

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

if ($db->query("INSERT INTO `users` VALUES (?n, '?s', '?i', '?s')", null, 'тест', '10', 'тест')) {
    echo $db->getLastInsertId(); // последнее значение автоинкрементного поля
    echo $db->getAffectedRows(); // количество задействованных рядов
}

Получить все в виде ассоциативных массивов:

// Получить все...
$res = $db->query('SELECT * FROM users');
// Последовательно получать в виде ассоциативных массивов
while (($data = $res->fetchAssoc()) !== null) {
    print_r($data);
}

Получить одно значение из выборки:

echo $db->query('SELECT 5 + ?d', '5.5')->getOne(); // 10.5

Получить все SQL-запросы текущего соединения:

print_r($db->getQueries());