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

Лучше использовать специализированные данные, но мы попытаемся сохранить 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 = <<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.

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

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

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

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

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

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

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

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

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

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

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

Про nextRowSet ()

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

$sql_insert = <<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 по изменению данного поведения:

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

Но и это не все особенности. Знали ли вы, что используя 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] = '\0';

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, который терпит наш с ним рефакторинг и бесконечные споры. 

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

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

© Habrahabr.ru