Хождение по граблям PDO: что скрывают за собой современные PHP ORM

Привет! Меня зовут Валерий Горбачев. Давно пишу на PHP, занимаюсь опенсорсом (участвую в команде разработке Yii3 Framework), организую митапы в родном Краснодаре и выступаю с докладами на конференциях. 

Эта статья написана на основе моего доклада на HighLoad++ 2022. На момент выступления на конференции я работал старшим разработчиком в Delivery Club. 

Рассказывать буду только о том, о чём знаю сам. У вас может быть абсолютно другое мнение. Поэтому если вы нашли ошибку или хотите обсудить — свяжитесь со мной.

Как я изучал БД и понял, что со старым кодом пора что-то делать

Всем знакомы стадии отрицания, гнева, депрессии… Именно так часто выглядит классическая кривая обучения использованию баз данных (БД). В моей жизни всё было примерно также, но добавились ещё несколько пунктов:

• данные в файлах;

• файловые БД (dbf, dBase IV);

• изучение SQL (книги, документация).

Я начал с типизированных структур, которые хранил в файлах. Затем перешёл на dbf, и только потом пришёл к SQL и начал читать книги и погружаться в документацию. Сначала казалось, что работа с базами данных — это максимально просто.

Я писал такие SQL-запросы:

$sql = "SELECT * FROM products WHERE id = {$id}"

Иногда «интвалил», чтобы обезопасить свои запросы:

$sql = 'SELECT * FROM products WHERE id = ' . @intval($id);

Сохранял строковые данные вот так:

$sql = "SELECT * FROM products WHERE name = '{$name}'";

А когда меня первый раз взломали, наконец узнал про SQL-уязвимости. Тогда я начал искать информацию в интернете, плюс посоветовался с коллегами и стал использовать экранирование:

$name = mysql_real_escape_string($name);
$sql = "SELECT * FROM products WHERE name = '". $name. "'";

После того как модуль mysql_ задепрекейтили, задумался и о других решениях:

$stmt = $mysqli->prepare("SELECT * FROM products WHERE name = ?");
$stmt->bind_param("s", $name);

Как менялись спецификации в PHP

Все эти изменения можно буквально расписать по годам.

• PHP 5.0 — появление ext/mysqli (2003).

• PHP 5.1 — появление модуля PDO_MySQL (2005).

• PHP 5.3 — появление mysqlnd с рядом интересных изменений и уходом от ограничений Oracle (2009).

• PHP 5.* — переход с libmysqlclient (C++, Oracle) на mysqlnd (2013).

• PHP 5.5 — модуль mysql_* помечен устаревшим и удалён в версии 7 (2013).

Тогда я задумался, что со старым кодом пора что-то делать и версию понадобилось обновить. Передо мной стал выбор: перейти на MySQLi или начать использовать PDO. 

Конечно же, сначала их нужно сравнить:

Преимущества MySQLi несомненные, но я выбрал его в первую очередь потому, что можно было просто добавить букву i, быстро пройтись по коду и всё стало бы хорошо. Но, к сожалению, я запомнил и фразу с одного из форумов:

«Используя PDO, можно легко сменить используемую СУБД»

И на этом можно было бы остановиться…

Я жил в мире розовых пони, то есть фреймворков, использовал ORM, QueryBuilder’ы и делал вот так:

$userQuery = (new Query())->select('id')->from('user');

На старых проектах даже чуть-чуть пробовал MySQLi с биндингом:

$stmt->bind_param("ssssiisississ", $name, $title, ...);

Но в биндинге можно было легко запутаться в правильном порядке букв “s”, “i”. Особенно если биндить параметр и пытаться вставить его в середину. Другое дело ORM.

ORM — лекарство от всех проблем?

У использования ORM много преимуществ:

• Код абстрактный и легко читается.

• Нет нужды писать «сырые» запросы, ведь можно использовать QueryBuilder.

• Порог вхождения гораздо ниже, потому что даже необязательно знать SQL.

• Код не слишком привязан к движку БД.

Примеры таких ORM: Eloquent, Doctrine, Cycle, Propel, RedBeanPHP, Yii DB и т.д.

Но у ORM, конечно, есть и недостатки. Чтобы обойтись без обобщений, дальше я буду опираться на опыт полученный мной из использования ORM от Yii Framework версий 1 и 2.

• Нет той же гибкости, как при написании «сырых» запросов.

• Трудно писать сложные запросы.

• Работает быстро, но немного медленнее нативных библиотек. Это же абстракция, поэтому на производительности придётся чуть-чуть потерять ради удобства.

• Если вы обнаружили какую-то проблему, помним, что это чужой код и мейнтейнеры могут ответить, что «так сложилось» и менять ничего не будут.

