Translated using Weblate (Portuguese)

[phpmyadmin.git] / src / Table / Search.php

<?php

declare(strict_types=1);

namespace PhpMyAdmin\Table;

use PhpMyAdmin\DatabaseInterface;
use PhpMyAdmin\Util;
use PhpMyAdmin\Utils\Gis;

use function count;
use function explode;
use function implode;
use function in_array;
use function is_array;
use function preg_match;
use function str_contains;
use function str_replace;
use function strncasecmp;
use function trim;

final class Search
{
    public function __construct(private DatabaseInterface $dbi)
    {
    }

    /**
     * Builds the sql search query from the post parameters
     *
     * @return string the generated SQL query
     */
    public function buildSqlQuery(): string
    {
        $sqlQuery = 'SELECT ';

        // If only distinct values are needed
        $isDistinct = isset($_POST['distinct']) ? 'true' : 'false';
        if ($isDistinct === 'true') {
            $sqlQuery .= 'DISTINCT ';
        }

        // if all column names were selected to display, we do a 'SELECT *'
        // (more efficient and this helps prevent a problem in IE
        // if one of the rows is edited and we come back to the Select results)
        if (isset($_POST['zoom_submit']) || ! empty($_POST['displayAllColumns'])) {
            $sqlQuery .= '* ';
        } else {
            $columnsToDisplay = $_POST['columnsToDisplay'];
            $quotedColumns = [];
            foreach ($columnsToDisplay as $column) {
                $quotedColumns[] = Util::backquote($column);
            }

            $sqlQuery .= implode(', ', $quotedColumns);
        }

        $sqlQuery .= ' FROM '
            . Util::backquote($_POST['table']);
        $whereClause = $this->generateWhereClause();
        $sqlQuery .= $whereClause;

        // if the search results are to be ordered
        if (isset($_POST['orderByColumn']) && $_POST['orderByColumn'] !== '--nil--') {
            $sqlQuery .= ' ORDER BY '
                . Util::backquote($_POST['orderByColumn'])
                . ' ' . $_POST['order'];
        }

        return $sqlQuery;
    }

    /**
     * Generates the where clause for the SQL search query to be executed
     *
     * @return string the generated where clause
     */
    private function generateWhereClause(): string
    {
        if (isset($_POST['customWhereClause']) && trim($_POST['customWhereClause']) != '') {
            return ' WHERE ' . $_POST['customWhereClause'];
        }

        if (
            ! isset(
                $_POST['criteriaColumnOperators'],
                $_POST['criteriaValues'],
                $_POST['criteriaColumnNames'],
                $_POST['criteriaColumnTypes'],
            )
        ) {
            return '';
        }

        // else continue to form the where clause from column criteria values
        $fullWhereClause = [];
        foreach ($_POST['criteriaColumnOperators'] as $columnIndex => $operator) {
            $unaryFlag = $this->dbi->types->isUnaryOperator($operator);
            $tmpGeomFunc = $_POST['geom_func'][$columnIndex] ?? null;

            $whereClause = $this->getWhereClause(
                $_POST['criteriaValues'][$columnIndex],
                $_POST['criteriaColumnNames'][$columnIndex],
                $_POST['criteriaColumnTypes'][$columnIndex],
                $operator,
                $unaryFlag,
                $tmpGeomFunc,
            );

            if ($whereClause === '') {
                continue;
            }

            $fullWhereClause[] = $whereClause;
        }

        if ($fullWhereClause !== []) {
            return ' WHERE ' . implode(' AND ', $fullWhereClause);
        }

        return '';
    }

