1<?php 2 3namespace Doctrine\DBAL\Driver\OCI8; 4 5use Doctrine\DBAL\Driver\Statement; 6use Doctrine\DBAL\Driver\StatementIterator; 7use Doctrine\DBAL\FetchMode; 8use Doctrine\DBAL\ParameterType; 9use InvalidArgumentException; 10use IteratorAggregate; 11use PDO; 12use const OCI_ASSOC; 13use const OCI_B_BIN; 14use const OCI_B_BLOB; 15use const OCI_BOTH; 16use const OCI_D_LOB; 17use const OCI_FETCHSTATEMENT_BY_COLUMN; 18use const OCI_FETCHSTATEMENT_BY_ROW; 19use const OCI_NUM; 20use const OCI_RETURN_LOBS; 21use const OCI_RETURN_NULLS; 22use const OCI_TEMP_BLOB; 23use const PREG_OFFSET_CAPTURE; 24use const SQLT_CHR; 25use function array_key_exists; 26use function assert; 27use function count; 28use function implode; 29use function is_int; 30use function is_resource; 31use function oci_bind_by_name; 32use function oci_cancel; 33use function oci_error; 34use function oci_execute; 35use function oci_fetch_all; 36use function oci_fetch_array; 37use function oci_fetch_object; 38use function oci_new_descriptor; 39use function oci_num_fields; 40use function oci_num_rows; 41use function oci_parse; 42use function preg_match; 43use function preg_quote; 44use function sprintf; 45use function substr; 46 47/** 48 * The OCI8 implementation of the Statement interface. 49 */ 50class OCI8Statement implements IteratorAggregate, Statement 51{ 52 /** @var resource */ 53 protected $_dbh; 54 55 /** @var resource */ 56 protected $_sth; 57 58 /** @var OCI8Connection */ 59 protected $_conn; 60 61 /** 62 * @deprecated 63 * 64 * @var string 65 */ 66 protected static $_PARAM = ':param'; 67 68 /** @var int[] */ 69 protected static $fetchModeMap = [ 70 FetchMode::MIXED => OCI_BOTH, 71 FetchMode::ASSOCIATIVE => OCI_ASSOC, 72 FetchMode::NUMERIC => OCI_NUM, 73 FetchMode::COLUMN => OCI_NUM, 74 ]; 75 76 /** @var int */ 77 protected $_defaultFetchMode = FetchMode::MIXED; 78 79 /** @var string[] */ 80 protected $_paramMap = []; 81 82 /** 83 * Holds references to bound parameter values. 84 * 85 * This is a new requirement for PHP7's oci8 extension that prevents bound values from being garbage collected. 86 * 87 * @var mixed[] 88 */ 89 private $boundValues = []; 90 91 /** 92 * Indicates whether the statement is in the state when fetching results is possible 93 * 94 * @var bool 95 */ 96 private $result = false; 97 98 /** 99 * Creates a new OCI8Statement that uses the given connection handle and SQL statement. 100 * 101 * @param resource $dbh The connection handle. 102 * @param string $query The SQL query. 103 */ 104 public function __construct($dbh, $query, OCI8Connection $conn) 105 { 106 [$query, $paramMap] = self::convertPositionalToNamedPlaceholders($query); 107 108 $stmt = oci_parse($dbh, $query); 109 assert(is_resource($stmt)); 110 111 $this->_sth = $stmt; 112 $this->_dbh = $dbh; 113 $this->_paramMap = $paramMap; 114 $this->_conn = $conn; 115 } 116 117 /** 118 * Converts positional (?) into named placeholders (:param<num>). 119 * 120 * Oracle does not support positional parameters, hence this method converts all 121 * positional parameters into artificially named parameters. Note that this conversion 122 * is not perfect. All question marks (?) in the original statement are treated as 123 * placeholders and converted to a named parameter. 124 * 125 * The algorithm uses a state machine with two possible states: InLiteral and NotInLiteral. 126 * Question marks inside literal strings are therefore handled correctly by this method. 127 * This comes at a cost, the whole sql statement has to be looped over. 128 * 129 * @param string $statement The SQL statement to convert. 130 * 131 * @return mixed[] [0] => the statement value (string), [1] => the paramMap value (array). 132 * 133 * @throws OCI8Exception 134 * 135 * @todo extract into utility class in Doctrine\DBAL\Util namespace 136 * @todo review and test for lost spaces. we experienced missing spaces with oci8 in some sql statements. 137 */ 138 public static function convertPositionalToNamedPlaceholders($statement) 139 { 140 $fragmentOffset = $tokenOffset = 0; 141 $fragments = $paramMap = []; 142 $currentLiteralDelimiter = null; 143 144 do { 145 if (! $currentLiteralDelimiter) { 146 $result = self::findPlaceholderOrOpeningQuote( 147 $statement, 148 $tokenOffset, 149 $fragmentOffset, 150 $fragments, 151 $currentLiteralDelimiter, 152 $paramMap 153 ); 154 } else { 155 $result = self::findClosingQuote($statement, $tokenOffset, $currentLiteralDelimiter); 156 } 157 } while ($result); 158 159 if ($currentLiteralDelimiter) { 160 throw new OCI8Exception(sprintf( 161 'The statement contains non-terminated string literal starting at offset %d', 162 $tokenOffset - 1 163 )); 164 } 165 166 $fragments[] = substr($statement, $fragmentOffset); 167 $statement = implode('', $fragments); 168 169 return [$statement, $paramMap]; 170 } 171 172 /** 173 * Finds next placeholder or opening quote. 174 * 175 * @param string $statement The SQL statement to parse 176 * @param string $tokenOffset The offset to start searching from 177 * @param int $fragmentOffset The offset to build the next fragment from 178 * @param string[] $fragments Fragments of the original statement not containing placeholders 179 * @param string|null $currentLiteralDelimiter The delimiter of the current string literal 180 * or NULL if not currently in a literal 181 * @param array<int, string> $paramMap Mapping of the original parameter positions to their named replacements 182 * 183 * @return bool Whether the token was found 184 */ 185 private static function findPlaceholderOrOpeningQuote( 186 $statement, 187 &$tokenOffset, 188 &$fragmentOffset, 189 &$fragments, 190 &$currentLiteralDelimiter, 191 &$paramMap 192 ) { 193 $token = self::findToken($statement, $tokenOffset, '/[?\'"]/'); 194 195 if (! $token) { 196 return false; 197 } 198 199 if ($token === '?') { 200 $position = count($paramMap) + 1; 201 $param = ':param' . $position; 202 $fragments[] = substr($statement, $fragmentOffset, $tokenOffset - $fragmentOffset); 203 $fragments[] = $param; 204 $paramMap[$position] = $param; 205 $tokenOffset += 1; 206 $fragmentOffset = $tokenOffset; 207 208 return true; 209 } 210 211 $currentLiteralDelimiter = $token; 212 ++$tokenOffset; 213 214 return true; 215 } 216 217 /** 218 * Finds closing quote 219 * 220 * @param string $statement The SQL statement to parse 221 * @param string $tokenOffset The offset to start searching from 222 * @param string $currentLiteralDelimiter The delimiter of the current string literal 223 * 224 * @return bool Whether the token was found 225 */ 226 private static function findClosingQuote( 227 $statement, 228 &$tokenOffset, 229 &$currentLiteralDelimiter 230 ) { 231 $token = self::findToken( 232 $statement, 233 $tokenOffset, 234 '/' . preg_quote($currentLiteralDelimiter, '/') . '/' 235 ); 236 237 if (! $token) { 238 return false; 239 } 240 241 $currentLiteralDelimiter = false; 242 ++$tokenOffset; 243 244 return true; 245 } 246 247 /** 248 * Finds the token described by regex starting from the given offset. Updates the offset with the position 249 * where the token was found. 250 * 251 * @param string $statement The SQL statement to parse 252 * @param int $offset The offset to start searching from 253 * @param string $regex The regex containing token pattern 254 * 255 * @return string|null Token or NULL if not found 256 */ 257 private static function findToken($statement, &$offset, $regex) 258 { 259 if (preg_match($regex, $statement, $matches, PREG_OFFSET_CAPTURE, $offset)) { 260 $offset = $matches[0][1]; 261 262 return $matches[0][0]; 263 } 264 265 return null; 266 } 267 268 /** 269 * {@inheritdoc} 270 */ 271 public function bindValue($param, $value, $type = ParameterType::STRING) 272 { 273 return $this->bindParam($param, $value, $type, null); 274 } 275 276 /** 277 * {@inheritdoc} 278 */ 279 public function bindParam($column, &$variable, $type = ParameterType::STRING, $length = null) 280 { 281 $column = $this->_paramMap[$column]; 282 283 if ($type === ParameterType::LARGE_OBJECT) { 284 $lob = oci_new_descriptor($this->_dbh, OCI_D_LOB); 285 286 $class = 'OCI-Lob'; 287 assert($lob instanceof $class); 288 289 $lob->writeTemporary($variable, OCI_TEMP_BLOB); 290 291 $variable =& $lob; 292 } 293 294 $this->boundValues[$column] =& $variable; 295 296 return oci_bind_by_name( 297 $this->_sth, 298 $column, 299 $variable, 300 $length ?? -1, 301 $this->convertParameterType($type) 302 ); 303 } 304 305 /** 306 * Converts DBAL parameter type to oci8 parameter type 307 */ 308 private function convertParameterType(int $type) : int 309 { 310 switch ($type) { 311 case ParameterType::BINARY: 312 return OCI_B_BIN; 313 314 case ParameterType::LARGE_OBJECT: 315 return OCI_B_BLOB; 316 317 default: 318 return SQLT_CHR; 319 } 320 } 321 322 /** 323 * {@inheritdoc} 324 */ 325 public function closeCursor() 326 { 327 // not having the result means there's nothing to close 328 if (! $this->result) { 329 return true; 330 } 331 332 oci_cancel($this->_sth); 333 334 $this->result = false; 335 336 return true; 337 } 338 339 /** 340 * {@inheritdoc} 341 */ 342 public function columnCount() 343 { 344 return oci_num_fields($this->_sth) ?: 0; 345 } 346 347 /** 348 * {@inheritdoc} 349 */ 350 public function errorCode() 351 { 352 $error = oci_error($this->_sth); 353 if ($error !== false) { 354 $error = $error['code']; 355 } 356 357 return $error; 358 } 359 360 /** 361 * {@inheritdoc} 362 */ 363 public function errorInfo() 364 { 365 $error = oci_error($this->_sth); 366 367 if ($error === false) { 368 return []; 369 } 370 371 return $error; 372 } 373 374 /** 375 * {@inheritdoc} 376 */ 377 public function execute($params = null) 378 { 379 if ($params) { 380 $hasZeroIndex = array_key_exists(0, $params); 381 382 foreach ($params as $key => $val) { 383 if ($hasZeroIndex && is_int($key)) { 384 $this->bindValue($key + 1, $val); 385 } else { 386 $this->bindValue($key, $val); 387 } 388 } 389 } 390 391 $ret = @oci_execute($this->_sth, $this->_conn->getExecuteMode()); 392 if (! $ret) { 393 throw OCI8Exception::fromErrorInfo($this->errorInfo()); 394 } 395 396 $this->result = true; 397 398 return $ret; 399 } 400 401 /** 402 * {@inheritdoc} 403 */ 404 public function setFetchMode($fetchMode, $arg2 = null, $arg3 = null) 405 { 406 $this->_defaultFetchMode = $fetchMode; 407 408 return true; 409 } 410 411 /** 412 * {@inheritdoc} 413 */ 414 public function getIterator() 415 { 416 return new StatementIterator($this); 417 } 418 419 /** 420 * {@inheritdoc} 421 */ 422 public function fetch($fetchMode = null, $cursorOrientation = PDO::FETCH_ORI_NEXT, $cursorOffset = 0) 423 { 424 // do not try fetching from the statement if it's not expected to contain result 425 // in order to prevent exceptional situation 426 if (! $this->result) { 427 return false; 428 } 429 430 $fetchMode = $fetchMode ?: $this->_defaultFetchMode; 431 432 if ($fetchMode === FetchMode::COLUMN) { 433 return $this->fetchColumn(); 434 } 435 436 if ($fetchMode === FetchMode::STANDARD_OBJECT) { 437 return oci_fetch_object($this->_sth); 438 } 439 440 if (! isset(self::$fetchModeMap[$fetchMode])) { 441 throw new InvalidArgumentException('Invalid fetch style: ' . $fetchMode); 442 } 443 444 return oci_fetch_array( 445 $this->_sth, 446 self::$fetchModeMap[$fetchMode] | OCI_RETURN_NULLS | OCI_RETURN_LOBS 447 ); 448 } 449 450 /** 451 * {@inheritdoc} 452 */ 453 public function fetchAll($fetchMode = null, $fetchArgument = null, $ctorArgs = null) 454 { 455 $fetchMode = $fetchMode ?: $this->_defaultFetchMode; 456 457 $result = []; 458 459 if ($fetchMode === FetchMode::STANDARD_OBJECT) { 460 while ($row = $this->fetch($fetchMode)) { 461 $result[] = $row; 462 } 463 464 return $result; 465 } 466 467 if (! isset(self::$fetchModeMap[$fetchMode])) { 468 throw new InvalidArgumentException('Invalid fetch style: ' . $fetchMode); 469 } 470 471 if (self::$fetchModeMap[$fetchMode] === OCI_BOTH) { 472 while ($row = $this->fetch($fetchMode)) { 473 $result[] = $row; 474 } 475 } else { 476 $fetchStructure = OCI_FETCHSTATEMENT_BY_ROW; 477 478 if ($fetchMode === FetchMode::COLUMN) { 479 $fetchStructure = OCI_FETCHSTATEMENT_BY_COLUMN; 480 } 481 482 // do not try fetching from the statement if it's not expected to contain result 483 // in order to prevent exceptional situation 484 if (! $this->result) { 485 return []; 486 } 487 488 oci_fetch_all( 489 $this->_sth, 490 $result, 491 0, 492 -1, 493 self::$fetchModeMap[$fetchMode] | OCI_RETURN_NULLS | $fetchStructure | OCI_RETURN_LOBS 494 ); 495 496 if ($fetchMode === FetchMode::COLUMN) { 497 $result = $result[0]; 498 } 499 } 500 501 return $result; 502 } 503 504 /** 505 * {@inheritdoc} 506 */ 507 public function fetchColumn($columnIndex = 0) 508 { 509 // do not try fetching from the statement if it's not expected to contain result 510 // in order to prevent exceptional situation 511 if (! $this->result) { 512 return false; 513 } 514 515 $row = oci_fetch_array($this->_sth, OCI_NUM | OCI_RETURN_NULLS | OCI_RETURN_LOBS); 516 517 if ($row === false) { 518 return false; 519 } 520 521 return $row[$columnIndex] ?? null; 522 } 523 524 /** 525 * {@inheritdoc} 526 */ 527 public function rowCount() 528 { 529 return oci_num_rows($this->_sth) ?: 0; 530 } 531} 532