SQLParserUtils.php 9.12 KB
Newer Older
1 2 3 4
<?php

namespace Doctrine\DBAL;

5
use function array_fill;
6
use function array_fill_keys;
7
use function array_key_exists;
8
use function array_keys;
9 10 11 12 13 14 15 16 17
use function array_merge;
use function array_slice;
use function array_values;
use function count;
use function implode;
use function is_int;
use function key;
use function ksort;
use function preg_match_all;
18
use function sprintf;
19 20 21
use function strlen;
use function strpos;
use function substr;
22

Grégoire Paris's avatar
Grégoire Paris committed
23
use const PREG_OFFSET_CAPTURE;
24

25 26 27
/**
 * Utility class that parses sql statements with regard to types and parameters.
 */
28 29
class SQLParserUtils
{
30 31 32
    private const POSITIONAL_TOKEN = '\?';
    private const NAMED_TOKEN      = '(?<!:):[a-zA-Z_][a-zA-Z0-9_]*';

Sergei Morozov's avatar
Sergei Morozov committed
33
    /**#@+
34
     * Quote characters within string literals can be preceded by a backslash.
Sergei Morozov's avatar
Sergei Morozov committed
35
     */
36 37 38 39
    private const ESCAPED_SINGLE_QUOTED_TEXT   = "(?:'(?:\\\\)+'|'(?:[^'\\\\]|\\\\'?|'')*')";
    private const ESCAPED_DOUBLE_QUOTED_TEXT   = '(?:"(?:\\\\)+"|"(?:[^"\\\\]|\\\\"?)*")';
    private const ESCAPED_BACKTICK_QUOTED_TEXT = '(?:`(?:\\\\)+`|`(?:[^`\\\\]|\\\\`?)*`)';
    private const ESCAPED_BRACKET_QUOTED_TEXT  = '(?<!\b(?i:ARRAY))\[(?:[^\]])*\]';
40 41
    /**#@-*/

Sergei Morozov's avatar
Sergei Morozov committed
42 43 44 45 46
    /**
     * Returns a zero-indexed list of placeholder position.
     *
     * @return int[]
     */
47
    private static function getPositionalPlaceholderPositions(string $statement): array
Sergei Morozov's avatar
Sergei Morozov committed
48 49 50 51 52
    {
        return self::collectPlaceholders(
            $statement,
            '?',
            self::POSITIONAL_TOKEN,
53
            static function (string $_, int $placeholderPosition, int $fragmentPosition, array &$carry): void {
Sergei Morozov's avatar
Sergei Morozov committed
54 55 56 57 58 59 60 61 62 63
                $carry[] = $placeholderPosition + $fragmentPosition;
            }
        );
    }

    /**
     * Returns a map of placeholder positions to their parameter names.
     *
     * @return string[]
     */
64
    private static function getNamedPlaceholderPositions(string $statement): array
Sergei Morozov's avatar
Sergei Morozov committed
65 66 67 68 69
    {
        return self::collectPlaceholders(
            $statement,
            ':',
            self::NAMED_TOKEN,
70
            static function (string $placeholder, int $placeholderPosition, int $fragmentPosition, array &$carry): void {
Sergei Morozov's avatar
Sergei Morozov committed
71 72 73 74 75 76 77 78
                $carry[$placeholderPosition + $fragmentPosition] = substr($placeholder, 1);
            }
        );
    }