    /**
     * Return the where clause for query generation based on the inputs provided.
     *
     * @param mixed       $criteriaValues Search criteria input
     * @param string      $names          Name of the column on which search is submitted
     * @param string      $types          Type of the field
     * @param string      $funcType       Search function/operator
     * @param bool        $unaryFlag      Whether operator unary or not
     * @param string|null $geomFunc       Whether geometry functions should be applied
     *
     * @return string generated where clause.
     */
    private function getWhereClause(
        mixed $criteriaValues,
        string $names,
        string $types,
        string $funcType,
        bool $unaryFlag,
        string|null $geomFunc = null,
    ): string {
        // If geometry function is set
        if ($geomFunc !== null && $geomFunc !== '') {
            return $this->getGeomWhereClause($criteriaValues, $names, $funcType, $types, $geomFunc);
        }

        $backquotedName = Util::backquote($names);
        $where = '';
        if ($unaryFlag) {
            $where = $backquotedName . ' ' . $funcType;
        } elseif (strncasecmp($types, 'enum', 4) == 0 && ! empty($criteriaValues)) {
            $where = $backquotedName;
            $where .= $this->getEnumWhereClause($criteriaValues, $funcType);
        } elseif ($criteriaValues != '') {
            // For these types we quote the value. Even if it's another type
            // (like INT), for a LIKE we always quote the value. MySQL converts
            // strings to numbers and numbers to strings as necessary
            // during the comparison
            $needsQuoting = preg_match('@char|binary|blob|text|set|date|time|year|uuid@i', $types)
                || str_contains($funcType, 'LIKE');

            // LIKE %...%
            if ($funcType === 'LIKE %...%') {
                $funcType = 'LIKE';
                $criteriaValues = '%' . $criteriaValues . '%';
            }

            if ($funcType === 'NOT LIKE %...%') {
                $funcType = 'NOT LIKE';
                $criteriaValues = '%' . $criteriaValues . '%';
            }

            if ($funcType === 'REGEXP ^...$') {
                $funcType = 'REGEXP';
                $criteriaValues = '^' . $criteriaValues . '$';
            }

            if (
                $funcType !== 'IN (...)'
                && $funcType !== 'NOT IN (...)'
                && $funcType !== 'BETWEEN'
                && $funcType !== 'NOT BETWEEN'
            ) {
                return $backquotedName . ' ' . $funcType . ' '
                    . ($needsQuoting ? $this->dbi->quoteString($criteriaValues) : $criteriaValues);
            }

            $funcType = str_replace(' (...)', '', $funcType);

            //Don't explode if this is already an array
            //(Case for (NOT) IN/BETWEEN.)
            $values = is_array($criteriaValues) ? $criteriaValues : explode(',', $criteriaValues);

            // quote values one by one
            $emptyKey = false;
            foreach ($values as $key => $value) {
                if ($value === '') {
                    $emptyKey = $key;
                    $values[$key] = 'NULL';
                    continue;
                }

                if (! $needsQuoting) {
                    continue;
                }

                $values[$key] = $this->dbi->quoteString(trim($value));
            }

            if ($funcType === 'BETWEEN' || $funcType === 'NOT BETWEEN') {
                $where = $backquotedName . ' ' . $funcType . ' '
                    . ($values[0] ?? '')
                    . ' AND ' . ($values[1] ?? '');
            } else { //[NOT] IN
                if ($emptyKey !== false) {
                    unset($values[$emptyKey]);
                }

                $wheres = [];
                if ($values !== []) {
                    $wheres[] = $backquotedName . ' ' . $funcType
                        . ' (' . implode(',', $values) . ')';
                }

                if ($emptyKey !== false) {
                    $wheres[] = $backquotedName . ' IS NULL';
                }

                $where = implode(' OR ', $wheres);
                if (1 < count($wheres)) {
                    $where = '(' . $where . ')';
                }
            }
        }

        return $where;
    }

    /**
     * Return the where clause for a geometrical column.
     *
     * @param mixed       $criteriaValues Search criteria input
     * @param string      $names          Name of the column on which search is submitted
     * @param string      $funcType       Search function/operator
     * @param string      $types          Type of the field
     * @param string|null $geomFunc       Whether geometry functions should be applied
     *
     * @return string part of where clause.
     */
    private function getGeomWhereClause(
        mixed $criteriaValues,
        string $names,
        string $funcType,
        string $types,
        string|null $geomFunc = null,
    ): string {
        $geomUnaryFunctions = ['IsEmpty' => 1, 'IsSimple' => 1, 'IsRing' => 1, 'IsClosed' => 1];
        $where = '';

        // Get details about the geometry functions
        $geomFuncs = Gis::getFunctions($types);

        // If the function takes multiple parameters
        if (str_contains($funcType, 'IS NULL') || str_contains($funcType, 'IS NOT NULL')) {
            return Util::backquote($names) . ' ' . $funcType;
        }

        if ($geomFuncs[$geomFunc]['params'] > 1) {
            // create gis data from the criteria input
            $gisData = Gis::createData($criteriaValues, $this->dbi->getVersion());

            return $geomFunc . '(' . Util::backquote($names)
                . ', ' . $gisData . ')';
        }

        // New output type is the output type of the function being applied
        $type = $geomFuncs[$geomFunc]['type'];
        $geomFunctionApplied = $geomFunc
            . '(' . Util::backquote($names) . ')';

        // If the where clause is something like 'IsEmpty(`spatial_col_name`)'
        if (isset($geomUnaryFunctions[$geomFunc]) && trim($criteriaValues) == '') {
            $where = $geomFunctionApplied;
        } elseif (in_array($type, Gis::getDataTypes(), true) && ! empty($criteriaValues)) {
            // create gis data from the criteria input
            $gisData = Gis::createData($criteriaValues, $this->dbi->getVersion());
            $where = $geomFunctionApplied . ' ' . $funcType . ' ' . $gisData;
        } elseif ($criteriaValues != '') {
            $where = $geomFunctionApplied . ' '
                . $funcType . " '" . $criteriaValues . "'";
        }

        return $where;
    }

    /**
     * Return the where clause in case column's type is ENUM.
     *
     * @param mixed  $criteriaValues Search criteria input
     * @param string $funcType       Search function/operator
     *
     * @return string part of where clause.
     */
    private function getEnumWhereClause(mixed $criteriaValues, string $funcType): string
    {
        if (! is_array($criteriaValues)) {
            $criteriaValues = explode(',', $criteriaValues);
        }

        $enumSelectedCount = count($criteriaValues);
        if ($funcType === '=' && $enumSelectedCount > 1) {
            $funcType = 'IN';
            $parensOpen = '(';
            $parensClose = ')';
        } elseif ($funcType === '!=' && $enumSelectedCount > 1) {
            $funcType = 'NOT IN';
            $parensOpen = '(';
            $parensClose = ')';
        } else {
            $parensOpen = '';
            $parensClose = '';
        }

        $enumWhere = $this->dbi->quoteString($criteriaValues[0]);
        for ($e = 1; $e < $enumSelectedCount; $e++) {
            $enumWhere .= ', ' . $this->dbi->quoteString($criteriaValues[$e]);
        }

        return ' ' . $funcType . ' ' . $parensOpen
            . $enumWhere . $parensClose;
    }
}