SQLParserUtils.php 8.35 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
<?php
/*
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This software consists of voluntary contributions made by many individuals
Benjamin Eberlei's avatar
Benjamin Eberlei committed
16
 * and is licensed under the MIT license. For more information, see
17 18 19 20 21 22 23 24
 * <http://www.doctrine-project.org>.
 */


namespace Doctrine\DBAL;

use Doctrine\DBAL\Connection;

25 26 27 28 29 30 31 32
/**
 * Utility class that parses sql statements with regard to types and parameters.
 *
 * @license     http://www.opensource.org/licenses/lgpl-license.php LGPL
 * @link        www.doctrine-project.com
 * @since       2.0
 * @author      Benjamin Eberlei <kontakt@beberlei.de>
 */
33 34
class SQLParserUtils
{
35
    const POSITIONAL_TOKEN = '\?';
Thomas Subera's avatar
Thomas Subera committed
36
    const NAMED_TOKEN      = '(?<!:):[a-zA-Z_][a-zA-Z0-9_]*';
37 38 39 40 41

    // Quote characters within string literals can be preceded by a backslash.
    const ESCAPED_SINGLE_QUOTED_TEXT = "'(?:[^'\\\\]|\\\\'|\\\\\\\\)*'";
    const ESCAPED_DOUBLE_QUOTED_TEXT = '"(?:[^"\\\\]|\\\\"|\\\\\\\\)*"';

42 43
    /**
     * Get an array of the placeholders in an sql statements as keys and their positions in the query string.
44
     *
45 46
     * Returns an integer => integer pair (indexed from zero) for a positional statement
     * and a string => int[] pair for a named statement.
47
     *
48 49 50 51
     * @param string $statement
     * @param bool $isPositional
     * @return array
     */
52
    static public function getPlaceholderPositions($statement, $isPositional = true)
53
    {
54 55 56 57
        $match = ($isPositional) ? '?' : ':';
        if (strpos($statement, $match) === false) {
            return array();
        }
58

59
        $token = ($isPositional) ? self::POSITIONAL_TOKEN : self::NAMED_TOKEN;
60
        $paramMap = array();
61 62 63 64

        foreach (self::getUnquotedStatementFragments($statement) as $fragment) {
            preg_match_all("/$token/", $fragment[0], $matches, PREG_OFFSET_CAPTURE);
            foreach ($matches[0] as $placeholder) {
65
                if ($isPositional) {
66
                    $paramMap[] = $placeholder[1] + $fragment[1];
67
                } else {
68 69
                    $pos = $placeholder[1] + $fragment[1];
                    $paramMap[$pos] = substr($placeholder[0], 1, strlen($placeholder[0]));
70 71 72 73 74 75
                }
            }
        }

        return $paramMap;
    }
76

77
    /**
78
     * For a positional query this method can rewrite the sql statement with regard to array parameters.
79
     *
Fabio B. Silva's avatar
Fabio B. Silva committed
80 81 82
     * @param string    $query  The SQL query to execute.
     * @param array     $params The parameters to bind to the query.
     * @param array     $types  The types the previous parameters are in.
83 84
     *
     * @throws SQLParserUtilsException
85
     * @return array
86 87
     */
    static public function expandListParameters($query, $params, $types)
88
    {
Fabio B. Silva's avatar
Fabio B. Silva committed
89
        $isPositional   = is_int(key($params));
90
        $arrayPositions = array();
Fabio B. Silva's avatar
Fabio B. Silva committed
91 92
        $bindIndex      = -1;

93
        foreach ($types as $name => $type) {
94
            ++$bindIndex;
95

Fabio B. Silva's avatar
Fabio B. Silva committed
96 97 98 99 100 101
            if ($type !== Connection::PARAM_INT_ARRAY && $type !== Connection::PARAM_STR_ARRAY) {
                continue;
            }

            if ($isPositional) {
                $name = $bindIndex;
102
            }
Fabio B. Silva's avatar
Fabio B. Silva committed
103 104

            $arrayPositions[$name] = false;
105
        }
106

107
        if (( ! $arrayPositions && $isPositional)) {
108 109
            return array($query, $params, $types);
        }
110

111
        $paramPos = self::getPlaceholderPositions($query, $isPositional);
Fabio B. Silva's avatar
Fabio B. Silva committed
112

113 114 115
        if ($isPositional) {
            $paramOffset = 0;
            $queryOffset = 0;
Fabio B. Silva's avatar
Fabio B. Silva committed
116

117
            foreach ($paramPos as $needle => $needlePos) {
Fabio B. Silva's avatar
Fabio B. Silva committed
118
                if ( ! isset($arrayPositions[$needle])) {
119 120
                    continue;
                }
121

Fabio B. Silva's avatar
Fabio B. Silva committed
122
                $needle    += $paramOffset;
123
                $needlePos += $queryOffset;
Fabio B. Silva's avatar
Fabio B. Silva committed
124
                $count      = count($params[$needle]);
125

126
                $params = array_merge(
127
                    array_slice($params, 0, $needle),
128
                    $params[$needle],
129
                    array_slice($params, $needle + 1)
130
                );
131

132
                $types = array_merge(
133
                    array_slice($types, 0, $needle),
134 135 136
                    $count ?
                        array_fill(0, $count, $types[$needle] - Connection::ARRAY_PARAM_OFFSET) : // array needles are at PDO::PARAM_* + 100
                        array(),
137
                    array_slice($types, $needle + 1)
138
                );
139

Fabio B. Silva's avatar
Fabio B. Silva committed
140 141
                $expandStr  = implode(", ", array_fill(0, $count, "?"));
                $query      = substr($query, 0, $needlePos) . $expandStr . substr($query, $needlePos + 1);
142

Fabio B. Silva's avatar
Fabio B. Silva committed
143
                $paramOffset += ($count - 1); // Grows larger by number of parameters minus the replaced needle.
144 145
                $queryOffset += (strlen($expandStr) - 1);
            }
146

Fabio B. Silva's avatar
Fabio B. Silva committed
147 148
            return array($query, $params, $types);
        }
149

150

Fabio B. Silva's avatar
Fabio B. Silva committed
151 152 153 154 155
        $queryOffset = 0;
        $typesOrd    = array();
        $paramsOrd   = array();

        foreach ($paramPos as $pos => $paramName) {
156 157
            $paramLen = strlen($paramName) + 1;
            $value    = static::extractParam($paramName, $params, true);
Fabio B. Silva's avatar
Fabio B. Silva committed
158

159
            if ( ! isset($arrayPositions[$paramName]) && ! isset($arrayPositions[':' . $paramName])) {
Fabio B. Silva's avatar
Fabio B. Silva committed
160 161 162
                $pos         += $queryOffset;
                $queryOffset -= ($paramLen - 1);
                $paramsOrd[]  = $value;
163
                $typesOrd[]   = static::extractParam($paramName, $types, false, \PDO::PARAM_STR);
Fabio B. Silva's avatar
Fabio B. Silva committed
164
                $query        = substr($query, 0, $pos) . '?' . substr($query, ($pos + $paramLen));
165

Fabio B. Silva's avatar
Fabio B. Silva committed
166 167 168 169 170 171 172 173
                continue;
            }

            $count      = count($value);
            $expandStr  = $count > 0 ? implode(', ', array_fill(0, $count, '?')) : '?';

            foreach ($value as $val) {
                $paramsOrd[] = $val;
174
                $typesOrd[]  = static::extractParam($paramName, $types, false) - Connection::ARRAY_PARAM_OFFSET;
175
            }
176

Fabio B. Silva's avatar
Fabio B. Silva committed
177 178 179
            $pos         += $queryOffset;
            $queryOffset += (strlen($expandStr) - $paramLen);
            $query        = substr($query, 0, $pos) . $expandStr . substr($query, ($pos + $paramLen));
180
        }
181

Fabio B. Silva's avatar
Fabio B. Silva committed
182
        return array($query, $paramsOrd, $typesOrd);
183
    }
184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202

