TYPO3 выдает исключение при использовании именованного параметра без кавычек

Question

Я пытаюсь выполнить необработанный запрос с подготовленным оператором в стиле PDO, используя параметры без кавычек (случаи 1, 1A), в любом случае он выдает исключение:

Возникла исключительная ситуация при выполнении 'SELECT * FROM pages WHERE title LIKE:title': у вас есть ошибка в синтаксисе SQL; проверьте руководство, соответствующее вашей версии сервера MySQL, чтобы найти правильный синтаксис рядом с ':title' в строке 1

Кроме того, цитирование named param не работает (случай 2), оно не генерирует исключение, но также ничего не находит.

Использование безымянных / пронумерованных и не заключенных в кавычки параметров (случаи 3, 3A) или executeQuery() вместо того prepare()(случай 4) работает по мере необходимости. Особенно я хотел бы использовать именованные параметры, последний - мой выбор.

use TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;

... 

public function queryPagesByTitle(string $title = null): array
{
    /** @var Connection $conn */
    $conn = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('pages');

//  Case 1: DOESN'T work with non-quoted params
    $stmt = $conn->prepare("SELECT * FROM pages WHERE title LIKE :title");
    $stmt->execute(['title' => $title]);

//  Case 1A: DOESN'T work with non-quoted params
    $stmt = $conn->prepare("SELECT * FROM pages WHERE title LIKE :title");
    $stmt->bindValue('title', $title, \PDO::PARAM_STR);
    $stmt->execute();

//  Case 1B: DOESN'T work with non-quoted,unique params
    $stmt = $conn->prepare("SELECT * FROM pages WHERE title LIKE :dcUniqueParam");
    $stmt->bindParam('dcUniqueParam', $title, \PDO::PARAM_STR);
    $stmt->execute();

//  Case 1C: DOESN'T work with non-quoted,unique params even with :colon while binding
    $stmt = $conn->prepare("SELECT * FROM pages WHERE title LIKE :dcUniqueParam");
    $stmt->bindParam(':dcUniqueParam', $title, \PDO::PARAM_STR);

//  Case 2: DOESN'T work with quoted params neither, doesn't throw an exception, but doesn;t find anything
    $stmt = $conn->prepare("SELECT * FROM pages WHERE title LIKE ':title'");
    $stmt->execute(['title' => $title]);

//  Case 3: Works with numbered param(s)
    $stmt = $conn->prepare("SELECT * FROM pages WHERE title LIKE ?");
    $stmt->execute([1 => $title]);

//  Case 3A: Works with numbered param(s)
    $stmt = $conn->prepare("SELECT * FROM pages WHERE title LIKE ?");
    $stmt->bindParam(1, $title, \PDO::PARAM_STR);
    $stmt->execute();

//  Case 4: Works with non-quoted named param(s)
    $stmt = $conn->executeQuery(
        "SELECT uid, title FROM pages WHERE title LIKE :title",
        ['title' => $title],
        [\PDO::PARAM_STR]
    );
    return $stmt->fetchAll(FetchMode::ASSOCIATIVE);
}

Несколько вопросов

1. Почему первый случай не работает так, как я ожидал бы после наследования PDO, или как Doctrine на самом деле это делает?
2. Есть ли недостатки (если есть) у использования executeQuery() вместо того prepare()?
3. Должен ли я использовать prepare() вместо этого с пронумерованными параметрами?
4. Есть ли существенная разница между использованием сырых запросов вместо QueryBuilder?

Заметка

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

Answer 1

В конце концов, все сводится к выражению, подобному PDO, однако с существенной разницей между использованием mysqliв качестве драйвера (https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) илиpdo_mysqlв качестве драйвера (https://www.php.net/manual/en/pdo.prepared-statements.php).

Важный аспект упомянут в документации PDO (https://www.php.net/manual/en/pdo.prepare.php):

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

Фрагменты кода, приведенные в исходном вопросе, работают при использовании pdo_mysql как водитель - вместо mysqli который можно настроить в typo3conf/LocalConfiguration.php

$GLOBALS['TYPO3_CONF_VARS']['DB']['Connections']['Default']['driver'] = 'pdo_mysql';

Теперь сосредоточимся на внутренних деталях Doctrine DBAL, который также является оболочкой для mysqli или pdo_mysql - внутренне DBAL использует позиционированные параметры ? и соответственно преобразует именованные параметры.

На самом деле это происходит в Doctrine DBAL Connection - именованные параметры преобразуются в позиционные параметры (независимо от того, какой драйвер базы данных использовался):

SELECT * FROM `pages` WHERE `title` LIKE :dcValue1

конвертируется в

SELECT * FROM `pages` WHERE `title` LIKE ?

Резюме

• DBAL Connection::executeQuery использует Connection::prepareвнутри см. https://github.com/doctrine/dbal/blob/2.10.x/lib/Doctrine/DBAL/Connection.php
• DBAL использует позиционные / нумерованные параметры ? а также - именованные параметры :dcValue просто "виртуальные"
• с помощью $stmt->bindParam напрямую не выполняет этот процесс преобразования, используя Connection::executeQuery делает и объясняет, почему это сработало

---

Кроме того, поскольку вы уже находитесь в среде TYPO3, вы можете захотеть использовать его QueryBuilder который также использует подготовленные операторы для внутренних целей.

public function queryPagesByTitle(string $title = null): array
{
    $builder = GeneralUtility::makeInstance(ConnectionPool::class)
        ->getQueryBuilderForTable('page');
    $stmt = $builder->select('*')
        ->from('pages')
        ->where($builder->expr()->like(
            'title',
            $builder->createNamedParameter(
                '%' . $builder->escapeLikeWildcards($title) . '%',
                \PDO::PARAM_STR
            )
        ))
        ->execute();
    return $stmt->fetchAll(FetchMode::ASSOCIATIVE) ?? [];
}