• MySQL-first-подход как самая большая боль — в случае если фреймворк устарел. Когда писали ORM, было важно, чтобы всё работало с MySQL, а поддержку остальных БД реализовали по принципу «чтобы как в MySQL».

Хождение по граблям как стиль жизни

Небольшое отступление: мне мешают использовать ORM всю жизнь.

Например, на одном из проектов сказали, что будем ходить в БД только через API, и про ORM пришлось забыть. Писали сырые запросы и использовали через API-вызовы.

На разных работах я понемногу занимался MySQL, MSSQL, PostgreSQL, Oracle и другими менее популярными решениями. Это привело меня к текущему моменту: я осознал, что весь мой опыт работы с БД весьма поверхностен. А где это может пригодиться? Конечно же, при написании пакета для поддержки баз данных.

Именно так я и познакомился с Wilmer Arambula, с которым мы вместе сейчас рефакторим Yii Database Library. А в основе пакетов этой библиотеки лежит PDO…

Скучный пересказ документации, в которой можно найти ответы на большинство вопросов

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

/* Выполнение запроса с передачей ему массива параметров */
$sql = 'SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour';
$sth = $dbh->prepare($sql, ['calories' => 150, 'colour' => 'red']);
$sth->execute();
$red = $sth->fetchAll();

Это пример из официальной документации: в именованные плейсхолдеры подставляем параметры и выполняем — всё красиво и безопасно.

Возможности PDO по поддержке разных БД зашкаливают:

Жёлтым я подсветил то, с чем сам работал из PHP. Но на самом деле PDO_ODBC тоже можно было подсветить, потому что периодически мы заходили в БД и через PDO_ODBC. 

Впрочем, к вопросу об универсальном использовании разных DBMS с использованием PDO. Я ведь ещё помню, что PDO обещает, что это будет несложно, но даже при первых пробах проявились детали:

• Запрос запросу рознь (LIMIT 10 OFFSET 10 vs LIMIT 10, 10).

• Универсального кода не бывает, универсальный код SQL тоже не универсален.

• Даже если универсальный код и бывает, то иногда он работает неожиданно...

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

В основном мы используем режим для выборки FETCH_ASSOC или FETCH_OBJ:

$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

/* Извлечение всех оставшихся строк результирующего набора */
print("Извлечение всех оставшихся строк результирующего набора:n");
$result = $sth->fetchAll();
print_r($result);

Складываем в ассоциативный массив или в объект и всё. Но документация подсказывает, что есть множество других интересных режимов.

FETCH_KEY_PAIR

SELECT `username`, `email` FROM `users`

/*
[
    'login1' => 'email1@mail.ru',
    'login2' => 'email2@mail.ru',
]
*/

Режим выборки в стиле "Key-value" следит, чтобы в выборке было только два поля и перезаписывает последним ключом. Первый параметр из SQL-запроса будет использоваться как ключ, второй — как значение.

FETCH_UNIQUE

SELECT `id`, `username`, `email` FROM `users`

/*
[
  1 => ['username' => 'login101', 'email' => 'email1@mail.ru', ],
  2 => ['username' => 'login102', 'email' => 'email2@mail.ru', ],
]
*/

Здесь всё как в предыдущем случае, только значения не одно, а массив значений. 

FETCH_GROUP

SELECT `group`, `id`, `username` FROM `users`

/*
[
    'reader' => [
        ['id' => 1, 'username' => 'login101'],
        ['id' => 3, 'username' => 'login103'],
    ],
    'writer' => [
        ['id' => 2, 'username' => 'login102'],
        ['id' => 4, 'username' => 'login104'],
    ],
]
*/

Группировка по первому значению может пригодиться при построении элементарного двухуровневого меню. 

FETCH_NAMED

FETCH_NAMED можно использовать вместо FETCH_ASSOC, если одно значение в выборке используется несколько раз, как в примере:

SELECT u.`name`, u.`email`, g.`name`
  FROM `users` u
  INNER JOIN `groups` g ON g.`id` = u.`group_id`

$stmt->fetch(PDO::FETCH_NAMED);
/*
[
    'name' => [
        0 => 'login101',
        1 => 'writer',
    ],
    'email' => 'email1@mail.ru',
]
*/

Или, например, вы сделали SELECT *, объединили с помощью JOIN две таблицы и имена полей совпали. Благодаря FETCH_NAMED эти значения не потеряются при перезаписи.

Не рекомендую использовать SELECT *, но в жизни всякое бывает.

FETCH_CLASSTYPE

SELECT
  CONCAT(UCASE(LEFT(`group`, 1)), SUBSTRING(`group`, 2)),
    `username`, `email`
  FROM `users`

$stmt->fetch(PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE);
/*
object(Reader)#3 (1) {
  ["username"] => string(8) "login101",
  ["email"] => string(14) "email1@mail.ru",
}
*/