    /**
     * Slice the SQL statement around pairs of quotes and
     * return string fragments of SQL outside of quoted literals.
     * Each fragment is captured as a 2-element array:
     *
     * 0 => matched fragment string,
     * 1 => offset of fragment in $statement
     *
     * @param string $statement
     * @return array
     */
    static private function getUnquotedStatementFragments($statement)
    {
        $literal = self::ESCAPED_SINGLE_QUOTED_TEXT . '|' . self::ESCAPED_DOUBLE_QUOTED_TEXT;
        preg_match_all("/([^'\"]+)(?:$literal)?/s", $statement, $fragments, PREG_OFFSET_CAPTURE);

        return $fragments[1];
    }
203 204 205 206 207 208 209 210 211 212 213 214

    /**
     * @param string    $paramName      The name of the parameter (without a colon in front)
     * @param array     $paramsOrTypes  A hash of parameters or types
     * @param bool      $isParam
     * @param mixed     $defaultValue   An optional default value. If omitted, an exception is thrown
     *
     * @throws SQLParserUtilsException
     * @return mixed
     */
    static private function extractParam($paramName, $paramsOrTypes, $isParam, $defaultValue = null)
    {
215
        if (array_key_exists($paramName, $paramsOrTypes)) {
216 217 218 219
            return $paramsOrTypes[$paramName];
        }

        // Hash keys can be prefixed with a colon for compatibility
220
        if (array_key_exists(':' . $paramName, $paramsOrTypes)) {
221 222 223 224 225 226 227 228 229 230
            return $paramsOrTypes[':' . $paramName];
        }

        if (null !== $defaultValue) {
            return $defaultValue;
        }

        if ($isParam) {
            throw SQLParserUtilsException::missingParam($paramName);
        }
Benjamin Eberlei's avatar
Benjamin Eberlei committed
231 232

        throw SQLParserUtilsException::missingType($paramName);
233
    }
234
}