QueryHelper.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 TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Utility\GeneralUtility;

class QueryHelper
{
    public static function parseOrderBy(string $input): array
    {
        $input = preg_replace('/^(?:ORDER[[:space:]]*BY[[:space:]]*)+/i', '', trim($input)) ?: '';
        $orderExpressions = GeneralUtility::trimExplode(',', $input, true);

        return array_map(
            function ($expression) {
                list($fieldName, $order) = GeneralUtility::trimExplode(' ', $expression, true);

                return [$fieldName, $order];
            },
            $orderExpressions
        );
    }

    public static function parseTableList(string $input): array
    {
        $input = preg_replace('/^(?:FROM[[:space:]]+)+/i', '', trim($input)) ?: '';
        $tableExpressions = GeneralUtility::trimExplode(',', $input, true);

        return array_map(
            function ($expression) {
                list($tableName, $as, $alias) = GeneralUtility::trimExplode(' ', $expression, true);

                if (!empty($as) && strtolower($as) === 'as' && !empty($alias)) {
                    return [$tableName, $alias];
                }
                if (!empty($as) && empty($alias)) {
                    return [$tableName, $as];
                }
                return [$tableName, null];
            },
            $tableExpressions
        );
    }

    public static function parseGroupBy(string $input): array
    {
        $input = preg_replace('/^(?:GROUP[[:space:]]*BY[[:space:]]*)+/i', '', trim($input)) ?: '';

        return GeneralUtility::trimExplode(',', $input, true);
    }

    public static function parseJoin(string $input): array
    {
        $input = trim($input);
        $quoteCharacter = ' ';
        // Check if the tableName is quoted
        if ($input[0] === '`' || $input[0] === '"') {
            $quoteCharacter .= $input[0];
            $input = substr($input, 1);
            $tableName = strtok($input, $quoteCharacter);
        } else {
            $tableName = strtok($input, $quoteCharacter);
        }

        $tableAlias = strtok($quoteCharacter);
        if (strtolower($tableAlias) === 'as') {
            $tableAlias = strtok($quoteCharacter);
            // Skip the next token which must be ON
            strtok(' ');
            $joinCondition = strtok('');
        } elseif (strtolower($tableAlias) === 'on') {
            $tableAlias = null;
            $joinCondition = strtok('');
        } else {
            // Skip the next token which must be ON
            strtok(' ');
            $joinCondition = strtok('');
        }

        // Catch the edge case that the table name is unquoted and the
        // table alias is actually quoted. This will not work in the case
        // that the quoted table alias contains whitespace.
        $firstCharacterOfTableAlias = $tableAlias[0] ?? null;
        if ($firstCharacterOfTableAlias === '`' || $firstCharacterOfTableAlias === '"') {
            $tableAlias = substr($tableAlias, 1, -1);
        }

        $tableAlias = $tableAlias ?: $tableName;

        return ['tableName' => $tableName, 'tableAlias' => $tableAlias, 'joinCondition' => $joinCondition];
    }

    public static function stripLogicalOperatorPrefix(string $constraint): string
    {
        return preg_replace('/^(?:(AND|OR)[[:space:]]*)+/i', '', trim($constraint)) ?: '';
    }

    public static function getDateTimeFormats()
    {
        return [
            'date' => [
                'empty' => '0000-00-00',
                'format' => 'Y-m-d'
            ],
            'datetime' => [
                'empty' => '0000-00-00 00:00:00',
                'format' => 'Y-m-d H:i:s'
            ]
        ];
    }

    public static function quoteDatabaseIdentifiers(Connection $connection, string $sql): string
    {
        if (strpos($sql, '{#') !== false) {
            $sql = preg_replace_callback(
                '/{#(?P<identifier>[^}]+)}/',
                function (array $matches) use ($connection) {
                    return $connection->quoteIdentifier($matches['identifier']);
                },
                $sql
            );
        }

        return $sql;
    }
}