Можно использовать первую колонку из результата запроса как класс, который PDO будет искать в PHP, создать объект класса, намаппить туда значения и получится своеобразная абстрактная фабрика. 

Если вам тоже стало интересно почитать про режимы работы PDO, вот здесь об этом отлично рассказали.

Раз мы коснулись, что всё складываем в объект, то самое время вспомнить, что начиная с PHP 8.2, с использованием динамических свойств могут начаться проблемы. До этого мы спокойно могли добавлять их в объект, а сейчас будем получать предупреждение. Начиная с PHP 9 с этим, вероятно, будут ещё бóльшие проблемы.

А есть ли ещё «занимательные флаги»?

• PDO::ATTR_ORACLE_NULLS —"" => NULL, NULL =>""
  вместо пустой строки вернуть NULL, вместо NULL — пустая строка.

На самом деле это работает не только с Oracle, я проверил это с PostgreSQL — тоже прекрасно работает.

• PDO::ATTR_CASE — регистр имён столбцов в возвращаемых данных.

В ассоциативном массиве ключи можно вернуть в верхнем, нижнем регистре или в нативном режиме.

Прогресс не стоит на месте и в новых версиях PHP добавлены новые флаги:

• MYSQL_ATTR_LOCAL_INFILE_DIRECTORY 

Можно добавить ограничение, вроде open_basedir только для импорта ваших данных из файлов расположенных на сервере:

LOAD DATA LOCAL INFILE 'path/to/file/file.txt'

Есть, конечно, и «странные флаги»:

• PDO::MYSQL_ATTR_MULTI_STATEMENTS — разрешает использование многострочных SQL-выражений, с которыми все борются, а этим флагом их можно включать, но с множеством оговорок.

• PDO::ATTR_FETCH_CATALOG_NAMES — добавление имени каталога БД к именам колонок в возвращаемых результатах.

• PDO::ATTR_FETCH_TABLE_NAMES — добавление имени таблицы к имени колонки в возвращаемых результатах.

Это может помочь не потерять значение колонки при повторе её имени в возвращаемых результатах. В ассоциативном массиве результатов в качестве ключа будет использовано ТАБЛИЦА.ИМЯ ПОЛЯ.

Про ATTR_CASE

Как-то раз к нам обратились с проблемой — перестали получать схему данных (https://github.com/yiisoft/yii2/issues/18171). Это случилось после обновления на минорную версию MySQL 8.0.21. Раньше регистр колонок указанный в запросе возвращался в результатах в том же виде. Начиная с версии MySQL 8.0.21 его начали возвращать так, как имя колонки создано в таблице БД. Как вы понимаете, в INFORMATION_SCHEMA используется верхний регистр для имён колонок, а в запросе на его получение — нижний.

SELECT `constraint_name` from `information_schema`.`key_column_usage`;

/*
MySQL < 8.0.21 fields name:
array(1) {
  'constraint_name' => "name_of_contraint"
}

MySQL 8.0.21 fields name:
array(1) {
  'CONSTRAINT_NAME' => "name_of_contraint"
}
*/

Конечно, можно было использовать PDO::ATTR_CASE, но мы решили, что не будем изменять общее поведение при инициализации соединения, и просто добавили метод для приведения регистра имён в получаемых результатах к нижнему регистру.

Хранение JSON в BLOB полях

Типичная задача для человека, который пишет Role-based access control (RBAC) и сохраняет их в БД (настройки ролей, доступы и т.п.).

$sql_insert = <<<SQL
    insert into `pdo_types`(`blob_col`) values(:blob_data);
SQL;

$sql_read = <<<SQL
    select `blob_col` from `pdo_types`;
SQL;

Лучше использовать специализированные данные, но мы попытаемся сохранить JSON в непредназначенном для этого BLOB-поле.

MySQL

С MySQL всё просто (пример со строкой просто для читаемости):

• Вставляем данные:

$blobData = "test `s_t_r_i_n_g`";
$pdo->prepare($sql_insert)->execute([':blob_data' => $blobData]);

• Читаем данные:

$stmt = $pdo->query($sql_read);
$result = $stmt->fetch(PDO::FETCH_COLUMN);
var_dump($result);

• Радуемся:

string(18) "test `s_t_r_i_n_g`"

Подготовили типовой запрос, вставили данные, прочитали данные — и всё отлично.

PostgreSQL

• Вставляем данные:

$blobData = "test `s_t_r_i_n_g`";
$pdo->prepare($sql_insert)->execute([':blob_data' => $blobData]);

• Читаем данные:

$stmt = $pdo->query($sql_read);
$result = $stmt->fetch(PDO::FETCH_COLUMN);
var_dump($result);

• Удивляемся:

resource(9) of type (stream)

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

Немного экзотики — Oracle

С моим любимым Oracle у нас не получилось даже вставить данные.

• Вставляем:

$blobData = "test `s_t_r_i_n_g`";
$pdo->prepare($sql_insert)->execute([':blob_data' => $blobData]);

• Удивляемся:

PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000]: General error: 1465 OCIStmtExecute: ORA-01465: invalid hex number

