typo3typo3-10.xdoctrine-dbal

TYPO3 throws exception when using unqoted named param

I'm trying to execute a raw query with a prepared statement in PDO-like style using unquoted params (cases 1, 1A), anyway it throws an exception:

An exception occurred while executing 'SELECT * FROM pages WHERE title LIKE :title': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':title' at line 1

Also, quoting named param doesn't work (case 2), it doesn't throw an exception but doesn't find anything as well.

Using unnamed/numbered and unquoted params (cases 3, 3A) or executeQuery() instead of prepare() (case 4) works as required. Especially that I would like to use named params, the last one is my choice.

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);
}

Several questions

  1. Why the first case doesn't work as I would expect it after PDO inheritance or how also Doctrine actualy does it?
  2. Are there some disadvantages (if any) of using executeQuery() instead of prepare()?
  3. Should I use prepare() with numbered params instead?
  4. Is there any significant difference between using raw queries instead of QueryBuilder?

Note

I'm aware that for proper work with models data and repositories I can/should use common QueryBuilder interface. This case is for some raw data in my DB that doesn't use data mapping and I'm looking for some performance improvement. pages table is used here only for demonstrating the concept.

Solution

  • In the end it all comes down to a PDO-like statement, however with an essential difference between using mysqli as driver (https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) or pdo_mysql as driver (https://www.php.net/manual/en/pdo.prepared-statements.php).

    The important aspect is mentioned in PDO docs (https://www.php.net/manual/en/pdo.prepare.php):

    PDO will emulate prepared statements/bound parameters for drivers that do not natively support them, and can also rewrite named or question mark style parameter markers to something more appropriate, if the driver supports one style but not the other.

    The code snippets given in the original question are working when using pdo_mysql as driver - instead of mysqli which can be configured in typo3conf/LocalConfiguration.php

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

    Now focussing on the internal details of Doctrine DBAL, which is also just a wrapper for mysqli or pdo_mysql - internally DBAL is using positioned parameters ? and converts named parameters accordingly.

    Actually that happens in Doctrine DBAL's Connection - named parameters are transformed to positioned parameters (no matter which database driver has been used):

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

    gets converted to

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

    Summary

    Besides that, since you're in a TYPO3 environment already, you might want to use it's QueryBuilder that uses prepared statements internally as well.

    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) ?? [];
}