QueryBuilder.php

Go to the documentation of this file.

<?php
declare(strict_types = 1);
namespace TYPO3\CMS\Core\Database\Query;

/*
 * This file is part of the TYPO3 CMS project.
 *
 * It is free software; you can redistribute it and/or modify it under
 * the terms of the GNU General Public License, either version 2
 * of the License, or any later version.
 *
 * For the full copyright and license information, please read the
 * LICENSE.txt file that was distributed with this source code.
 *
 * The TYPO3 project - inspiring people to share!
 */

use Doctrine\DBAL\Platforms\SQLServerPlatform;
use Doctrine\DBAL\Query\Expression\CompositeExpression;
use TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
use TYPO3\CMS\Core\Database\Query\Restriction\DefaultRestrictionContainer;
use TYPO3\CMS\Core\Database\Query\Restriction\QueryRestrictionContainerInterface;
use TYPO3\CMS\Core\Utility\GeneralUtility;

class QueryBuilder
{
    protected $connection;

    protected $concreteQueryBuilder;

    protected $restrictionContainer;

    protected $additionalRestrictions;

    public function __construct(
        Connection $connection,
        QueryRestrictionContainerInterface $restrictionContainer = null,
        \Doctrine\DBAL\Query\QueryBuilder $concreteQueryBuilder = null,
        array $additionalRestrictions = null
    ) {
        $this->connection = $connection;
        $this->additionalRestrictions = $additionalRestrictions ?: $GLOBALS['TYPO3_CONF_VARS']['DB']['additionalQueryRestrictions'] ?? [];
        $this->setRestrictions($restrictionContainer ?: GeneralUtility::makeInstance(DefaultRestrictionContainer::class));
        $this->concreteQueryBuilder = $concreteQueryBuilder ?: GeneralUtility::makeInstance(\Doctrine\DBAL\Query\QueryBuilder::class, $connection);
    }

    public function getRestrictions()
    {
        return $this->restrictionContainer;
    }

    public function setRestrictions(QueryRestrictionContainerInterface $restrictionContainer)
    {
        foreach ($this->additionalRestrictions as $restrictionClass => $options) {
            if (empty($options['disabled'])) {
                $restriction = GeneralUtility::makeInstance($restrictionClass);
                $restrictionContainer->add($restriction);
            }
        }
        $this->restrictionContainer = $restrictionContainer;
    }

    public function resetRestrictions()
    {
        $this->setRestrictions(GeneralUtility::makeInstance(DefaultRestrictionContainer::class));
    }

    public function expr(): ExpressionBuilder
    {
        return $this->connection->getExpressionBuilder();
    }

    public function getType(): int
    {
        return $this->concreteQueryBuilder->getType();
    }

    public function getConnection(): Connection
    {
        return $this->connection;
    }

    public function getState(): int
    {
        return $this->concreteQueryBuilder->getState();
    }

    public function getConcreteQueryBuilder(): \Doctrine\DBAL\Query\QueryBuilder
    {
        return $this->concreteQueryBuilder;
    }

    public function execute()
    {
        if ($this->getType() !== \Doctrine\DBAL\Query\QueryBuilder::SELECT) {
            return $this->concreteQueryBuilder->execute();
        }

        // Set additional query restrictions
        $originalWhereConditions = $this->addAdditionalWhereConditions();

        $result = $this->concreteQueryBuilder->execute();

        // Restore the original query conditions in case the user keeps
        // on modifying the state.
        $this->concreteQueryBuilder->add('where', $originalWhereConditions, false);

        return $result;
    }

    public function getSQL(): string
    {
        if ($this->getType() !== \Doctrine\DBAL\Query\QueryBuilder::SELECT) {
            return $this->concreteQueryBuilder->getSQL();
        }

        // Set additional query restrictions
        $originalWhereConditions = $this->addAdditionalWhereConditions();

        $sql = $this->concreteQueryBuilder->getSQL();

        // Restore the original query conditions in case the user keeps
        // on modifying the state.
        $this->concreteQueryBuilder->add('where', $originalWhereConditions, false);

        return $sql;
    }

    public function setParameter($key, $value, int $type = null): QueryBuilder
    {
        $this->concreteQueryBuilder->setParameter($key, $value, $type);

        return $this;
    }