Ответ есть в документации. Нам просто нужен иной синтаксис:

В случае с базами Oracle требуется несколько иной синтаксис для извлечения содержимого файла и помещения в базу. Также необходимо выполнять вставку в рамках транзакции, иначе вставленный LOB будет зафиксирован в базе с нулевой длиной, так как если не обозначить границы транзакции, изменения будут фиксироваться после каждого выполненного запроса.

Если до этого нужна была маленькая строка запроса, то теперь это выглядит вот так:

$sql_insert = <<<SQL
    insert into "pdo_types"("blob_col") values(empty_blob()) returning "blob_col" into :blob_data
SQL;

$blobData = "test `s_t_r_i_n_g`";
$fp = fopen('php://memory', 'rwb');
fwrite($fp, $blobData);
fseek($fp, 0);

$stmt = $pdo->prepare($sql_insert);
$pdo->beginTransaction();
    $stmt->bindValue(':blob_data', $fp, PDO::PARAM_LOB);
    $stmt->execute();
$pdo->commit();

Мы должны указать при подстановке, что будут использованы бинарные данные используя PDO::PARAM_LOB. Всё необходимо делать в транзакции, чтобы выполнение было одномоментным.

Поведение при чтении не отличается от PostgreSQL, в Oracle нам аналогично вернётся stream. 

Rewind

$stmt = $pdo->query($sql_read);
$handle = $stmt->fetch(PDO::FETCH_COLUMN);

$contents = '';
while (!feof($handle)) {
    $contents .= fread($handle, 2);
}
rewind($handle);
while (!feof($handle)) {
    $contents .= fread($handle, 2);
}

На всякий случай попробовал проверить возвращаемые потоки — можно ли их перематывать с помощью rewind и fseek. Проблем не увидел: они ведут себя как обычные файловые дескрипторы. Но давайте всё же посмотрим, как обойтись без всей этой сложности с транзакцией.

Чиним BLOB поля и Oracle

Если данные у вас не совсем бинарные (а как в нашем примере JSON/base64), то можно обойтись без транзакций и файлов. Вот такой костыль с преобразованием данных в запросе решает мою проблему — я успешно вставил данные в BLOB поле:

$stmt = $db->prepare("INSERT INTO t1(blob_col) VALUES (TO_BLOB(UTL_RAW.CAST_TO_RAW(:blob_col)))");
$stmt->bindParam(':blob_col', $blobData);

Но лучше всё же хранить данные в предназначенных для этого специализированных полях (base64 в CLOB, а json в JSON).

Получение последнего вставленного ID

Ещё одна задача, которую мы часто решаем, — получение ID только что вставленной записи. Описание метода из официальной документации:

public PDO::lastInsertId(?string $name = null): string|false

Нужный нам метод, и конечно у него есть ограничения:

Замечание: 

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

Не все драйверы это поддерживают, например, Microsoft SQL Server. По этому поводу даже есть пример вопроса со Stackoverflow:

I am running an insert query using PDO and then getting the newly created Id with lastInsertId(). This is all working on my localhost environment. When I move the exact same code onto a server, the lastInsertId() is always returning blank, even though the insert statement works and inserts the new row into the database.

Почему возвращается пустая строка? Ответ есть в документации:

Remarks:

Поддержка PDO была добавлена в версии 2.0 Драйверы Microsoft SQL Server для РНР.

Между версиями 2.0 и 4.3 необязательным параметром является имя таблицы, а возвращаемым значением — идентификатор последней добавленной в указанную таблицу записи. Начиная с 5.0, как необязательный параметр рассматривается имя последовательности, а как возвращаемое значение — последовательность, которую добавили для указанного имени последовательности последней. Если имя таблицы указано для версий после 4.3, lastinsertid возвращает пустую строку. Последовательности поддерживаются только в SQL Server 2012 и более поздних версиях.

Смириться или приложить подорожник?

Поддержка MSSQL в Yii существовала и до исправления в драйверах. Чинили это вот таким образом: мы делаем выбор из двух системных функций, и из одной из них получаем результаты. 

class mssqlPDO extends PDO
{
    /**
     * Returns value of the last inserted ID.
     * @param string|null $sequence the sequence name. Defaults to null.
     * @return int last inserted ID value.
     */
    public function lastInsertId($sequence = null)
    {
        $sql = 'SELECT CAST(COALESCE(SCOPE_IDENTITY(), @@IDENTITY) AS bigint)';
        return $this->query($sql)->fetchColumn();
    }
}

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

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

