1<?php 2 3namespace Doctrine\DBAL; 4 5use Doctrine\DBAL\Types\Type; 6 7use function array_fill; 8use function array_fill_keys; 9use function array_key_exists; 10use function array_keys; 11use function array_merge; 12use function array_slice; 13use function array_values; 14use function count; 15use function implode; 16use function is_int; 17use function key; 18use function ksort; 19use function preg_match_all; 20use function sprintf; 21use function strlen; 22use function strpos; 23use function substr; 24 25use const PREG_OFFSET_CAPTURE; 26 27/** 28 * Utility class that parses sql statements with regard to types and parameters. 29 * 30 * @internal 31 */ 32class SQLParserUtils 33{ 34 /**#@+ 35 * 36 * @deprecated Will be removed as internal implementation details. 37 */ 38 public const POSITIONAL_TOKEN = '\?'; 39 public const NAMED_TOKEN = '(?<!:):[a-zA-Z_][a-zA-Z0-9_]*'; 40 // Quote characters within string literals can be preceded by a backslash. 41 public const ESCAPED_SINGLE_QUOTED_TEXT = "(?:'(?:\\\\)+'|'(?:[^'\\\\]|\\\\'?|'')*')"; 42 public const ESCAPED_DOUBLE_QUOTED_TEXT = '(?:"(?:\\\\)+"|"(?:[^"\\\\]|\\\\"?)*")'; 43 public const ESCAPED_BACKTICK_QUOTED_TEXT = '(?:`(?:\\\\)+`|`(?:[^`\\\\]|\\\\`?)*`)'; 44 /**#@-*/ 45 46 private const ESCAPED_BRACKET_QUOTED_TEXT = '(?<!\b(?i:ARRAY))\[(?:[^\]])*\]'; 47 48 /** 49 * Gets an array of the placeholders in an sql statements as keys and their positions in the query string. 50 * 51 * For a statement with positional parameters, returns a zero-indexed list of placeholder position. 52 * For a statement with named parameters, returns a map of placeholder positions to their parameter names. 53 * 54 * @deprecated Will be removed as internal implementation detail. 55 * 56 * @param string $statement 57 * @param bool $isPositional 58 * 59 * @return int[]|string[] 60 */ 61 public static function getPlaceholderPositions($statement, $isPositional = true) 62 { 63 return $isPositional 64 ? self::getPositionalPlaceholderPositions($statement) 65 : self::getNamedPlaceholderPositions($statement); 66 } 67 68 /** 69 * Returns a zero-indexed list of placeholder position. 70 * 71 * @return list<int> 72 */ 73 private static function getPositionalPlaceholderPositions(string $statement): array 74 { 75 return self::collectPlaceholders( 76 $statement, 77 '?', 78 self::POSITIONAL_TOKEN, 79 static function (string $_, int $placeholderPosition, int $fragmentPosition, array &$carry): void { 80 $carry[] = $placeholderPosition + $fragmentPosition; 81 } 82 ); 83 } 84 85 /** 86 * Returns a map of placeholder positions to their parameter names. 87 * 88 * @return array<int,string> 89 */ 90 private static function getNamedPlaceholderPositions(string $statement): array 91 { 92 return self::collectPlaceholders( 93 $statement, 94 ':', 95 self::NAMED_TOKEN, 96 static function ( 97 string $placeholder, 98 int $placeholderPosition, 99 int $fragmentPosition, 100 array &$carry 101 ): void { 102 $carry[$placeholderPosition + $fragmentPosition] = substr($placeholder, 1); 103 } 104 ); 105 } 106 107 /** 108 * @return mixed[] 109 */ 110 private static function collectPlaceholders( 111 string $statement, 112 string $match, 113 string $token, 114 callable $collector 115 ): array { 116 if (strpos($statement, $match) === false) { 117 return []; 118 } 119 120 $carry = []; 121 122 foreach (self::getUnquotedStatementFragments($statement) as $fragment) { 123 preg_match_all('/' . $token . '/', $fragment[0], $matches, PREG_OFFSET_CAPTURE); 124 foreach ($matches[0] as $placeholder) { 125 $collector($placeholder[0], $placeholder[1], $fragment[1], $carry); 126 } 127 } 128 129 return $carry; 130 } 131 132 /** 133 * For a positional query this method can rewrite the sql statement with regard to array parameters. 134 * 135 * @param string $query SQL query 136 * @param mixed[] $params Query parameters 137 * @param array<int, Type|int|string|null>|array<string, Type|int|string|null> $types Parameter types 138 * 139 * @return mixed[] 140 * 141 * @throws SQLParserUtilsException 142 */ 143 public static function expandListParameters($query, $params, $types) 144 { 145 $isPositional = is_int(key($params)); 146 $arrayPositions = []; 147 $bindIndex = -1; 148 149 if ($isPositional) { 150 // make sure that $types has the same keys as $params 151 // to allow omitting parameters with unspecified types 152 $types += array_fill_keys(array_keys($params), null); 153 154 ksort($params); 155 ksort($types); 156 } 157 158 foreach ($types as $name => $type) { 159 ++$bindIndex; 160 161 if ($type !== Connection::PARAM_INT_ARRAY && $type !== Connection::PARAM_STR_ARRAY) { 162 continue; 163 } 164 165 if ($isPositional) { 166 $name = $bindIndex; 167 } 168 169 $arrayPositions[$name] = false; 170 } 171 172 if (( ! $arrayPositions && $isPositional)) { 173 return [$query, $params, $types]; 174 } 175 176 if ($isPositional) { 177 $paramOffset = 0; 178 $queryOffset = 0; 179 $params = array_values($params); 180 $types = array_values($types); 181 182 $paramPos = self::getPositionalPlaceholderPositions($query); 183 184 foreach ($paramPos as $needle => $needlePos) { 185 if (! isset($arrayPositions[$needle])) { 186 continue; 187 } 188 189 $needle += $paramOffset; 190 $needlePos += $queryOffset; 191 $count = count($params[$needle]); 192 193 $params = array_merge( 194 array_slice($params, 0, $needle), 195 $params[$needle], 196 array_slice($params, $needle + 1) 197 ); 198 199 $types = array_merge( 200 array_slice($types, 0, $needle), 201 $count ? 202 // array needles are at {@link \Doctrine\DBAL\ParameterType} constants 203 // + {@link \Doctrine\DBAL\Connection::ARRAY_PARAM_OFFSET} 204 array_fill(0, $count, $types[$needle] - Connection::ARRAY_PARAM_OFFSET) : 205 [], 206 array_slice($types, $needle + 1) 207 ); 208 209 $expandStr = $count ? implode(', ', array_fill(0, $count, '?')) : 'NULL'; 210 $query = substr($query, 0, $needlePos) . $expandStr . substr($query, $needlePos + 1); 211 212 $paramOffset += $count - 1; // Grows larger by number of parameters minus the replaced needle. 213 $queryOffset += strlen($expandStr) - 1; 214 } 215 216 return [$query, $params, $types]; 217 } 218 219 $queryOffset = 0; 220 $typesOrd = []; 221 $paramsOrd = []; 222 223 $paramPos = self::getNamedPlaceholderPositions($query); 224 225 foreach ($paramPos as $pos => $paramName) { 226 $paramLen = strlen($paramName) + 1; 227 $value = static::extractParam($paramName, $params, true); 228 229 if (! isset($arrayPositions[$paramName]) && ! isset($arrayPositions[':' . $paramName])) { 230 $pos += $queryOffset; 231 $queryOffset -= $paramLen - 1; 232 $paramsOrd[] = $value; 233 $typesOrd[] = static::extractParam($paramName, $types, false, ParameterType::STRING); 234 $query = substr($query, 0, $pos) . '?' . substr($query, $pos + $paramLen); 235 236 continue; 237 } 238 239 $count = count($value); 240 $expandStr = $count > 0 ? implode(', ', array_fill(0, $count, '?')) : 'NULL'; 241 242 foreach ($value as $val) { 243 $paramsOrd[] = $val; 244 $typesOrd[] = static::extractParam($paramName, $types, false) - Connection::ARRAY_PARAM_OFFSET; 245 } 246 247 $pos += $queryOffset; 248 $queryOffset += strlen($expandStr) - $paramLen; 249 $query = substr($query, 0, $pos) . $expandStr . substr($query, $pos + $paramLen); 250 } 251 252 return [$query, $paramsOrd, $typesOrd]; 253 } 254 255 /** 256 * Slice the SQL statement around pairs of quotes and 257 * return string fragments of SQL outside of quoted literals. 258 * Each fragment is captured as a 2-element array: 259 * 260 * 0 => matched fragment string, 261 * 1 => offset of fragment in $statement 262 * 263 * @param string $statement 264 * 265 * @return mixed[][] 266 */ 267 private static function getUnquotedStatementFragments($statement) 268 { 269 $literal = self::ESCAPED_SINGLE_QUOTED_TEXT . '|' . 270 self::ESCAPED_DOUBLE_QUOTED_TEXT . '|' . 271 self::ESCAPED_BACKTICK_QUOTED_TEXT . '|' . 272 self::ESCAPED_BRACKET_QUOTED_TEXT; 273 $expression = sprintf('/((.+(?i:ARRAY)\\[.+\\])|([^\'"`\\[]+))(?:%s)?/s', $literal); 274 275 preg_match_all($expression, $statement, $fragments, PREG_OFFSET_CAPTURE); 276 277 return $fragments[1]; 278 } 279 280 /** 281 * @param string $paramName The name of the parameter (without a colon in front) 282 * @param mixed $paramsOrTypes A hash of parameters or types 283 * @param bool $isParam 284 * @param mixed $defaultValue An optional default value. If omitted, an exception is thrown 285 * 286 * @return mixed 287 * 288 * @throws SQLParserUtilsException 289 */ 290 private static function extractParam($paramName, $paramsOrTypes, $isParam, $defaultValue = null) 291 { 292 if (array_key_exists($paramName, $paramsOrTypes)) { 293 return $paramsOrTypes[$paramName]; 294 } 295 296 // Hash keys can be prefixed with a colon for compatibility 297 if (array_key_exists(':' . $paramName, $paramsOrTypes)) { 298 return $paramsOrTypes[':' . $paramName]; 299 } 300 301 if ($defaultValue !== null) { 302 return $defaultValue; 303 } 304 305 if ($isParam) { 306 throw SQLParserUtilsException::missingParam($paramName); 307 } 308 309 throw SQLParserUtilsException::missingType($paramName); 310 } 311} 312