    /**
     * @return mixed[]
     */
79
    private static function collectPlaceholders(string $statement, string $match, string $token, callable $collector): array
Sergei Morozov's avatar
Sergei Morozov committed
80
    {
81
        if (strpos($statement, $match) === false) {
82
            return [];
83
        }
84

Sergei Morozov's avatar
Sergei Morozov committed
85
        $carry = [];
86 87

        foreach (self::getUnquotedStatementFragments($statement) as $fragment) {
88
            preg_match_all('/' . $token . '/', $fragment[0], $matches, PREG_OFFSET_CAPTURE);
89
            foreach ($matches[0] as $placeholder) {
Sergei Morozov's avatar
Sergei Morozov committed
90
                $collector($placeholder[0], $placeholder[1], $fragment[1], $carry);
91 92 93
            }
        }

Sergei Morozov's avatar
Sergei Morozov committed
94
        return $carry;
95
    }
96

97
    /**
98
     * For a positional query this method can rewrite the sql statement with regard to array parameters.
99
     *
100 101 102
     * @param string                 $query  The SQL query to execute.
     * @param mixed[]                $params The parameters to bind to the query.
     * @param array<string|int|null> $types  The types the previous parameters are in.
103
     *
104
     * @return mixed[]
Benjamin Morel's avatar
Benjamin Morel committed
105 106
     *
     * @throws SQLParserUtilsException
107
     */
108
    public static function expandListParameters($query, $params, $types)
109
    {
Fabio B. Silva's avatar
Fabio B. Silva committed
110
        $isPositional   = is_int(key($params));
111
        $arrayPositions = [];
Fabio B. Silva's avatar
Fabio B. Silva committed
112 113
        $bindIndex      = -1;

114
        if ($isPositional) {
115 116 117 118
            // make sure that $types has the same keys as $params
            // to allow omitting parameters with unspecified types
            $types += array_fill_keys(array_keys($params), null);

119 120 121 122
            ksort($params);
            ksort($types);
        }

123
        foreach ($types as $name => $type) {
124
            ++$bindIndex;
125

Fabio B. Silva's avatar
Fabio B. Silva committed
126 127 128 129 130 131
            if ($type !== Connection::PARAM_INT_ARRAY && $type !== Connection::PARAM_STR_ARRAY) {
                continue;
            }

            if ($isPositional) {
                $name = $bindIndex;
132
            }
Fabio B. Silva's avatar
Fabio B. Silva committed
133 134

            $arrayPositions[$name] = false;
135
        }
136

137
        if ($isPositional && count($arrayPositions) === 0) {
138
            return [$query, $params, $types];
139
        }
140

141 142 143
        if ($isPositional) {
            $paramOffset = 0;
            $queryOffset = 0;
144 145
            $params      = array_values($params);
            $types       = array_values($types);
Fabio B. Silva's avatar
Fabio B. Silva committed
146

Sergei Morozov's avatar
Sergei Morozov committed
147 148
            $paramPos = self::getPositionalPlaceholderPositions($query);

149
            foreach ($paramPos as $needle => $needlePos) {
150
                if (! isset($arrayPositions[$needle])) {
151 152
                    continue;
                }
153

Fabio B. Silva's avatar
Fabio B. Silva committed
154
                $needle    += $paramOffset;
155
                $needlePos += $queryOffset;
Fabio B. Silva's avatar
Fabio B. Silva committed
156
                $count      = count($params[$needle]);
157

158
                $params = array_merge(
159
                    array_slice($params, 0, $needle),
160
                    $params[$needle],
161
                    array_slice($params, $needle + 1)
162
                );
163

164
                $types = array_merge(
165
                    array_slice($types, 0, $needle),
166
                    $count > 0 ?
167 168 169
                        // array needles are at {@link \Doctrine\DBAL\ParameterType} constants
                        // + {@link Doctrine\DBAL\Connection::ARRAY_PARAM_OFFSET}
                        array_fill(0, $count, $types[$needle] - Connection::ARRAY_PARAM_OFFSET) :
170
                        [],
171
                    array_slice($types, $needle + 1)
172
                );
173

174
                $expandStr = $count > 0 ? implode(', ', array_fill(0, $count, '?')) : 'NULL';
175
                $query     = substr($query, 0, $needlePos) . $expandStr . substr($query, $needlePos + 1);
176

Grégoire Paris's avatar
Grégoire Paris committed
177 178
                $paramOffset += $count - 1; // Grows larger by number of parameters minus the replaced needle.
                $queryOffset += strlen($expandStr) - 1;
179
            }
180

181
            return [$query, $params, $types];
Fabio B. Silva's avatar
Fabio B. Silva committed
182
        }
183

Fabio B. Silva's avatar
Fabio B. Silva committed
184
        $queryOffset = 0;
185 186
        $typesOrd    = [];
        $paramsOrd   = [];
Fabio B. Silva's avatar
Fabio B. Silva committed
187

Sergei Morozov's avatar
Sergei Morozov committed
188 189
        $paramPos = self::getNamedPlaceholderPositions($query);

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

194
            if (! isset($arrayPositions[$paramName]) && ! isset($arrayPositions[':' . $paramName])) {
Fabio B. Silva's avatar
Fabio B. Silva committed
195
                $pos         += $queryOffset;
Grégoire Paris's avatar
Grégoire Paris committed
196
                $queryOffset -= $paramLen - 1;
Fabio B. Silva's avatar
Fabio B. Silva committed
197
                $paramsOrd[]  = $value;
198
                $typesOrd[]   = static::extractParam($paramName, $types, false, ParameterType::STRING);
Grégoire Paris's avatar
Grégoire Paris committed
199
                $query        = substr($query, 0, $pos) . '?' . substr($query, $pos + $paramLen);
200

Fabio B. Silva's avatar
Fabio B. Silva committed
201 202 203
                continue;
            }

204 205
            $count     = count($value);
            $expandStr = $count > 0 ? implode(', ', array_fill(0, $count, '?')) : 'NULL';
Fabio B. Silva's avatar
Fabio B. Silva committed
206 207 208

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

Fabio B. Silva's avatar
Fabio B. Silva committed
212
            $pos         += $queryOffset;
Grégoire Paris's avatar
Grégoire Paris committed
213 214
            $queryOffset += strlen($expandStr) - $paramLen;
            $query        = substr($query, 0, $pos) . $expandStr . substr($query, $pos + $paramLen);
215
        }
216

217
        return [$query, $paramsOrd, $typesOrd];
218
    }