For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger

https://docs.microsoft.com/ru-RU/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-2016

• IDENT_CURRENT — returns the last identity value generated for a specific table in any session and any scope.

• @@IDENTITY — returns the last identity value generated for any table in the current session, across all scopes.

• SCOPE_IDENTITY — returns the last identity value generated for any table in the current session and the current scope.

Я решил допроверять поведение, кто поддерживает lastInsertId, а кто нет.

$pdo1->prepare($sql_insert)->execute([':n' => 101]);
$pdo2->prepare($sql_insert)->execute([':n' => 102]);

echo 'lastInsertId1 = '.var_export($pdo1->lastInsertId(), true).PHP_EOL;
echo 'lastInsertId2 = '.var_export($pdo2->lastInsertId(), true).PHP_EOL;
// lastInsertId1 = '1'
// lastInsertId2 = '2'

MySQL, PostgreSQL и MSSQL работают корректно и проблем с получением вставленных ID нет. Я создавал два соединения, вставлял в них по очереди и проверял, тот ли ID я получаю. Выглядит всё хорошо.

С Oracle я сразу получил сообщение, что драйвер не поддерживает функцию:

PHP Fatal error:  Uncaught PDOException: SQLSTATE[IM001]: Driver does not support this function: driver does not support lastInsertId()

Но если очень хочется…

Я решил поэкспериментировать и попробовать использовать системные функции по аналогии с MSSQL. Для экспериментов использовал PHP 8.0 + Oracle XE 11g r2 + instantclient21_3 и прямое обращение к sequence — в результате мы получаем нужный нам ID.

$stmt1 = $pdo1->query('SELECT "pdo_types_SEQ".CURRVAL FROM DUAL');
$result1 = $stmt1->fetchColumn();

$stmt2 = $pdo2->query('SELECT "pdo_types_SEQ".CURRVAL FROM DUAL');
$result2 = $stmt2->fetchColumn();

// result1 = '1'
// result2 = '2'

Дам общую рекомендацию — не используйте PDO::lastInsertId, есть способы лучше:

• PostgreSQL — RETURNING "id"

• MSSQL — OUTPUT INSERTED.ID 

• Oracle — RETURNING id INTO :id

• MariaDB (с версии 10.5.0) — RETURNING id

Для всех DBMS есть решения, кроме старых версий MySQL. В использовании MSSQL тоже есть особенность, связанная с таблицами имеющими триггер. 

Вообще lastInsertId — интересная штука. Случай из жизни, когда заказчики попросили: «Вы нам письмо отправляете и прикладываете файлик. А можно, чтобы там была не 32 или 40-символьная UUID абракадабра, а циферки, чтобы клиенты могли их нам по телефону адекватно передавать». Даже люди вне IT, к сожалению, уже знают про наш lastInsertId.

Ещё порция непрошенных советов

• PDO::MYSQL_ATTR_INIT_COMMAND можно использовать для SET NAMES в сочетании с charset=utf8mb4 в DSN строке.

Мы знаем, что для согласования кодировки в соединении и кодировки в DSN строке можно использовать SET NAMES. Когда у вас MySQL, можете использовать MYSQL_ATTR_INIT_COMMAND для вставки SET NAMES, и он выполнится при инициализации соединения.

• Выключайте PDO::ATTR_EMULATE_PREPARES. 

Я рекомендую (и не только я) выключать EMULATE_PREPARES, то есть подготовку SQL-выражения на стороне клиента. Выключайте, хоть так и можно поддерживать multi-statements. По-моему мнению, вредная штука.

• Помните, что именованные подстановки используются только один раз.

Это произойдёт, если вы воспользуетесь предыдущим советом. Именованные подстановки с одним и тем же именем нельзя будет использовать в запросе к MySQL дважды. PDO будет ругаться, что количество параметров не совпадает.

• Не ленитесь указывать тип используемых данных: если уж биндите данные, указывайте, какой тип данных вы хотите передать. 

bindValue('attr', $value, PDO::PARAM_INT);
// лучше так, чем execute(['attr' => $value]). 

• MYSQL_ATTR_USE_BUFFERED_QUERY=false для больших SQL-запросов. 

Мы переехали на mysqlnd — изменился режим работы с буферизированными запросами. Сделали большой запрос — и это при получении может вызвать переполнение памяти. Выключите и получайте большие объёмы данных из соединения постепенно.

• Избегайте многострочных SQL-запросов в одном вызове. 

Не надо делать запросы через точку с запятой, но если уж так получилось стоит помнить:

