SQLParserUtils.php 8.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245
  1. <?php
  2. namespace Doctrine\DBAL;
  3. use const PREG_OFFSET_CAPTURE;
  4. use function array_fill;
  5. use function array_key_exists;
  6. use function array_merge;
  7. use function array_slice;
  8. use function array_values;
  9. use function count;
  10. use function implode;
  11. use function is_int;
  12. use function key;
  13. use function ksort;
  14. use function preg_match_all;
  15. use function sprintf;
  16. use function strlen;
  17. use function strpos;
  18. use function substr;
  19. /**
  20. * Utility class that parses sql statements with regard to types and parameters.
  21. */
  22. class SQLParserUtils
  23. {
  24. public const POSITIONAL_TOKEN = '\?';
  25. public const NAMED_TOKEN = '(?<!:):[a-zA-Z_][a-zA-Z0-9_]*';
  26. // Quote characters within string literals can be preceded by a backslash.
  27. public const ESCAPED_SINGLE_QUOTED_TEXT = "(?:'(?:\\\\\\\\)+'|'(?:[^'\\\\]|\\\\'?|'')*')";
  28. public const ESCAPED_DOUBLE_QUOTED_TEXT = '(?:"(?:\\\\\\\\)+"|"(?:[^"\\\\]|\\\\"?)*")';
  29. public const ESCAPED_BACKTICK_QUOTED_TEXT = '(?:`(?:\\\\\\\\)+`|`(?:[^`\\\\]|\\\\`?)*`)';
  30. private const ESCAPED_BRACKET_QUOTED_TEXT = '(?<!\b(?i:ARRAY))\[(?:[^\]])*\]';
  31. /**
  32. * Gets an array of the placeholders in an sql statements as keys and their positions in the query string.
  33. *
  34. * Returns an integer => integer pair (indexed from zero) for a positional statement
  35. * and a string => int[] pair for a named statement.
  36. *
  37. * @param string $statement
  38. * @param bool $isPositional
  39. *
  40. * @return int[]
  41. */
  42. public static function getPlaceholderPositions($statement, $isPositional = true)
  43. {
  44. $match = $isPositional ? '?' : ':';
  45. if (strpos($statement, $match) === false) {
  46. return [];
  47. }
  48. $token = $isPositional ? self::POSITIONAL_TOKEN : self::NAMED_TOKEN;
  49. $paramMap = [];
  50. foreach (self::getUnquotedStatementFragments($statement) as $fragment) {
  51. preg_match_all('/' . $token . '/', $fragment[0], $matches, PREG_OFFSET_CAPTURE);
  52. foreach ($matches[0] as $placeholder) {
  53. if ($isPositional) {
  54. $paramMap[] = $placeholder[1] + $fragment[1];
  55. } else {
  56. $pos = $placeholder[1] + $fragment[1];
  57. $paramMap[$pos] = substr($placeholder[0], 1, strlen($placeholder[0]));
  58. }
  59. }
  60. }
  61. return $paramMap;
  62. }
  63. /**
  64. * For a positional query this method can rewrite the sql statement with regard to array parameters.
  65. *
  66. * @param string $query The SQL query to execute.
  67. * @param mixed[] $params The parameters to bind to the query.
  68. * @param int[]|string[] $types The types the previous parameters are in.
  69. *
  70. * @return mixed[]
  71. *
  72. * @throws SQLParserUtilsException
  73. */
  74. public static function expandListParameters($query, $params, $types)
  75. {
  76. $isPositional = is_int(key($params));
  77. $arrayPositions = [];
  78. $bindIndex = -1;
  79. if ($isPositional) {
  80. ksort($params);
  81. ksort($types);
  82. }
  83. foreach ($types as $name => $type) {
  84. ++$bindIndex;
  85. if ($type !== Connection::PARAM_INT_ARRAY && $type !== Connection::PARAM_STR_ARRAY) {
  86. continue;
  87. }
  88. if ($isPositional) {
  89. $name = $bindIndex;
  90. }
  91. $arrayPositions[$name] = false;
  92. }
  93. if (( ! $arrayPositions && $isPositional)) {
  94. return [$query, $params, $types];
  95. }
  96. $paramPos = self::getPlaceholderPositions($query, $isPositional);
  97. if ($isPositional) {
  98. $paramOffset = 0;
  99. $queryOffset = 0;
  100. $params = array_values($params);
  101. $types = array_values($types);
  102. foreach ($paramPos as $needle => $needlePos) {
  103. if (! isset($arrayPositions[$needle])) {
  104. continue;
  105. }
  106. $needle += $paramOffset;
  107. $needlePos += $queryOffset;
  108. $count = count($params[$needle]);
  109. $params = array_merge(
  110. array_slice($params, 0, $needle),
  111. $params[$needle],
  112. array_slice($params, $needle + 1)
  113. );
  114. $types = array_merge(
  115. array_slice($types, 0, $needle),
  116. $count ?
  117. // array needles are at {@link \Doctrine\DBAL\ParameterType} constants
  118. // + {@link Doctrine\DBAL\Connection::ARRAY_PARAM_OFFSET}
  119. array_fill(0, $count, $types[$needle] - Connection::ARRAY_PARAM_OFFSET) :
  120. [],
  121. array_slice($types, $needle + 1)
  122. );
  123. $expandStr = $count ? implode(', ', array_fill(0, $count, '?')) : 'NULL';
  124. $query = substr($query, 0, $needlePos) . $expandStr . substr($query, $needlePos + 1);
  125. $paramOffset += ($count - 1); // Grows larger by number of parameters minus the replaced needle.
  126. $queryOffset += (strlen($expandStr) - 1);
  127. }
  128. return [$query, $params, $types];
  129. }
  130. $queryOffset = 0;
  131. $typesOrd = [];
  132. $paramsOrd = [];
  133. foreach ($paramPos as $pos => $paramName) {
  134. $paramLen = strlen($paramName) + 1;
  135. $value = static::extractParam($paramName, $params, true);
  136. if (! isset($arrayPositions[$paramName]) && ! isset($arrayPositions[':' . $paramName])) {
  137. $pos += $queryOffset;
  138. $queryOffset -= ($paramLen - 1);
  139. $paramsOrd[] = $value;
  140. $typesOrd[] = static::extractParam($paramName, $types, false, ParameterType::STRING);
  141. $query = substr($query, 0, $pos) . '?' . substr($query, ($pos + $paramLen));
  142. continue;
  143. }
  144. $count = count($value);
  145. $expandStr = $count > 0 ? implode(', ', array_fill(0, $count, '?')) : 'NULL';
  146. foreach ($value as $val) {
  147. $paramsOrd[] = $val;
  148. $typesOrd[] = static::extractParam($paramName, $types, false) - Connection::ARRAY_PARAM_OFFSET;
  149. }
  150. $pos += $queryOffset;
  151. $queryOffset += (strlen($expandStr) - $paramLen);
  152. $query = substr($query, 0, $pos) . $expandStr . substr($query, ($pos + $paramLen));
  153. }
  154. return [$query, $paramsOrd, $typesOrd];
  155. }
  156. /**
  157. * Slice the SQL statement around pairs of quotes and
  158. * return string fragments of SQL outside of quoted literals.
  159. * Each fragment is captured as a 2-element array:
  160. *
  161. * 0 => matched fragment string,
  162. * 1 => offset of fragment in $statement
  163. *
  164. * @param string $statement
  165. *
  166. * @return mixed[][]
  167. */
  168. private static function getUnquotedStatementFragments($statement)
  169. {
  170. $literal = self::ESCAPED_SINGLE_QUOTED_TEXT . '|' .
  171. self::ESCAPED_DOUBLE_QUOTED_TEXT . '|' .
  172. self::ESCAPED_BACKTICK_QUOTED_TEXT . '|' .
  173. self::ESCAPED_BRACKET_QUOTED_TEXT;
  174. $expression = sprintf('/((.+(?i:ARRAY)\\[.+\\])|([^\'"`\\[]+))(?:%s)?/s', $literal);
  175. preg_match_all($expression, $statement, $fragments, PREG_OFFSET_CAPTURE);
  176. return $fragments[1];
  177. }
  178. /**
  179. * @param string $paramName The name of the parameter (without a colon in front)
  180. * @param mixed $paramsOrTypes A hash of parameters or types
  181. * @param bool $isParam
  182. * @param mixed $defaultValue An optional default value. If omitted, an exception is thrown
  183. *
  184. * @return mixed
  185. *
  186. * @throws SQLParserUtilsException
  187. */
  188. private static function extractParam($paramName, $paramsOrTypes, $isParam, $defaultValue = null)
  189. {
  190. if (array_key_exists($paramName, $paramsOrTypes)) {
  191. return $paramsOrTypes[$paramName];
  192. }
  193. // Hash keys can be prefixed with a colon for compatibility
  194. if (array_key_exists(':' . $paramName, $paramsOrTypes)) {
  195. return $paramsOrTypes[':' . $paramName];
  196. }
  197. if ($defaultValue !== null) {
  198. return $defaultValue;
  199. }
  200. if ($isParam) {
  201. throw SQLParserUtilsException::missingParam($paramName);
  202. }
  203. throw SQLParserUtilsException::missingType($paramName);
  204. }
  205. }