    public function setParameters(array $params, array $types = []): QueryBuilder
    {
        $this->concreteQueryBuilder->setParameters($params, $types);

        return $this;
    }

    public function getParameters(): array
    {
        return $this->concreteQueryBuilder->getParameters();
    }

    public function getParameter($key)
    {
        return $this->concreteQueryBuilder->getParameter($key);
    }

    public function getParameterTypes(): array
    {
        return $this->concreteQueryBuilder->getParameterTypes();
    }

    public function getParameterType($key)
    {
        return $this->concreteQueryBuilder->getParameterType($key);
    }

    public function setFirstResult(int $firstResult): QueryBuilder
    {
        $this->concreteQueryBuilder->setFirstResult($firstResult);

        return $this;
    }

    public function getFirstResult(): int
    {
        return (int)$this->concreteQueryBuilder->getFirstResult();
    }

    public function setMaxResults(int $maxResults): QueryBuilder
    {
        $this->concreteQueryBuilder->setMaxResults($maxResults);

        return $this;
    }

    public function getMaxResults(): int
    {
        return (int)$this->concreteQueryBuilder->getMaxResults();
    }

    public function add(string $sqlPartName, string $sqlPart, bool $append = false): QueryBuilder
    {
        $this->concreteQueryBuilder->add($sqlPartName, $sqlPart, $append);

        return $this;
    }

    public function count(string $item): QueryBuilder
    {
        $countExpr = $this->getConnection()->getDatabasePlatform()->getCountExpression(
            $item === '*' ? $item : $this->quoteIdentifier($item)
        );
        $this->concreteQueryBuilder->select($countExpr);

        return $this;
    }

    public function select(string ...$selects): QueryBuilder
    {
        $this->concreteQueryBuilder->select(...$this->quoteIdentifiersForSelect($selects));

        return $this;
    }

    public function addSelect(string ...$selects): QueryBuilder
    {
        $this->concreteQueryBuilder->addSelect(...$this->quoteIdentifiersForSelect($selects));

        return $this;
    }

    public function selectLiteral(string ...$selects): QueryBuilder
    {
        $this->concreteQueryBuilder->select(...$selects);

        return $this;
    }

    public function addSelectLiteral(string ...$selects): QueryBuilder
    {
        $this->concreteQueryBuilder->addSelect(...$selects);

        return $this;
    }

    public function delete(string $delete, string $alias = null): QueryBuilder
    {
        $this->concreteQueryBuilder->delete(
            $this->quoteIdentifier($delete),
            empty($alias) ? $alias : $this->quoteIdentifier($alias)
        );

        return $this;
    }

    public function update(string $update, string $alias = null): QueryBuilder
    {
        $this->concreteQueryBuilder->update(
            $this->quoteIdentifier($update),
            empty($alias) ? $alias : $this->quoteIdentifier($alias)
        );

        return $this;
    }

    public function insert(string $insert): QueryBuilder
    {
        $this->concreteQueryBuilder->insert($this->quoteIdentifier($insert));

        return $this;
    }

    public function from(string $from, string $alias = null): QueryBuilder
    {
        $this->concreteQueryBuilder->from(
            $this->quoteIdentifier($from),
            empty($alias) ? $alias : $this->quoteIdentifier($alias)
        );

        return $this;
    }

    public function join(string $fromAlias, string $join, string $alias, string $condition = null): QueryBuilder
    {
        $this->concreteQueryBuilder->innerJoin(
            $this->quoteIdentifier($fromAlias),
            $this->quoteIdentifier($join),
            $this->quoteIdentifier($alias),
            $condition
        );

        return $this;
    }

    public function innerJoin(string $fromAlias, string $join, string $alias, string $condition = null): QueryBuilder
    {
        $this->concreteQueryBuilder->innerJoin(
            $this->quoteIdentifier($fromAlias),
            $this->quoteIdentifier($join),
            $this->quoteIdentifier($alias),
            $condition
        );

        return $this;
    }

    public function leftJoin(string $fromAlias, string $join, string $alias, string $condition = null): QueryBuilder
    {
        $this->concreteQueryBuilder->leftJoin(
            $this->quoteIdentifier($fromAlias),
            $this->quoteIdentifier($join),
            $this->quoteIdentifier($alias),
            $condition
        );

        return $this;
    }