• execute() сообщит об ошибке только в первом SQL‑запросе, и необходимо получать следующий resultSet, чтобы выявить там ошибку;

• используйте nextRowSet() для выборки следующего набора;

• nextRowSet() — может вернуть пустой результат, который нельзя выбрать (columnCount()).

Про nextRowSet()

Иногда не получается избежать многострочных запросов. Как пример, тот самый случай с таблицей имеющей триггеры, когда мы хотим в результате выполнения запроса получить вставленный ID:

$sql_insert = <<<SQL
SET NOCOUNT ON;
DECLARE @tmp TABLE ([id] int);
INSERT INTO [table]([val]) OUTPUT INSERTED.id INTO @tmp VALUES('12');
SELECT * FROM @temporary_inserted;
SQL;

$stmt = $pdo->query($sql_insert);
do {
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    echo 'columnCount = ' . var_export($stmt->columnCount(), true) . PHP_EOL;
    echo 'result = ' . var_export($result, true) . PHP_EOL;
} while ($stmt->nextRowset());

Чтобы MSSQL не сообщал нам после каждого запроса «Я вставил одну строку», «Я изменил 0 строк» и т.д., мы выключим этот режим с помощью ‘SET NOCOUNT ON’. Затем создадим временную таблицу, чтобы вернуть в неё вставленный данные с помощью OUTPUT INSERTED, а в завершении из неё сделаем выборку. Так мы получим последний вставленный ID. 

Помня про особенности использования многострочных запросов, перебираем до первого результативного значения, именно того, которое нам необходимо.

Особенности драйверов

Для поддержки MSSQL в PHP существует несколько драйверов, и каждый из них имеет свои особенности. Есть официальный драйвер от Microsoft и есть DBLIB — Free TDS. Раньше часто использовали старенький драйвер с поддержкой SyBase, который, к счастью, сейчас не используют, поэтому про него говорить не будем. 

Два существующих драйвера по-разному работают с примером представленным чуть ранее:

PDO_SQLSRV

columnCount = 1
result = array (
  'id' => '4',
)

PDO_DBLIB

columnCount = 0
result = false

columnCount = 0
result = false

columnCount = 1
result = array (
  'id' => 5,
)

С драйвером от Microsoft будет возвращён всего один результат, а вот в случае с DBLib их будет три. Не будет resultSet, только от декларации временной таблицы, но и так было не всегда. Раньше мы могли получить ошибку, даже при запросе nextRowSet. Вот issue по изменению данного поведения:

• Column rowsets to be skipped automatically 

• Empty result set reported for TDS INFO messages

А вы знали, что...

Но и это не все особенности. Знали ли вы, что используя PDO + официальный драйвер от Microsoft, вы не сможете вставить в запрос больше 2100 значений.

В официальном драйвере есть вот такое ограничение:

PDOException: SQLSTATE[IMSSP]: Tried to bind parameter number 2101.  SQL Server supports a maximum of 2100 parameters.

Если вы захотите сделать batch insert, именно это число будет вас ограничивать. По этому поводу есть issue: https://github.com/microsoft/msphpsql/issues/410.

Именно это часто толкает к использованию DBLIB, хотя Microsoft не рекомендует этого делать. 

Ещё немного мелочей

Мы продолжим искать неприятности. Что может пойти не так при обычном SELECT? Итак, у нас версия PHP 7.4/8.0, с тестами всё хорошо. 

// SELECT * FROM simple_table WHERE id=1
var_dump($result);

/*
array(4) {
  'int_col'     => string(4) "-123"
  'bigint_col'  => string(10) "8817806877"
  'float_col'   => string(11) "-12345.6789"
  'numeric_col' => string(6) "-33.22"
}
*/

Но после обновления до версии 8.1 наши тесты ложатся, потому что возвращаемые значения стали более типизированными, int из таблицы БД, теперь возвращается как int, а не как строка. Это связано с тем, что флаг PDO::EMULATE_PREPARES перестал влиять на поведение (если не проинициализирован принудительно).

// SELECT * FROM simple_table WHERE id=1
var_dump($result);

/*
array(4) {
  ["int_col"]     => int(-123)
  ["bigint_col"]  => int(8817806877)
  ["float_col"]   => float(-12345.6789)
  ["numeric_col"] => string(6) "-33.22"
}
*/

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

$pdo = new PDO('mysql:......;charset=utf8', 'username', 'password');

// для версии 8.1
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, 1);

// в драйвере до версии 8.0 флаг включен. С 8.1 флаг не влияет.
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);

/*
array(4) {
  'int_col'     => string(4) "-123"
  'bigint_col'  => string(10) "8817806877"
  'float_col'   => string(11) "-12345.6789"
  'numeric_col' => string(6) "-33.22"
}
*/

