PreparedStatement.php

Go to the documentation of this file.

<?php
namespace TYPO3\CMS\Core\Database;

/*
 * 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\Crypto\Random;
use TYPO3\CMS\Core\Utility\GeneralUtility;

class PreparedStatement
{
    const PARAM_NULL = 0;

    const PARAM_INT = 1;

    const PARAM_STR = 2;

    const PARAM_BOOL = 3;

    const PARAM_AUTOTYPE = 4;

    const FETCH_ASSOC = 2;

    const FETCH_NUM = 3;

    protected $query;

    protected $precompiledQueryParts;

    protected $table;

    protected $parameters;

    protected $defaultFetchMode = self::FETCH_ASSOC;

    protected $statement;

    protected $fields;

    protected $buffer;

    protected $parameterWrapToken;

    public function __construct($query, $table, array $precompiledQueryParts = [])
    {
        GeneralUtility::logDeprecatedFunction();
        $this->query = $query;
        $this->precompiledQueryParts = $precompiledQueryParts;
        $this->table = $table;
        $this->parameters = [];

        // Test if named placeholders are used
        if ($this->hasNamedPlaceholders($query) || !empty($precompiledQueryParts)) {
            $this->statement = null;
        } else {
            // Only question mark placeholders are used
            $this->statement = $GLOBALS['TYPO3_DB']->prepare_PREPAREDquery($this->query, $this->precompiledQueryParts);
        }

        $this->parameterWrapToken = $this->generateParameterWrapToken();
    }

    public function bindValues(array $values)
    {
        foreach ($values as $parameter => $value) {
            $key = is_int($parameter) ? $parameter + 1 : $parameter;
            $this->bindValue($key, $value, self::PARAM_AUTOTYPE);
        }
        return $this;
    }

    public function bindValue($parameter, $value, $data_type = self::PARAM_AUTOTYPE)
    {
        switch ($data_type) {
            case self::PARAM_INT:
                if (!is_int($value)) {
                    throw new \InvalidArgumentException('$value is not an integer as expected: ' . $value, 1281868686);
                }
                break;
            case self::PARAM_BOOL:
                if (!is_bool($value)) {
                    throw new \InvalidArgumentException('$value is not a boolean as expected: ' . $value, 1281868687);
                }
                break;
            case self::PARAM_NULL:
                if (!is_null($value)) {
                    throw new \InvalidArgumentException('$value is not NULL as expected: ' . $value, 1282489834);
                }
                break;
        }
        if (!is_int($parameter) && !preg_match('/^:[\\w]+$/', $parameter)) {
            throw new \InvalidArgumentException('Parameter names must start with ":" followed by an arbitrary number of alphanumerical characters.', 1395055513);
        }
        $key = is_int($parameter) ? $parameter - 1 : $parameter;
        $this->parameters[$key] = [
            'value' => $value,
            'type' => $data_type == self::PARAM_AUTOTYPE ? $this->guessValueType($value) : $data_type
        ];
        return $this;
    }

    public function execute(array $input_parameters = [])
    {
        $parameterValues = $this->parameters;
        if (!empty($input_parameters)) {
            $parameterValues = [];
            foreach ($input_parameters as $key => $value) {
                $parameterValues[$key] = [
                    'value' => $value,
                    'type' => $this->guessValueType($value)
                ];
            }
        }

        if ($this->statement !== null) {
            // The statement has already been executed, we try to reset it
            // for current run but will set it to NULL if it fails for some
            // reason, just as if it were the first run
            if (!@$this->statement->reset()) {
                $this->statement = null;
            }
        }
        if ($this->statement === null) {
            // The statement has never been executed so we prepare it and
            // store it for further reuse
            $query = $this->query;
            $precompiledQueryParts = $this->precompiledQueryParts;

            $this->convertNamedPlaceholdersToQuestionMarks($query, $parameterValues, $precompiledQueryParts);
            if (!empty($precompiledQueryParts)) {
                $query = implode('', $precompiledQueryParts['queryParts']);
            }
            $this->statement = $GLOBALS['TYPO3_DB']->prepare_PREPAREDquery($query, $precompiledQueryParts);
            if ($this->statement === null) {
                return false;
            }
        }

        $combinedTypes = '';
        $values = [];
        foreach ($parameterValues as $parameterValue) {
            switch ($parameterValue['type']) {
                case self::PARAM_NULL:
                    $type = 's';
                    $value = null;
                    break;
                case self::PARAM_INT:
                    $type = 'i';
                    $value = (int)$parameterValue['value'];
                    break;
                case self::PARAM_STR:
                    $type = 's';
                    $value = $parameterValue['value'];
                    break;
                case self::PARAM_BOOL:
                    $type = 'i';
                    $value = $parameterValue['value'] ? 1 : 0;
                    break;
                default:
                    throw new \InvalidArgumentException(sprintf('Unknown type %s used for parameter %s.', $parameterValue['type'], $key), 1281859196);
            }

            $combinedTypes .= $type;
            $values[] = $value;
        }

        // ->bind_param requires second up to last arguments as references
        if (!empty($combinedTypes)) {
            $bindParamArguments = [];
            $bindParamArguments[] = $combinedTypes;
            $numberOfExtraParamArguments = count($values);
            for ($i = 0; $i < $numberOfExtraParamArguments; $i++) {
                $bindParamArguments[] = &$values[$i];
            }

            call_user_func_array([$this->statement, 'bind_param'], $bindParamArguments);
        }

        $success = $this->statement->execute();

        // Store result
        if (!$success || $this->statement->store_result() === false) {
            return false;
        }

        if (empty($this->fields)) {
            // Store the list of fields
            if ($this->statement instanceof \mysqli_stmt) {
                $result = $this->statement->result_metadata();
                if ($result instanceof \mysqli_result) {
                    $fields = $result->fetch_fields();
                    $result->close();
                }
            } else {
                $fields = $this->statement->fetch_fields();
            }
            if (is_array($fields)) {
                foreach ($fields as $field) {
                    $this->fields[] = $field->name;
                }
            }
        }

        // New result set available
        $this->buffer = null;

        // Empty binding parameters
        $this->parameters = [];

        // Return the success flag
        return $success;
    }

    public function fetch($fetch_style = 0)
    {
        if ($fetch_style == 0) {
            $fetch_style = $this->defaultFetchMode;
        }

        if ($this->statement instanceof \mysqli_stmt) {
            if ($this->buffer === null) {
                $variables = [];
                $this->buffer = [];
                foreach ($this->fields as $field) {
                    $this->buffer[$field] = null;
                    $variables[] = &$this->buffer[$field];
                }

                call_user_func_array([$this->statement, 'bind_result'], $variables);
            }
            $success = $this->statement->fetch();
            $columns = $this->buffer;
        } else {
            $columns = $this->statement->fetch();
            $success = is_array($columns);
        }

        if ($success) {
            $row = [];
            foreach ($columns as $key => $value) {
                switch ($fetch_style) {
                    case self::FETCH_ASSOC:
                        $row[$key] = $value;
                        break;
                    case self::FETCH_NUM:
                        $row[] = $value;
                        break;
                    default:
                        throw new \InvalidArgumentException('$fetch_style must be either TYPO3\\CMS\\Core\\Database\\PreparedStatement::FETCH_ASSOC or TYPO3\\CMS\\Core\\Database\\PreparedStatement::FETCH_NUM', 1281646455);
                }
            }
        } else {
            $row = false;
        }

        return $row;
    }

    public function seek($rowNumber)
    {
        $success = $this->statement->data_seek((int)$rowNumber);
        if ($this->statement instanceof \mysqli_stmt) {
            // data_seek() does not return anything
            $success = true;
        }
        return $success;
    }

    public function fetchAll($fetch_style = 0)
    {
        $rows = [];
        while (($row = $this->fetch($fetch_style)) !== false) {
            $rows[] = $row;
        }
        return $rows;
    }

    public function free()
    {
        $this->statement->close();
    }

    public function rowCount()
    {
        return $this->statement->num_rows;
    }

    public function errorCode()
    {
        return $this->statement->errno;
    }

    public function errorInfo()
    {
        return [
            $this->statement->errno,
            $this->statement->error
        ];
    }

    public function setFetchMode($mode)
    {
        switch ($mode) {
            case self::FETCH_ASSOC:
            case self::FETCH_NUM:
                $this->defaultFetchMode = $mode;
                break;
            default:
                throw new \InvalidArgumentException('$mode must be either TYPO3\\CMS\\Core\\Database\\PreparedStatement::FETCH_ASSOC or TYPO3\\CMS\\Core\\Database\\PreparedStatement::FETCH_NUM', 1281875340);
        }
    }

    protected function guessValueType($value)
    {
        if (is_bool($value)) {
            $type = self::PARAM_BOOL;
        } elseif (is_int($value)) {
            $type = self::PARAM_INT;
        } elseif (is_null($value)) {
            $type = self::PARAM_NULL;
        } else {
            $type = self::PARAM_STR;
        }
        return $type;
    }

    protected function hasNamedPlaceholders($query)
    {
        $matches = preg_match('/(?<![\\w:]):[\\w]+\\b/', $query);
        return $matches > 0;
    }

    protected function convertNamedPlaceholdersToQuestionMarks(&$query, array &$parameterValues, array &$precompiledQueryParts)
    {
        $queryPartsCount = is_array($precompiledQueryParts['queryParts']) ? count($precompiledQueryParts['queryParts']) : 0;
        $newParameterValues = [];
        $hasNamedPlaceholders = false;

        if ($queryPartsCount === 0) {
            $hasNamedPlaceholders = $this->hasNamedPlaceholders($query);
            if ($hasNamedPlaceholders) {
                $query = $this->tokenizeQueryParameterMarkers($query, $parameterValues);
            }
        } elseif (!empty($parameterValues)) {
            $hasNamedPlaceholders = !is_int(key($parameterValues));
            if ($hasNamedPlaceholders) {
                for ($i = 1; $i < $queryPartsCount; $i += 2) {
                    $key = $precompiledQueryParts['queryParts'][$i];
                    $precompiledQueryParts['queryParts'][$i] = '?';
                    $newParameterValues[] = $parameterValues[$key];
                }
            }
        }

        if ($hasNamedPlaceholders) {
            if ($queryPartsCount === 0) {
                // Convert named placeholders to standard question mark placeholders
                $quotedParamWrapToken = preg_quote($this->parameterWrapToken, '/');
                while (preg_match(
                    '/' . $quotedParamWrapToken . '(.*?)' . $quotedParamWrapToken . '/',
                    $query,
                    $matches
                )) {
                    $key = $matches[1];

                    $newParameterValues[] = $parameterValues[$key];
                    $query = preg_replace(
                        '/' . $quotedParamWrapToken . $key . $quotedParamWrapToken . '/',
                        '?',
                        $query,
                        1
                    );
                }
            }

            $parameterValues = $newParameterValues;
        }
    }

    protected function tokenizeQueryParameterMarkers($query, array $parameterValues)
    {
        $unnamedParameterCount = 0;
        foreach ($parameterValues as $key => $typeValue) {
            if (!is_int($key)) {
                if (!preg_match('/^:[\\w]+$/', $key)) {
                    throw new \InvalidArgumentException('Parameter names must start with ":" followed by an arbitrary number of alphanumerical characters.', 1282348825);
                }
                // Replace the marker (not preceded by a word character or a ':' but
                // followed by a word boundary)
                $query = preg_replace('/(?<![\\w:])' . preg_quote($key, '/') . '\\b/', $this->parameterWrapToken . $key . $this->parameterWrapToken, $query);
            } else {
                $unnamedParameterCount++;
            }
        }
        $parts = explode('?', $query, $unnamedParameterCount + 1);
        $query = implode($this->parameterWrapToken . '?' . $this->parameterWrapToken, $parts);
        return $query;
    }

    protected function generateParameterWrapToken()
    {
        return '__' . GeneralUtility::makeInstance(Random::class)->generateRandomHexString(16) . '__';
    }
}