DatabaseStatement::asTickedList() - Code Metrics - Inspection of "Docs review" - symphonycms/symphony-2 - Measure and Improve Code Quality continuously with Scrutinizer

Passed
Pull Request — master (#2835)

unknown
created 2018-05-09 20:36 UTC
DatabaseStatement::asTickedList() A

↳ Parent: DatabaseStatement
Complexity

Conditions	2
Paths	1
Size

Total Lines	8
Code Lines	5
Duplication

Lines	0
Ratio	0 %
Importance

Changes	2
Bugs	0	Features	0
Metric	Value
cc	2
eloc	5
c	2
b	0
f	0
nc	1
nop	1
dl	0
loc	8
rs	9.4285
<?php

/**
 * @package toolkit
 */

/**
 * This class holds all the required data to enable dynamic SQL statement creation.
 * The way it currently works is by keeping an array for SQL parts. Each operation
 * must add their corresponding SQL part.
 *
 * When appending parts, developers should make sure that the SQL is safe.
 * The class also offers methods to sanitize and validate field values.
 *
 * Finally, the class can be inherited by specialized class for particular queries.
 *
 * @see DatabaseQuery
 * @see DatabaseCreate
 * @see DatabaseUpdate
 * @see DatabaseDelete
 * @see DatabaseShow
 * @see DatabaseCreate
 * @see DatabaseAlter
 * @see DatabaseDrop
 * @see DatabaseTruncate
 * @see DatabaseOptimize
 * @see DatabaseSet
 * @see DatabaseStatementException
 */
class DatabaseStatement
{
    /**
     * List of element delimiter
     * @var string
     */
    const LIST_DELIMITER = ', ';

    /**
     * The SQL values delimiter
     */
    const VALUES_DELIMITER = ',';

    /**
     * The SQL part delimiter
     * @var string
     */
    const STATEMENTS_DELIMITER = ' ';

    /**
     * The SQL part end of line
     */
    const FORMATTED_PART_EOL = "\n";

    /**
     * The SQL part tab character
     */
    const FORMATTED_PART_TAB = "\t";

    /**
     * The SQL part delimiter
     */
    const FORMATTED_PART_DELIMITER = self::FORMATTED_PART_EOL . self::FORMATTED_PART_TAB;

    /**
     * Regular Expression that matches SQL functions
     * @var string
     */
    const FCT_PATTERN = '/^([A-Za-z_]+)\((.*)\)$/';

    /**
     * The SQL functions arguments delimiter
     */
    const FCT_ARGS_DELIMITER = ',';

    /**
     * Regular Expression that matches SQL operators +, -, *, /
     * @var string
     */
    const OP_PATTERN = '/\s+([\-\+\*\/])\s+/';

    /**
     * Database object reference
     * @var Database
     */
    private $db;

    /**
     * SQL parts array
     * @var array
     */
    private $sql = [];

    /**
     * SQL values array
     * @see appendValues()
     * @var array
     */
    private $values = [];

    /**
     * SQL parameters cache
     * @see convertToParameterName()
     * @var array
     */
    private $parameters = [];

    /**
     * Placeholder flag: Developer should check if the statement supports name
     * parameters, which is on by default.
     * @var bool
     */
    private $usePlaceholders = false;

    /**
     * Safe flag: Allows old code to still inject illegal characters in their SQL.
     * @see Database::validateSQLQuery()
     * @var boolean
     */
    private $safe = true;

    /**
     * Creates a new DatabaseStatement object, linked to the $db parameter
     * and containing the optional $statement.
     *
     * @param Database $db
     *  The Database reference
     * @param string $statement
     *  An optional string of SQL that will be appended right from the start.
     *  Defaults to an empty string.
     */
    public function __construct(Database $db, $statement = '')
    {
        General::ensureType([
            'statement' => ['var' => $statement, 'type' => 'string'],
        ]);
        $this->db = $db;
        if (!empty($statement)) {
            $this->unsafeAppendSQLPart('statement', $statement);
        }
    }

    /**
     * Destroys all underlying resources
     */
    public function __destruct()
    {
        $this->db = null;
    }

    /**
     * Getter for the underlying database object.
     *
     * @return Database
     */
    final protected function getDB()
    {
        return $this->db;
    }

    /**
     * Getter for the underlying SQL parts array.
     *
     * @return array
     */
    final protected function getSQL()
    {
        return $this->sql;
    }

    /**
     * Returns all the parts for the specified type
     *
     * @param string $type
     *  The type value for the parts to retrieve
     * @return array
     */
    final public function getSQLParts($type)
    {
        return array_filter($this->getSQL(), function ($part) use ($type) {
            if (is_array($type)) {

                return in_array(current(array_keys($part)), $type);
            }
            return current(array_keys($part)) === $type;
        });
    }

    /**
     * Returns true if the statement contains the specified part.
     *
     * @see getSQLParts()
     * @param string $type
     *  The type value for the parts to check for
     * @return bool
     */
    final public function containsSQLParts($type)
    {
        return !empty($this->getSQLParts($type));
    }

    /**
     * Returns the order in which parts needs to be generated.
     * Only those parts will be included when calling generateSQL().
     * When multiple parts can share the same order, use a sub-array.
     * Control characters can be used to merge parts together.
     * Those characters are:
     *  - `(` and `)` which wraps one or more parts in parenthesis
     *  - `,` which joins part with a comma if both the preceding and next parts are not empty
     *
     * @see getSQLParts()
     * @see generateSQL()
     * @return array
     */
    protected function getStatementStructure()
    {
        return ['statement'];
    }

    /**
     * Merges the ordered SQL parts array into a string, joined with the content of the
     * `STATEMENTS_DELIMITER` constant.
     * The order in which the part are merged are given by getStatementStructure().
     *
     * @see generateOrderedSQLParts()
     * @see getStatementStructure()
     * @return string
     *  The resulting SQL string
     */
    final public function generateSQL()
    {
        return implode(self::STATEMENTS_DELIMITER, array_map(function ($part) {
            return current($part);
        }, $this->generateOrderedSQLParts()));
        }, /** @scrutinizer ignore-type */ $this->generateOrderedSQLParts()));
    }

    /**
     * Merges the ordered SQL parts array into a string, joined with specific string in
     * order to create a formatted, human friendly representation of the resulting SQL.
     * The order in which the part are merged are given by getStatementStructure().
     * The string used for each SQL part is given by getSeparatorForPartType().
     *
     * @see FORMATTED_PART_DELIMITER
     * @see FORMATTED_PART_EOL
     * @see FORMATTED_PART_TAB
     * @see getSeparatorForPartType()
     * @see generateOrderedSQLParts()
     * @see getStatementStructure()
     * @return string
     *  The resulting formatted SQL string
     */
    final public function generateFormattedSQL()
    {
        $parts = $this->generateOrderedSQLParts();
        return array_reduce($parts, function ($memo, $part) {
        return array_reduce(/** @scrutinizer ignore-type */ $parts, function ($memo, $part) {
            $type = current(array_keys($part));
            $value = current($part);
            $sep = $this->getSeparatorForPartType($type);
            if (!$memo) {
                return $value;
            }
            return "{$memo}{$sep}{$value}";
        }, null);
    }

    /**
     * Gets the proper separator string for the given $type SQL part type, when
     * generating a formatted SQL statement.
     * The default implementation simply returns value of the `STATEMENTS_DELIMITER` constant.
     *
     * @see generateFormattedSQL()
     * @param string $type
     *  The SQL part type.
     * @return string
     *  The string to use to separate the formatted SQL parts.
     */
    public function getSeparatorForPartType($type)
    {
        General::ensureType([
            'type' => ['var' => $type, 'type' => 'string'],
        ]);
        return self::STATEMENTS_DELIMITER;
    }

    /**
     * Creates the ordered SQL parts array.
     * The order in which the part sorted are given by getStatementStructure().
     *
     * @see getStatementStructure()
     * @return string
     *  The resulting SQL string
     */
    final public function generateOrderedSQLParts()
    {
        $allParts = $this->getStatementStructure();
        $orderedParts = [];
        foreach ($allParts as $ti => $type) {
            if (in_array($type, ['(', ')'])) {
                $orderedParts[] = [$type => $type];
                continue;
            } elseif ($type === self::VALUES_DELIMITER) {
                $before = $this->getSQLParts($allParts[$ti - 1]);
                $after = $this->getSQLParts($allParts[$ti + 1]);
                if (!empty($before) && !empty($after)) {
                    $orderedParts[] = [$type => $type];
                }
                continue;
            }
            $parts = $this->getSQLParts($type);
            foreach ($parts as $pt => $part) {
                $orderedParts[] = $part;
            }
        }
        return $orderedParts;

    }

    /**
     * @internal
     * Appends part $part of type $type into the SQL parts array.
     * Type $type is just a tag value, used to classify parts.
     * This can allow things like filtering out some parts.
     *
     * Only allowed parts will be accepted. The only valid part by default is 'statement'.
     *
     * BEWARE: This method does not validate or sanitize anything, except the
     * type of both parameters, which must be string. This method should be
     * used as a last resort or with properly sanitized values.
     *
     * @see getStatementStructure()
     * @param string $type
     *  The type value for this part
     * @param string $part
     *  The actual SQL code part
     * @return DatabaseStatement
     *  The current instance
     * @throws DatabaseStatementException
     */
    final public function unsafeAppendSQLPart($type, $part)
    {
        General::ensureType([
            'type' => ['var' => $type, 'type' => 'string'],
            'part' => ['var' => $part, 'type' => 'string'],
        ]);
        if (!General::in_array_multi($type, $this->getStatementStructure(), true)) {
        if (!General::/** @scrutinizer ignore-call */ in_array_multi($type, $this->getStatementStructure(), true)) {
            $class = get_class($this);
            throw new DatabaseStatementException("SQL Part type `$type` is not valid for class `$class`");
        }
        $this->sql[] = [$type => $part];
        return $this;
    }

    /**
     * Getter for the array of SQL values sent with the statement
     * to the database server.
     *
     * @return array
     */
    final public function getValues()
    {
        return $this->values;
    }

    /**
     * Appends the specified $values to the SQL values array.
     * This is the proper way to send user input, as those values
     * are send along the SQL statement without any concatenation.
     * It is safer and faster.
     *
     * It supports keyed and numeric arrays.
     * When using a keyed arrays, keys should be used as SQL named parameters.
     * When using a numeric array, parameters should be place holders (?)
     *
     * @see usePlaceholders()
     * @see convertToParameterName()
     * @param array $values
     *  The values to send to the database
     * @return DatabaseStatement
     *  The current instance
     */
    final protected function appendValues(array $values)
    {
        if ($this->isUsingPlaceholders()) {
            $values = array_values($values);
        } else {
            foreach ($values as $key => $value) {
                if (is_string($key)) {
                    $safeKey = $this->convertToParameterName($key, $value);
                    if ($key !== $safeKey) {
                        unset($values[$key]);
                        $values[$safeKey] = $value;
                    }
                }
            }
        }
        $this->values = array_merge($this->values, $values);
        return $this;
    }

    /**
     * Statement parameter setter. This function bypasses the automatic parameter generation
     * to allow the developer to set values as if using PDO directly.
     * This is sometimes needed when dealing with complex custom queries.
     * You should rather consider to sub class the DatabaseStatement and use appendValues() instead.
     *
     * @param mixed $key
     *  The key of the value, either its index or name
     * @param mixed $value
     *  The actual user provided value
     * @return DatabaseStatement
     *  The current instance
     * @throws DatabaseStatementException
     *  If the key is not the proper type: numeric when using place holders, string if not.
     *  If the key is already set.
     */
    final public function setValue($key, $value)
    {
        if (General::intval($key) > 0) {
            $key = General::intval($key);
            if (!$this->isUsingPlaceholders()) {
                throw new DatabaseStatementException(
                    'Can not use numeric index when using named parameters'
                );
            }
        } elseif (!is_string($key)) {
            throw new DatabaseStatementException('Key for parameter must be a string');
        }
        if (isset($this->values[$key])) {
            throw new DatabaseStatementException("Value for parameter `$key` is already defined");
        }
        $this->values[$key] = $value;
        return $this;
    }

    /**
     * Enable the use of placeholders (?) in the query instead of named parameters.
     *
     * @return DatabaseStatement
     *  The current instance
     */
    final public function usePlaceholders()
    {
        $this->usePlaceholders = true;
        return $this;
    }

    /**
     * If the current statement uses placeholders (?).
     *
     * @return bool
     *  true is the statement uses placeholders
     */
    final public function isUsingPlaceholders()
    {
        return $this->usePlaceholders;
    }

    /**
     * Marks the statement as not safe.
     * This disables strict validation
     *
     * @return DatabaseStatement
     *  The current instance
     */
    final public function unsafe()
    {
        $this->safe = false;
        return $this;
    }

    /**
     * If the current statement is deem safe.
     * Safe statements are validated more strictly
     *
     * @return bool
     *  true is the statement uses placeholders
     */
    final public function isSafe()
    {
        return $this->safe;
    }

    /**
     * @internal This method is not meant to be called directly. Use execute().
     * Appends any remaining part of the statement.
     * Called just before validation and the actual sending of the statement to
     * the SQL server.
     *
     * @see execute()
     * @return DatabaseStatement
     *  The current instance
     */
    public function finalize()
    {
        return $this;
    }

    /**
     * Send the query and all associated values to the server for execution.
     * Calls finalize before sending creating and sending the query to the server.
     *
     * @see Database::execute()
     * @return DatabaseStatementResult
     * @throws DatabaseException
     */
    final public function execute()
    {
        return $this
            ->finalize()
            ->getDB()
            ->execute($this);
    }

    /**
     * Factory function that creates a new DatabaseStatementResult based upon the $result
     * and $stm parameters.
     * Child classes can overwrite this method to return a specialized version of the
     * DatabaseStatementResult class.
     *
     * @param bool $success
     *  If the DatabaseStatement creating this instance succeeded or not.
     * @param PDOStatement $stm
     *  The PDOStatement created by the execution of the DatabaseStatement.
     * @return DatabaseStatementResult
     */
    public function results($success, PDOStatement $stm)
    {
        General::ensureType([
            'success' => ['var' => $success, 'type' => 'bool'],
        ]);
        return new DatabaseStatementResult($success, $stm);
    }

    /**
     * @internal
     * Given a string, replace the default table prefixes with the
     * table prefix for this database instance.
     *
     * @param string $query
     * @return string
     */
    final public function replaceTablePrefix($table)
    {
        General::ensureType([
            'table' => ['var' => $table, 'type' => 'string'],
        ]);
        if ($this->getDB()->getPrefix() != 'tbl_') {
            $table = preg_replace('/tbl_(\S+?)([\s\.,]|$)/', $this->getDB()->getPrefix() .'\\1\\2', trim($table));
        }

        return $table;
    }

    /**
     * @internal
     * Given a valid field name, returns its variant as a SQL parameter.
     * If the $key string is numeric, it will default to placeholders.
     * If enabled, it will use named parameters.
     *
     * @see validateFieldName()
     * @see isUsingPlaceholders()
     * @see usePlaceholders()
     * @see convertToParameterName()
     * @param string $key
     *  The key from which to derive the parameter name from
     * @param mixed $value
     *  The associated value for this key
     * @return string
     *  The parameter expression
     */
    final public function asPlaceholderString($key, $value)
    {
        if (!$this->isUsingPlaceholders() && General::intval($key) === -1) {
            $this->validateFieldName($key);
            $key = $this->convertToParameterName($key, $value);
            return ":$key";
        }
        return '?';
    }

    /**
     * Given an array of valid field names, maps `asPlaceholderString` on each
     * keys and then implodes the resulting array using LIST_DELIMITER
     *
     * @see asPlaceholderString()
     * @see LIST_DELIMITER
     * @param array $values
     * @return void
     */
    final public function asPlaceholdersList(array $values)
    {
        return implode(self::LIST_DELIMITER, General::array_map([$this, 'asPlaceholderString'], $values));
        return implode(self::LIST_DELIMITER, General::array_map(/** @scrutinizer ignore-type */ [$this, 'asPlaceholderString'], $values));
    }

    /**
     * @internal Actually does the tick formatting on the $value string.
     * It makes sure all ticks are removed before validating the value.
     * If the string contains a dot, it will explode it before adding the ticks.
     *
     * @uses validateFieldName()
     * @param string $value
     *  The value to surrounded with ticks
     * @return string
     *  The value surrounded by ticks
     */
    final public function tickString($value)
    {
        General::ensureType([
            'value' => ['var' => $value, 'type' => 'string'],
        ]);
        $value = str_replace('`', '', $value);
        $this->validateFieldName($value);
        if (strpos($value, '.') !== false) {
            return implode('.', array_map([$this, 'asTickedString'], explode('.', $value)));
        }
        return "`$value`";
    }

    /**
     * @internal Splits the arguments of function calls.
     * Arguments are only separated: no formatting is made.
     * Each value should to pass to asTickedString() before being used in SQL queries.
     *
     * @param string $arguments
     *  The argument string to parse
     * @return array
     *  The arguments array
     */
    final public function splitFunctionArguments($arguments)
    {
        $arguments = str_split($arguments);
        $current = [];
        $args = [];
        $openParenthesisCount = 0;
        foreach ($arguments as $char) {
            if (!trim($char)) {
                continue;
            } elseif ($openParenthesisCount === 0 && $char === self::FCT_ARGS_DELIMITER) {
                if (!empty($current)) {
                    $args[] = implode('', $current);
                }
                $current = [];
                continue;
            }
            $current[] = $char;
            if ($char === '(') {
                $openParenthesisCount++;
            } elseif ($char === ')') {
                $openParenthesisCount--;
            }
        }
        if ($openParenthesisCount !== 0) {
            throw new DatabaseStatementException('Imbalanced number of parenthesis in function arguments');
        }
        if (!empty($current)) {
            $args[] = implode('', $current);
        }
        return $args;
    }

    /**
     * @internal
     * Given some value, it will create a ticked string, i.e. "`string`".
     * If the $value parameter is:
     *  1. an array, it will call asPlaceholdersList();
     *  2. the string '*', it will keep it as is;
     *  3. a string matching a function call, it will parse it;
     *  4. a string with a mathematical operator (+, -, *, /), it will parse it;
     *  5. a string with comma, it will explode that string and call
     *     asTickedList() with the resulting array;
     *  6. a string starting with a colon, it will be used as named parameter;
     *  7. a plain string, it will surround all words with ticks.
     *
     * For other type of value, it will throw an Exception.
     *
     * @see asTickedList()
     * @uses tickString()
     * @uses splitFunctionArguments()
     * @param string|array $value
     *  The value or list of values to surround with ticks.
     * @param string $alias
     *  Used as an alias, create `x` AS `y` expressions.
     * @return string
     *  The resulting ticked string or list
     * @throws Exception
     */
    final public function asTickedString($value, $alias = null)
    {
        if (!$value) {
            return '';
        }
        // 1. deal with array
        if (is_array($value)) {
            return $this->asTickedList($value);
        }
        General::ensureType([
            'value' => ['var' => $value, 'type' => 'string'],
        ]);

        $fctMatches = [];
        $opMatches = [];
        $value = trim($value);

        // 2. '*'
        if ($value === '*') {
            return $value;
        // 3. function
        } elseif (preg_match(self::FCT_PATTERN, $value, $fctMatches) === 1) {
            $args = $this->splitFunctionArguments($fctMatches[2]);
            $fxCall = $fctMatches[1] . '(' . $this->asTickedList($args) . ')';
            if ($alias) {
                $alias = $this->tickString($alias);
                return "$fxCall AS $alias";
            }
            return $fxCall;
        // 4. op
        } elseif (preg_match(self::OP_PATTERN, $value, $opMatches) === 1) {
            $op = $opMatches[1];
            if (!$op) {
                throw new DatabaseStatementException("Failed to parse operator in `$value`");
            }
            $parts = array_map('trim', explode($op, $value, 2));
            $parts = array_map(function ($p) {
                // TODO: add support for params
                $ip = General::intval($p);
                return $ip === -1 ? $this->asTickedString($p) : "$ip";
            }, $parts);
            $value = implode(" $op ", $parts);
            if ($alias) {
                $alias = $this->tickString($alias);
                return "($value) AS $alias";
            }
            return $value;
        // 5. comma
        } elseif (strpos($value, self::VALUES_DELIMITER) !== false) {
            return $this->asTickedList(explode(self::VALUES_DELIMITER, $value));
        // 6. colon
        } elseif (strpos($value, ':') === 0) {
            $this->validateFieldName(substr($value, 1));
            return $value;
        }

        // 7. plain string
        $value = $this->tickString($value);
        if ($alias) {
            $alias = $this->tickString($alias);
            return "$value AS $alias";
        }
        return $value;
    }

    /**
     * @internal
     * Given an array, this method will call asTickedString() on each values and
     * then implode the results with LIST_DELIMITER.
     * If the array contains named keys, they become the value and the value in the array
     * is used as an alias, create `x` AS `y` expressions.
     *
     * @see asTickedString()
     * @param array $values
     * @return string
     *  The resulting list of ticked strings
     */
    final public function asTickedList(array $values)
    {
        return implode(self::LIST_DELIMITER, General::array_map(function ($key, $value) {
        return implode(self::LIST_DELIMITER, General::array_map(/** @scrutinizer ignore-type */ function ($key, $value) {
            if (General::intval($key) === -1) {
                return $this->asTickedString($key, $value);
            }
            return $this->asTickedString($value);
        }, $values));
    }

    /**
     * @internal
     * Given an array, this method will call asTickedList() on each values and
     * then implode the results with LIST_DELIMITER.
     * If the value is a DatabaseQuery object, the key is used as the alias.
     *
     * @see asTickedList()
     * @param array $values
     * @return string
     *  The resulting list of ticked strings
     */
    final public function asProjectionList(array $values)
    {
        return implode(self::LIST_DELIMITER, General::array_map(function ($key, $value) {
        return implode(self::LIST_DELIMITER, General::array_map(/** @scrutinizer ignore-type */ function ($key, $value) {
            if ($value instanceof DatabaseSubQuery) {
                $sql = $value->generateSQL();
                $key = $this->asTickedString($key);
                return "($sql) AS $key";
            }
            return $this->asTickedList([$key => $value]);
        }, $values));
    }

    /**
     * @internal
     * This method validates that the string $field is a valid field name
     * in SQL. If it is not, it throws DatabaseStatementException
     *
     * @param string $field
     * @return void
     * @throws DatabaseStatementException
     * @throws Exception
     */
    final protected function validateFieldName($field)
    {
        General::ensureType([
            'field' => ['var' => $field, 'type' => 'string'],
        ]);
        if (preg_match('/^[0-9a-zA-Z_]+$/', $field) === false) {
            throw new DatabaseStatementException(
                "Field name '$field' is not valid since it contains illegal characters"
            );
        }
    }

    /**
     * @internal
     * This function converts a valid field name into a suitable value
     * to use as a SQL parameter name.
     * It also makes sure that the returned parameter name is not currently used
     * for the specified $field, $value pair.
     *
     * @see formatParameterName()
     * @see validateFieldName()
     * @see appendValues()
     * @param string $field
     *  The field name, as passed in the public API of the statement
     * @param mixed $value
     *  The associated value for this field
     * @return string
     *  The sanitized parameter name
     */
    final public function convertToParameterName($field, $value)
    {
        General::ensureType([
            'value' => ['var' => $field, 'type' => 'string'],
        ]);
        $field = str_replace(['-', '.'], '_', $field);
        $field = preg_replace('/[^0-9a-zA-Z_]+/', '', $field);
        $field = $this->formatParameterName($field);

        $uniqueParameterKey = sha1(serialize($field) . serialize($value));
        // Have we seen this (field, value) pair ?
        if (isset($this->parameters[$uniqueParameterKey])) {
            return $this->parameters[$uniqueParameterKey];
        }
        // Have we seen this field ?
        $fieldLookup = $field;
        $fieldCount = 1;
        while (isset($this->parameters[$fieldLookup])) {
            $fieldCount++;
            $fieldLookup = "$field$fieldCount";
        }
        // Special case for null
        if ($value === null) {
            $fieldLookup = "_null_";
        }
        // Saved both for later
        $this->parameters[$uniqueParameterKey] = $this->parameters[$fieldLookup] = $fieldLookup;

        return $fieldLookup;
    }

    /**
     * @internal
     * Formats the given $parameter name to be used as SQL parameter.
     *
     * @param string $parameter
     *  The parameter name
     * @return string
     *  The formatted parameter name
     */
    protected function formatParameterName($parameter)
    {
        return $parameter;
    }
}

symphonycms / symphony-2

GitHub Access Token became invalid

Pull Request — master (#2835)

DatabaseStatement::asTickedList() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like