Сделали костыль, всё поправили для Yii2, но любопытство меня не покинуло: а почему стало так? Я пошёл в исходники и нашёл вот такое изменение.

Как было — мы получаем все данные из БД, и если значение есть, мы вставляем его в строчку с помощью ZVAL. 

#if SIZEOF_ZEND_LONG==4
    if ((L64(2147483647) < (int64_t) lval) || (L64(-2147483648) > (int64_t) lval)) {
        DBG_INF("stringify");
        tmp_len = sprintf((char *)&tmp, "%" PRIi64, lval);
    } else
#endif /* SIZEOF */
    {
        ZVAL_LONG(zv, (zend_long) lval); /* the cast is safe, we are in the range */
    }
}

if (tmp_len) { // ВОТ ЭТО МЕСТО
    ZVAL_STRINGL(zv, tmp, tmp_len);
}

После рефакторинга получили следующее:

#if SIZEOF_ZEND_LONG==4
    if ((L64(2147483647) < (int64_t) lval) || (L64(-2147483648) > (int64_t) lval)) {
        DBG_INF("stringify");
        ZVAL_STR(zv, zend_i64_to_str(lval));
    } else
#endif /* SIZEOF */
    {
        ZVAL_LONG(zv, (zend_long) lval); /* the cast is safe, we are in the range */
    }
}

Теперь если данные не влезут в int64, то только тогда мы преобразуем их в строку. Видимо именно эта оптимизация заставила наши тесты «краснеть».

Слабая динамическая оптимизация.

Я включил PDO::ATTR_EMULATE_PREPARES и использовал при получении данных флаг PDO::FETCH_BOUND (привязка переменной к получаемому результату), привязал это к переменной с типом int, но ко мне из PDO вернётся строка — вот такой простой способ получить TypeError (пример придуман специально для доклада — не делайте так). 

declare(strict_types=1);

class TypesCheck {
    private int $n = 1;

    public function check(PDO $pdo)
    {
        $stmt = $pdo->query('select int_col from pdo_types');
        $stmt->bindColumn(1, $this->n, PDO::PARAM_INT);
        $this->anyWork($this->n); // int(1)
        $stmt->fetch(PDO::FETCH_BOUND);
        var_dump($this->n); // string(3) "101"
        $this->anyWork($this->n);
        // PHP Fatal error:  Uncaught TypeError: TypesCheck::anyWork()...
    }

    private function anyWork(int $value) { /* any actions */ }
}

Транзакции в PDO

И тут не удержусь от ряда простых советов:

• Следите за PDO::ATTR_ERRMODE (PDO::ERRMODE_SILENT, и т.д.). Включайте режим обработки ошибок PDO::ERRMODE_EXCEPTION, исключения перехватывайте и обрабатывайте.

• DDL в транзакции поддерживают не все DBMS — будет молчаливый autocommit (MySQL, Oracle, MSSQL). 

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

• Вложенные транзакции не поддерживаются (касается многих DBMS). В фреймворках это обычно реализуется через savepoint. 

Вложенные транзакции

Свои методы для честной проверки активности транзакции с запросом состояния у сервера *_handle_in_transaction появились у PostgreSQL и MySQL, начиная с версии PHP 8.0. Они узнают о транзакции, даже если вы стартовали её через SQL. До этого изменения узнать можно было только по состоянию флага, которое изменялось помощью PDO-функций, управляющих транзакциями, например, beginTransaction.

if (pdo_is_in_transaction(dbh)) {
  zend_throw_exception_ex(php_pdo_get_exception(), 0,
        "There is already an active transaction");
  RETURN_THROWS();
}

...
  
static bool pdo_is_in_transaction(pdo_dbh_t *dbh) {
	if (dbh->methods->in_transaction) {
		return dbh->methods->in_transaction(dbh);
	}
	return dbh->in_txn; // boolean флаг
}
...
// А вот метод: dbh->methods->in_transaction
static bool pdo_mysql_in_transaction(pdo_dbh_t *dbh)
{
  pdo_mysql_db_handle *H = (pdo_mysql_db_handle *)dbh->driver_data;
  PDO_DBG_ENTER("pdo_mysql_in_transaction");
  PDO_DBG_RETURN((pdo_mysql_get_server_status(H->server) & SERVER_STATUS_IN_TRANS) != 0);
}

Выполнили PDO::beginTransaction — всё отлично. Если стартовали транзакцию с помощью SQL-запроса “begin transaction”, то драйвер об этом ранее не знал. Теперь знает, потому что мы сходили, посмотрели серверный статус и убедились — находимся мы в транзакции или нет.

Транзакции и MyISAM

Ещё один пример, когда вся БД имеет тип InnoDB, а одна из таблиц с типом MyISAM:

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