219 220 221 222 223 224 225 226 227 228

    /**
     * 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
229
     *
230
     * @return mixed[][]
231
     */
232
    private static function getUnquotedStatementFragments($statement)
233
    {
234 235 236 237 238 239 240
        $literal    = self::ESCAPED_SINGLE_QUOTED_TEXT . '|' .
            self::ESCAPED_DOUBLE_QUOTED_TEXT . '|' .
            self::ESCAPED_BACKTICK_QUOTED_TEXT . '|' .
            self::ESCAPED_BRACKET_QUOTED_TEXT;
        $expression = sprintf('/((.+(?i:ARRAY)\\[.+\\])|([^\'"`\\[]+))(?:%s)?/s', $literal);

        preg_match_all($expression, $statement, $fragments, PREG_OFFSET_CAPTURE);
241 242 243

        return $fragments[1];
    }
244 245

    /**
246
     * @param string $paramName     The name of the parameter (without a colon in front)
247
     * @param mixed  $paramsOrTypes A hash of parameters or types
248 249
     * @param bool   $isParam
     * @param mixed  $defaultValue  An optional default value. If omitted, an exception is thrown
250 251
     *
     * @return mixed
252 253
     *
     * @throws SQLParserUtilsException
254
     */
255
    private static function extractParam($paramName, $paramsOrTypes, $isParam, $defaultValue = null)
256
    {
257
        if (array_key_exists($paramName, $paramsOrTypes)) {
258 259 260 261
            return $paramsOrTypes[$paramName];
        }

        // Hash keys can be prefixed with a colon for compatibility
262
        if (array_key_exists(':' . $paramName, $paramsOrTypes)) {
263 264 265
            return $paramsOrTypes[':' . $paramName];
        }

266
        if ($defaultValue !== null) {
267 268 269 270 271 272
            return $defaultValue;
        }

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

        throw SQLParserUtilsException::missingType($paramName);
275
    }
276
}