PreparedStatement.php

Go to the documentation of this file.

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

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 = array()) {
        $this->query = $query;
        $this->precompiledQueryParts = $precompiledQueryParts;
        $this->table = $table;
        $this->parameters = array();

        // Test if named placeholders are used
        if ($this->hasNamedPlaceholders($query) || count($precompiledQueryParts) > 0) {
            $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] = array(
            'value' => $value,
            'type' => $data_type == self::PARAM_AUTOTYPE ? $this->guessValueType($value) : $data_type
        );
        return $this;
    }

    public function execute(array $input_parameters = array()) {
        $parameterValues = $this->parameters;
        if (!empty($input_parameters)) {
            $parameterValues = array();
            foreach ($input_parameters as $key => $value) {
                $parameterValues[$key] = array(
                    '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 (count($precompiledQueryParts) > 0) {
                $query = implode('', $precompiledQueryParts['queryParts']);
            }
            $this->statement = $GLOBALS['TYPO3_DB']->prepare_PREPAREDquery($query, $precompiledQueryParts);
            if ($this->statement === NULL) {
                return FALSE;
            }
        }

        $combinedTypes = '';
        $values = array();
        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 = array();
            $bindParamArguments[] = $combinedTypes;
            $numberOfExtraParamArguments = count($values);
            for ($i = 0; $i < $numberOfExtraParamArguments; $i++) {
                $bindParamArguments[] = &$values[$i];
            }

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

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

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

        if (count($this->fields) === 0) {
            // 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 = array();

        // 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 = array();
                $this->buffer = array();
                foreach ($this->fields as $field) {
                    $this->buffer[$field] = NULL;
                    $variables[] = &$this->buffer[$field];
                }

                call_user_func_array(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 = array();
            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 = array();
        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 array(
            $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 = count($precompiledQueryParts['queryParts']);
        $newParameterValues = array();
        $hasNamedPlaceholders = FALSE;

        if ($queryPartsCount === 0) {
            $hasNamedPlaceholders = $this->hasNamedPlaceholders($query);
            if ($hasNamedPlaceholders) {
                $query = $this->tokenizeQueryParameterMarkers($query, $parameterValues);
            }
        } elseif (count($parameterValues) > 0) {
            $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 preceeded 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 '__' . \TYPO3\CMS\Core\Utility\GeneralUtility::getRandomHexString(16) . '__';
    }

}