    public function rightJoin(string $fromAlias, string $join, string $alias, string $condition = null): QueryBuilder
    {
        $this->concreteQueryBuilder->rightJoin(
            $this->quoteIdentifier($fromAlias),
            $this->quoteIdentifier($join),
            $this->quoteIdentifier($alias),
            $condition
        );

        return $this;
    }

    public function set(string $key, $value, bool $createNamedParameter = true): QueryBuilder
    {
        $this->concreteQueryBuilder->set(
            $this->quoteIdentifier($key),
            $createNamedParameter ? $this->createNamedParameter($value) : $value
        );

        return $this;
    }

    public function where(...$predicates): QueryBuilder
    {
        $this->concreteQueryBuilder->where(...$predicates);

        return $this;
    }

    public function andWhere(...$where): QueryBuilder
    {
        $this->concreteQueryBuilder->andWhere(...$where);

        return $this;
    }

    public function orWhere(...$where): QueryBuilder
    {
        $this->concreteQueryBuilder->orWhere(...$where);

        return $this;
    }

    public function groupBy(...$groupBy): QueryBuilder
    {
        $this->concreteQueryBuilder->groupBy(...$this->quoteIdentifiers($groupBy));

        return $this;
    }

    public function addGroupBy(...$groupBy): QueryBuilder
    {
        $this->concreteQueryBuilder->addGroupBy(...$this->quoteIdentifiers($groupBy));

        return $this;
    }

    public function setValue(string $column, $value, bool $createNamedParameter = true): QueryBuilder
    {
        $this->concreteQueryBuilder->setValue(
            $this->quoteIdentifier($column),
            $createNamedParameter ? $this->createNamedParameter($value) : $value
        );

        return $this;
    }

    public function values(array $values, bool $createNamedParameters = true): QueryBuilder
    {
        if ($createNamedParameters === true) {
            foreach ($values as &$value) {
                $value = $this->createNamedParameter($value);
            }
        }

        $this->concreteQueryBuilder->values($this->quoteColumnValuePairs($values));

        return $this;
    }

    public function having(...$having): QueryBuilder
    {
        $this->concreteQueryBuilder->having(...$having);
        return $this;
    }

    public function andHaving(...$having): QueryBuilder
    {
        $this->concreteQueryBuilder->andHaving(...$having);

        return $this;
    }

    public function orHaving(...$having): QueryBuilder
    {
        $this->concreteQueryBuilder->orHaving(...$having);

        return $this;
    }

    public function orderBy(string $fieldName, string $order = null): QueryBuilder
    {
        $this->concreteQueryBuilder->orderBy($this->connection->quoteIdentifier($fieldName), $order);

        return $this;
    }

    public function addOrderBy(string $fieldName, string $order = null): QueryBuilder
    {
        $this->concreteQueryBuilder->addOrderBy($this->connection->quoteIdentifier($fieldName), $order);

        return $this;
    }

    public function getQueryPart(string $queryPartName)
    {
        return $this->concreteQueryBuilder->getQueryPart($queryPartName);
    }

    public function getQueryParts(): array
    {
        return $this->concreteQueryBuilder->getQueryParts();
    }

    public function resetQueryParts(array $queryPartNames = null): QueryBuilder
    {
        $this->concreteQueryBuilder->resetQueryParts($queryPartNames);

        return $this;
    }

    public function resetQueryPart($queryPartName): QueryBuilder
    {
        $this->concreteQueryBuilder->resetQueryPart($queryPartName);

        return $this;
    }

    public function __toString(): string
    {
        return $this->getSQL();
    }

    public function createNamedParameter($value, int $type = \PDO::PARAM_STR, string $placeHolder = null): string
    {
        return $this->concreteQueryBuilder->createNamedParameter($value, $type, $placeHolder);
    }

    public function createPositionalParameter($value, int $type = \PDO::PARAM_STR): string
    {
        return $this->concreteQueryBuilder->createPositionalParameter($value, $type);
    }

    public function escapeLikeWildcards(string $value): string
    {
        return addcslashes($value, '_%');
    }