$pdo1->beginTransaction(); // true
$insertResult1 = $pdo1->prepare($sql_insert)->execute([':n' => 111]);
$pdo1->rollBack(); // true

$st = $pdo1->prepare('SELECT id,int_col FROM type WHERE int_col=:n');
$st->execute(['n' => 111]);
var_dump($st->fetch(PDO::FETCH_ASSOC));

/*
array(2) {
  'id' => string(1) "3"
  'int_col' => string(3) "111"
}
*/

Хотя документация обещает исключение в таких случаях, мы его не получим, так как драйвер ничего не знает про MyISAM-таблицу, ведь в целом то у нас InnoDB.

Старые версии.

Ещё немного костылей из мира старых драйверов. Раньше мы писали такой костылик для SyBase:

$pdo = new PDO('dsn_string', 'username', 'password');
$pdo->exec('BEGIN TRANSACTION');
$pdo->exec('COMMIT TRANSACTION');
$pdo->exec('ROLLBACK TRANSACTION');

Так мы использовали SQL-запросы для управления транзакциями. С помощью таких костылей можно поддерживать вложенные транзакции (если их поддержка есть на стороне DBMS), даже несмотря на ограничения PDO.

PDO::quote и ¿'

Ещё стоит рассказать историю с просторов сети интернет про PDO::quote и ¿'. Пример о том, что если у вас соединение с кодировкой GBK, то там есть интересный перевернутый знак вопроса и кавычка, и mysql_real_escape_string вам не поможет. Ведь вся безопасность этой функции в том, что мы просто перед апострофами добавляем «» — обратный слэш. Там будет учтена кодировка соединения, которая может быть задана неверно. 

// ext/pdo_mysql/mysql_driver.c 
if (use_national_character_set) {
    *quotedlen = mysql_real_escape_string_quote(H->server, *quoted + 2, unquoted,
        unquotedlen, ''');
    (*quoted)[0] = 'N';
    (*quoted)[1] = ''';

    ++*quotedlen; /* N prefix */
} else {
    *quotedlen = mysql_real_escape_string_quote(H->server, *quoted + 1, unquoted,
        unquotedlen, ''');
    (*quoted)[0] = ''';
}

(*quoted)[++*quotedlen] = ''';
(*quoted)[++*quotedlen] = '';

0x-теричная система счисления

Можно ли вставить строку прямо в SQL-запрос без биндинга и не бояться уязвимостей? Ответ: да, если у вас MSSQL, причём нехитрым способом:

// Данные заэкранируем хексом - фича мсскл
$string = '0x' . bin2hex($value);
$sql = "INSERT INTO [table]([varchar_col]) VALUES($string)";

// Ну и теперь сохраняем строковые данные в BLOB поле MSSQL
if (is_string($value)) {
    return new Expression('CONVERT(VARBINARY(MAX), '.
            ('0x'. bin2hex($value)) .')');
}

MSSQL hex-значение декодирует и вставит. Такое решение у нас реализовано для тех самых ролевых моделей с JSON. Мы вставляем новый expression, и всё работает. Спасибо моему коллеге и другу Андрею Рычкову за то, что научил этому чудесному хаку.

Вместо вывода

Если вы когда-нибудь решите написать свою ORM, теперь вы будете готовы. И будете знать про трудности, с которыми можете столкнуться.

Спасибо Саше Макарову, который дал мне прикоснуться ко всему этому прекрасному, и Wilmer Arambula, который терпит наш с ним рефакторинг и бесконечные споры. 

И, конечно, спасибо вообще всем, кто занимается опенсорсом, потому что кто-то же должен это делать!

Литература и материалы

Странно добавлять данный раздел, но будем честны — без этого не было бы ни доклада, ни статьи

• https://phpdelusions.net/pdo

• https://phpdelusions.net/pdo/fetch_modes

• https://www.php.net/manual/ru/refs.database.php

• https://docs.microsoft.com/ru-RU/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-2016

• https://habr.com/ru/post/141127/

• https://www.sqlitetutorial.net/sqlite-describe-table/

• https://habr.com/ru/post/137664

• https://habr.com/ru/company/vk/blog/234125/

• https://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection/

• https://www.flaticon.com/ru/free-icons/

• https://github.com/yiisoft/yii2/issues/18171

• https://stackoverflow.com/questions/13747481/pdo-lastinsertid-not-working-for-ms-sql

• https://docs.microsoft.com/ru-RU/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-2016

• https://bugs.php.net/bug.php?id=69592

• https://github.com/FreeTDS/freetds/issues/156

• https://github.com/microsoft/msphpsql/issues/410

• https://mneti.ru

Плюс официальная документация к *sql drivers — всегда читайте документацию, в ней есть ответы на все вопросы.

Автор: Валерий

Источник