SQLParserUtils.php 8.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233
  1. <?php
  2. /*
  3. * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
  4. * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
  5. * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
  6. * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
  7. * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  8. * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
  9. * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
  10. * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
  11. * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  12. * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
  13. * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  14. *
  15. * This software consists of voluntary contributions made by many individuals
  16. * and is licensed under the MIT license. For more information, see
  17. * <http://www.doctrine-project.org>.
  18. */
  19. namespace Doctrine\DBAL;
  20. use Doctrine\DBAL\Connection;
  21. /**
  22. * Utility class that parses sql statements with regard to types and parameters.
  23. *
  24. * @link www.doctrine-project.org
  25. * @since 2.0
  26. * @author Benjamin Eberlei <kontakt@beberlei.de>
  27. */
  28. class SQLParserUtils
  29. {
  30. const POSITIONAL_TOKEN = '\?';
  31. const NAMED_TOKEN = '(?<!:):[a-zA-Z_][a-zA-Z0-9_]*';
  32. // Quote characters within string literals can be preceded by a backslash.
  33. const ESCAPED_SINGLE_QUOTED_TEXT = "'(?:[^'\\\\]|\\\\'|\\\\\\\\)*'";
  34. const ESCAPED_DOUBLE_QUOTED_TEXT = '"(?:[^"\\\\]|\\\\"|\\\\\\\\)*"';
  35. /**
  36. * Gets an array of the placeholders in an sql statements as keys and their positions in the query string.
  37. *
  38. * Returns an integer => integer pair (indexed from zero) for a positional statement
  39. * and a string => int[] pair for a named statement.
  40. *
  41. * @param string $statement
  42. * @param boolean $isPositional
  43. *
  44. * @return array
  45. */
  46. static public function getPlaceholderPositions($statement, $isPositional = true)
  47. {
  48. $match = ($isPositional) ? '?' : ':';
  49. if (strpos($statement, $match) === false) {
  50. return array();
  51. }
  52. $token = ($isPositional) ? self::POSITIONAL_TOKEN : self::NAMED_TOKEN;
  53. $paramMap = array();
  54. foreach (self::getUnquotedStatementFragments($statement) as $fragment) {
  55. preg_match_all("/$token/", $fragment[0], $matches, PREG_OFFSET_CAPTURE);
  56. foreach ($matches[0] as $placeholder) {
  57. if ($isPositional) {
  58. $paramMap[] = $placeholder[1] + $fragment[1];
  59. } else {
  60. $pos = $placeholder[1] + $fragment[1];
  61. $paramMap[$pos] = substr($placeholder[0], 1, strlen($placeholder[0]));
  62. }
  63. }
  64. }
  65. return $paramMap;
  66. }
  67. /**
  68. * For a positional query this method can rewrite the sql statement with regard to array parameters.
  69. *
  70. * @param string $query The SQL query to execute.
  71. * @param array $params The parameters to bind to the query.
  72. * @param array $types The types the previous parameters are in.
  73. *
  74. * @return array
  75. *
  76. * @throws SQLParserUtilsException
  77. */
  78. static public function expandListParameters($query, $params, $types)
  79. {
  80. $isPositional = is_int(key($params));
  81. $arrayPositions = array();
  82. $bindIndex = -1;
  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 array($query, $params, $types);
  95. }
  96. $paramPos = self::getPlaceholderPositions($query, $isPositional);
  97. if ($isPositional) {
  98. $paramOffset = 0;
  99. $queryOffset = 0;
  100. foreach ($paramPos as $needle => $needlePos) {
  101. if ( ! isset($arrayPositions[$needle])) {
  102. continue;
  103. }
  104. $needle += $paramOffset;
  105. $needlePos += $queryOffset;
  106. $count = count($params[$needle]);
  107. $params = array_merge(
  108. array_slice($params, 0, $needle),
  109. $params[$needle],
  110. array_slice($params, $needle + 1)
  111. );
  112. $types = array_merge(
  113. array_slice($types, 0, $needle),
  114. $count ?
  115. array_fill(0, $count, $types[$needle] - Connection::ARRAY_PARAM_OFFSET) : // array needles are at PDO::PARAM_* + 100
  116. array(),
  117. array_slice($types, $needle + 1)
  118. );
  119. $expandStr = $count ? implode(", ", array_fill(0, $count, "?")) : 'NULL';
  120. $query = substr($query, 0, $needlePos) . $expandStr . substr($query, $needlePos + 1);
  121. $paramOffset += ($count - 1); // Grows larger by number of parameters minus the replaced needle.
  122. $queryOffset += (strlen($expandStr) - 1);
  123. }
  124. return array($query, $params, $types);
  125. }
  126. $queryOffset = 0;
  127. $typesOrd = array();
  128. $paramsOrd = array();
  129. foreach ($paramPos as $pos => $paramName) {
  130. $paramLen = strlen($paramName) + 1;
  131. $value = static::extractParam($paramName, $params, true);
  132. if ( ! isset($arrayPositions[$paramName]) && ! isset($arrayPositions[':' . $paramName])) {
  133. $pos += $queryOffset;
  134. $queryOffset -= ($paramLen - 1);
  135. $paramsOrd[] = $value;
  136. $typesOrd[] = static::extractParam($paramName, $types, false, \PDO::PARAM_STR);
  137. $query = substr($query, 0, $pos) . '?' . substr($query, ($pos + $paramLen));
  138. continue;
  139. }
  140. $count = count($value);
  141. $expandStr = $count > 0 ? implode(', ', array_fill(0, $count, '?')) : 'NULL';
  142. foreach ($value as $val) {
  143. $paramsOrd[] = $val;
  144. $typesOrd[] = static::extractParam($paramName, $types, false) - Connection::ARRAY_PARAM_OFFSET;
  145. }
  146. $pos += $queryOffset;
  147. $queryOffset += (strlen($expandStr) - $paramLen);
  148. $query = substr($query, 0, $pos) . $expandStr . substr($query, ($pos + $paramLen));
  149. }
  150. return array($query, $paramsOrd, $typesOrd);
  151. }
  152. /**
  153. * Slice the SQL statement around pairs of quotes and
  154. * return string fragments of SQL outside of quoted literals.
  155. * Each fragment is captured as a 2-element array:
  156. *
  157. * 0 => matched fragment string,
  158. * 1 => offset of fragment in $statement
  159. *
  160. * @param string $statement
  161. * @return array
  162. */
  163. static private function getUnquotedStatementFragments($statement)
  164. {
  165. $literal = self::ESCAPED_SINGLE_QUOTED_TEXT . '|' . self::ESCAPED_DOUBLE_QUOTED_TEXT;
  166. preg_match_all("/([^'\"]+)(?:$literal)?/s", $statement, $fragments, PREG_OFFSET_CAPTURE);
  167. return $fragments[1];
  168. }
  169. /**
  170. * @param string $paramName The name of the parameter (without a colon in front)
  171. * @param array $paramsOrTypes A hash of parameters or types
  172. * @param bool $isParam
  173. * @param mixed $defaultValue An optional default value. If omitted, an exception is thrown
  174. *
  175. * @throws SQLParserUtilsException
  176. * @return mixed
  177. */
  178. static private function extractParam($paramName, $paramsOrTypes, $isParam, $defaultValue = null)
  179. {
  180. if (array_key_exists($paramName, $paramsOrTypes)) {
  181. return $paramsOrTypes[$paramName];
  182. }
  183. // Hash keys can be prefixed with a colon for compatibility
  184. if (array_key_exists(':' . $paramName, $paramsOrTypes)) {
  185. return $paramsOrTypes[':' . $paramName];
  186. }
  187. if (null !== $defaultValue) {
  188. return $defaultValue;
  189. }
  190. if ($isParam) {
  191. throw SQLParserUtilsException::missingParam($paramName);
  192. }
  193. throw SQLParserUtilsException::missingType($paramName);
  194. }
  195. }