    public function quote($input, int $type = null)
    {
        return $this->getConnection()->quote($input, $type);
    }

    public function quoteIdentifier(string $identifier): string
    {
        return $this->getConnection()->quoteIdentifier($identifier);
    }

    public function quoteIdentifiers(array $input): array
    {
        return $this->getConnection()->quoteIdentifiers($input);
    }

    public function quoteIdentifiersForSelect(array $input): array
    {
        foreach ($input as &$select) {
            list($fieldName, $alias, $suffix) = array_pad(
                GeneralUtility::trimExplode(
                    ' AS ',
                    str_ireplace(' as ', ' AS ', $select),
                    true,
                    3
                ),
                3,
                null
            );
            if (!empty($suffix)) {
                throw new \InvalidArgumentException(
                    'QueryBuilder::quoteIdentifiersForSelect() could not parse the input "' . $input . '"',
                    1461170686
                );
            }

            // The SQL * operator must not be quoted. As it can only occur either by itself
            // or preceded by a tablename (tablename.*) check if the last character of a select
            // expression is the * and quote only prepended table name. In all other cases the
            // full expression is being quoted.
            if (substr($fieldName, -2) === '.*') {
                $select = $this->quoteIdentifier(substr($fieldName, 0, -2)) . '.*';
            } elseif ($fieldName !== '*') {
                $select = $this->quoteIdentifier($fieldName);
            }

            // Quote the alias for the current fieldName, if given
            if (!empty($alias)) {
                $select .= ' AS ' . $this->quoteIdentifier($alias);
            }
        }
        return $input;
    }

    public function quoteColumnValuePairs(array $input): array
    {
        return $this->getConnection()->quoteColumnValuePairs($input);
    }

    protected function unquoteSingleIdentifier(string $identifier): string
    {
        $identifier = trim($identifier);
        $platform = $this->getConnection()->getDatabasePlatform();
        if ($platform instanceof SQLServerPlatform) {
            // mssql quotes identifiers with [ and ], not a single character
            $identifier = ltrim($identifier, '[');
            $identifier = rtrim($identifier, ']');
        } else {
            $quoteChar = $platform->getIdentifierQuoteCharacter();
            $identifier = trim($identifier, $quoteChar);
            $identifier = str_replace($quoteChar . $quoteChar, $quoteChar, $identifier);
        }
        return $identifier;
    }

    protected function getQueriedTables(): array
    {
        $queriedTables = [];

        // Loop through all FROM tables
        foreach ($this->getQueryPart('from') as $from) {
            $tableName = $this->unquoteSingleIdentifier($from['table']);
            $tableAlias = isset($from['alias']) ? $this->unquoteSingleIdentifier($from['alias']) : $tableName;
            $queriedTables[$tableAlias] = $tableName;
        }

        // Loop through all JOIN tables
        foreach ($this->getQueryPart('join') as $fromTable => $joins) {
            foreach ($joins as $join) {
                $tableName = $this->unquoteSingleIdentifier($join['joinTable']);
                $tableAlias = isset($join['joinAlias']) ? $this->unquoteSingleIdentifier($join['joinAlias']) : $tableName;
                $queriedTables[$tableAlias] = $tableName;
            }
        }

        return $queriedTables;
    }

    protected function addAdditionalWhereConditions()
    {
        $originalWhereConditions = $this->concreteQueryBuilder->getQueryPart('where');
        $expression = $this->restrictionContainer->buildExpression($this->getQueriedTables(), $this->expr());
        // This check would be obsolete, as the composite expression would not add empty expressions anyway
        // But we keep it here to only clone the previous state, in case we really will change it.
        // Once we remove this state preserving functionality, we can remove the count check here
        // and just add the expression to the query builder.
        if ($expression->count() > 0) {
            if ($originalWhereConditions instanceof CompositeExpression) {
                // Save the original query conditions so we can restore
                // them after the query has been built.
                $originalWhereConditions = clone $originalWhereConditions;
            }
            $this->concreteQueryBuilder->andWhere($expression);
        }

        return $originalWhereConditions;
    }

    public function __clone()
    {
        $this->concreteQueryBuilder = clone $this->concreteQueryBuilder;
        $this->restrictionContainer = clone $this->restrictionContainer;
    }
}