[Summary view] [Print] [Text view]

   1  <?php
   2  /**
   3   * Part of the Joomla Framework Database Package
   4   *
   5   * @copyright  Copyright (C) 2005 - 2021 Open Source Matters, Inc. All rights reserved.
   6   * @license    GNU General Public License version 2 or later; see LICENSE
   7   */
   8  
   9  namespace Joomla\Database\Sqlsrv;
  10  
  11  use Joomla\Database\Exception\ExecutionFailureException;
  12  use Joomla\Database\Exception\PrepareStatementFailureException;
  13  use Joomla\Database\FetchMode;
  14  use Joomla\Database\FetchOrientation;
  15  use Joomla\Database\ParameterType;
  16  use Joomla\Database\StatementInterface;
  17  
  18  /**
  19   * SQL Server Database Statement.
  20   *
  21   * This class is modeled on \Doctrine\DBAL\Driver\SQLSrv\SQLSrvStatement
  22   *
  23   * @since  2.0.0
  24   */
  25  class SqlsrvStatement implements StatementInterface
  26  {
  27      /**
  28       * The database connection resource.
  29       *
  30       * @var    resource
  31       * @since  2.0.0
  32       */
  33      protected $connection;
  34  
  35      /**
  36       * The default fetch mode for the statement.
  37       *
  38       * @var    integer
  39       * @since  2.0.0
  40       */
  41      protected $defaultFetchStyle = FetchMode::MIXED;
  42  
  43      /**
  44       * The default class to use for building object result sets.
  45       *
  46       * @var    integer
  47       * @since  2.0.0
  48       */
  49      protected $defaultObjectClass = \stdClass::class;
  50  
  51      /**
  52       * Mapping array converting fetch modes to the native engine type.
  53       *
  54       * @var    array
  55       * @since  2.0.0
  56       */
  57      private $fetchMap = [
  58          FetchMode::MIXED       => SQLSRV_FETCH_BOTH,
  59          FetchMode::ASSOCIATIVE => SQLSRV_FETCH_ASSOC,
  60          FetchMode::NUMERIC     => SQLSRV_FETCH_NUMERIC,
  61      ];
  62  
  63      /**
  64       * The query string being prepared.
  65       *
  66       * @var    string
  67       * @since  2.0.0
  68       */
  69      protected $query;
  70  
  71      /**
  72       * Internal tracking flag to set whether there is a result set available for processing
  73       *
  74       * @var    boolean
  75       * @since  2.0.0
  76       */
  77      private $result = false;
  78  
  79      /**
  80       * The prepared statement.
  81       *
  82       * @var    resource
  83       * @since  2.0.0
  84       */
  85      protected $statement;
  86  
  87      /**
  88       * Bound parameter types.
  89       *
  90       * @var    array
  91       * @since  2.0.0
  92       */
  93      protected $typesKeyMapping;
  94  
  95      /**
  96       * References to the variables bound as statement parameters.
  97       *
  98       * @var    array
  99       * @since  2.0.0
 100       */
 101      private $bindedValues = [];
 102  
 103      /**
 104       * Mapping between named parameters and position in query.
 105       *
 106       * @var    array
 107       * @since  2.0.0
 108       */
 109      protected $parameterKeyMapping;
 110  
 111      /**
 112       * Mapping array for parameter types.
 113       *
 114       * @var    array
 115       * @since  2.0.0
 116       */
 117      protected $parameterTypeMapping = [
 118          ParameterType::BOOLEAN      => ParameterType::BOOLEAN,
 119          ParameterType::INTEGER      => ParameterType::INTEGER,
 120          ParameterType::LARGE_OBJECT => ParameterType::LARGE_OBJECT,
 121          ParameterType::NULL         => ParameterType::NULL,
 122          ParameterType::STRING       => ParameterType::STRING,
 123      ];
 124  
 125      /**
 126       * Constructor.
 127       *
 128       * @param   resource  $connection  The database connection resource
 129       * @param   string    $query       The query this statement will process
 130       *
 131       * @since   2.0.0
 132       * @throws  PrepareStatementFailureException
 133       */
 134  	public function __construct($connection, string $query)
 135      {
 136          // Initial parameter types for prepared statements
 137          $this->parameterTypeMapping = [
 138              ParameterType::BOOLEAN      => SQLSRV_PHPTYPE_INT,
 139              ParameterType::INTEGER      => SQLSRV_PHPTYPE_INT,
 140              ParameterType::LARGE_OBJECT => SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY),
 141              ParameterType::NULL         => SQLSRV_PHPTYPE_NULL,
 142              ParameterType::STRING       => SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR),
 143          ];
 144  
 145          $this->connection = $connection;
 146          $this->query      = $this->prepareParameterKeyMapping($query);
 147      }
 148  
 149      /**
 150       * Replace named parameters with numbered parameters
 151       *
 152       * @param   string  $sql  The SQL statement to prepare.
 153       *
 154       * @return  string  The processed SQL statement.
 155       *
 156       * @since   2.0.0
 157       */
 158  	public function prepareParameterKeyMapping($sql)
 159      {
 160          $escaped       = false;
 161          $startPos      = 0;
 162          $quoteChar     = '';
 163          $literal    = '';
 164          $mapping    = [];
 165          $replace    = [];
 166          $matches    = [];
 167          $pattern    = '/([:][a-zA-Z0-9_]+)/';
 168  
 169          if (!preg_match($pattern, $sql, $matches))
 170          {
 171              return $sql;
 172          }
 173  
 174          $sql = trim($sql);
 175          $n   = \strlen($sql);
 176  
 177          while ($startPos < $n)
 178          {
 179              if (!preg_match($pattern, $sql, $matches, 0, $startPos))
 180              {
 181                  break;
 182              }
 183  
 184              $j = strpos($sql, "'", $startPos);
 185              $k = strpos($sql, '"', $startPos);
 186  
 187              if (($k !== false) && (($k < $j) || ($j === false)))
 188              {
 189                  $quoteChar = '"';
 190                  $j         = $k;
 191              }
 192              else
 193              {
 194                  $quoteChar = "'";
 195              }
 196  
 197              if ($j === false)
 198              {
 199                  $j = $n;
 200              }
 201  
 202              // Search for named prepared parameters and replace it with ? and save its position
 203              $substring = substr($sql, $startPos, $j - $startPos);
 204  
 205              if (preg_match_all($pattern, $substring, $matches, PREG_PATTERN_ORDER + PREG_OFFSET_CAPTURE))
 206              {
 207                  foreach ($matches[0] as $i => $match)
 208                  {
 209                      if ($i === 0)
 210                      {
 211                          $literal .= substr($substring, 0, $match[1]);
 212                      }
 213  
 214                      $mapping[$match[0]]     = \count($mapping);
 215                      $endOfPlaceholder       = $match[1] + strlen($match[0]);
 216                      $beginOfNextPlaceholder = $matches[0][$i + 1][1] ?? strlen($substring);
 217                      $beginOfNextPlaceholder -= $endOfPlaceholder;
 218                      $literal                .= '?' . substr($substring, $endOfPlaceholder, $beginOfNextPlaceholder);
 219                  }
 220              }
 221              else
 222              {
 223                  $literal .= $substring;
 224              }
 225  
 226              $startPos = $j;
 227              $j++;
 228  
 229              if ($j >= $n)
 230              {
 231                  break;
 232              }
 233  
 234              // Quote comes first, find end of quote
 235              while (true)
 236              {
 237                  $k       = strpos($sql, $quoteChar, $j);
 238                  $escaped = false;
 239  
 240                  if ($k === false)
 241                  {
 242                      break;
 243                  }
 244  
 245                  $l = $k - 1;
 246  
 247                  while ($l >= 0 && $sql[$l] === '\\')
 248                  {
 249                      $l--;
 250                      $escaped = !$escaped;
 251                  }
 252  
 253                  if ($escaped)
 254                  {
 255                      $j = $k + 1;
 256  
 257                      continue;
 258                  }
 259  
 260                  break;
 261              }
 262  
 263              if ($k === false)
 264              {
 265                  // Error in the query - no end quote; ignore it
 266                  break;
 267              }
 268  
 269              $literal .= substr($sql, $startPos, $k - $startPos + 1);
 270              $startPos = $k + 1;
 271          }
 272  
 273          if ($startPos < $n)
 274          {
 275              $literal .= substr($sql, $startPos, $n - $startPos);
 276          }
 277  
 278          $this->parameterKeyMapping = $mapping;
 279  
 280          return $literal;
 281      }
 282  
 283      /**
 284       * Binds a parameter to the specified variable name.
 285       *
 286       * @param   string|integer  $parameter       Parameter identifier. For a prepared statement using named placeholders, this will be a parameter
 287       *                                           name of the form `:name`. For a prepared statement using question mark placeholders, this will be
 288       *                                           the 1-indexed position of the parameter.
 289       * @param   mixed           $variable        Name of the PHP variable to bind to the SQL statement parameter.
 290       * @param   string          $dataType        Constant corresponding to a SQL datatype, this should be the processed type from the QueryInterface.
 291       * @param   integer         $length          The length of the variable. Usually required for OUTPUT parameters.
 292       * @param   array           $driverOptions   Optional driver options to be used.
 293       *
 294       * @return  boolean
 295       *
 296       * @since   2.0.0
 297       */
 298  	public function bindParam($parameter, &$variable, string $dataType = ParameterType::STRING, ?int $length = null, ?array $driverOptions = null)
 299      {
 300          $this->bindedValues[$parameter] =& $variable;
 301  
 302          // Validate parameter type
 303          if (!isset($this->parameterTypeMapping[$dataType]))
 304          {
 305              throw new \InvalidArgumentException(sprintf('Unsupported parameter type `%s`', $dataType));
 306          }
 307  
 308          $this->typesKeyMapping[$parameter] = $this->parameterTypeMapping[$dataType];
 309  
 310          $this->statement = null;
 311  
 312          return true;
 313      }
 314  
 315      /**
 316       * Binds a value to the specified variable.
 317       *
 318       * @param   string|integer  $parameter       Parameter identifier. For a prepared statement using named placeholders, this will be a parameter
 319       *                                           name of the form `:name`. For a prepared statement using question mark placeholders, this will be
 320       *                                           the 1-indexed position of the parameter.
 321       * @param   mixed           $variable        Name of the PHP variable to bind to the SQL statement parameter.
 322       * @param   string          $dataType        Constant corresponding to a SQL datatype, this should be the processed type from the QueryInterface.
 323       *
 324       * @return  void
 325       *
 326       * @since   2.0.0
 327       */
 328  	private function bindValue($parameter, $variable, $dataType = ParameterType::STRING)
 329      {
 330          $this->bindedValues[$parameter]    = $variable;
 331          $this->typesKeyMapping[$parameter] = $dataType;
 332      }
 333  
 334      /**
 335       * Closes the cursor, enabling the statement to be executed again.
 336       *
 337       * @return  void
 338       *
 339       * @since   2.0.0
 340       */
 341  	public function closeCursor(): void
 342      {
 343          if (!$this->result || !\is_resource($this->statement))
 344          {
 345              return;
 346          }
 347  
 348          // Emulate freeing the result fetching and discarding rows, similarly to what PDO does in this case
 349          while (sqlsrv_fetch($this->statement))
 350          {
 351              // Do nothing (see above)
 352          }
 353  
 354          $this->result = false;
 355      }
 356  
 357      /**
 358       * Fetches the SQLSTATE associated with the last operation on the statement handle.
 359       *
 360       * @return  string
 361       *
 362       * @since   2.0.0
 363       */
 364  	public function errorCode()
 365      {
 366          $errors = sqlsrv_errors(SQLSRV_ERR_ERRORS);
 367  
 368          if ($errors)
 369          {
 370              return $errors[0]['code'];
 371          }
 372  
 373          return false;
 374      }
 375  
 376      /**
 377       * Fetches extended error information associated with the last operation on the statement handle.
 378       *
 379       * @return  array
 380       *
 381       * @since   2.0.0
 382       */
 383  	public function errorInfo()
 384      {
 385          return sqlsrv_errors(SQLSRV_ERR_ERRORS);
 386      }
 387  
 388      /**
 389       * Executes a prepared statement
 390       *
 391       * @param   array|null  $parameters  An array of values with as many elements as there are bound parameters in the SQL statement being executed.
 392       *
 393       * @return  boolean
 394       *
 395       * @since   2.0.0
 396       */
 397  	public function execute(?array $parameters = null)
 398      {
 399          if (empty($this->bindedValues) && $parameters !== null)
 400          {
 401              $hasZeroIndex = array_key_exists(0, $parameters);
 402  
 403              foreach ($parameters as $key => $val)
 404              {
 405                  $key = ($hasZeroIndex && is_numeric($key)) ? $key + 1 : $key;
 406                  $this->bindValue($key, $val);
 407              }
 408          }
 409  
 410          if (!$this->statement)
 411          {
 412              $this->statement = $this->prepare();
 413          }
 414  
 415          if (!sqlsrv_execute($this->statement))
 416          {
 417              $errors = $this->errorInfo();
 418  
 419              throw new ExecutionFailureException($this->query, $errors[0]['message'], $errors[0]['code']);
 420          }
 421  
 422          $this->result = true;
 423  
 424          return true;
 425      }
 426  
 427      /**
 428       * Fetches the next row from a result set
 429       *
 430       * @param   integer|null  $fetchStyle         Controls how the next row will be returned to the caller. This value must be one of the
 431       *                                            FetchMode constants, defaulting to value of FetchMode::MIXED.
 432       * @param   integer       $cursorOrientation  For a StatementInterface object representing a scrollable cursor, this value determines which row
 433       *                                            will be returned to the caller. This value must be one of the FetchOrientation constants,
 434       *                                            defaulting to FetchOrientation::NEXT.
 435       * @param   integer       $cursorOffset       For a StatementInterface object representing a scrollable cursor for which the cursorOrientation
 436       *                                            parameter is set to FetchOrientation::ABS, this value specifies the absolute number of the row in
 437       *                                            the result set that shall be fetched. For a StatementInterface object representing a scrollable
 438       *                                            cursor for which the cursorOrientation parameter is set to FetchOrientation::REL, this value
 439       *                                            specifies the row to fetch relative to the cursor position before `fetch()` was called.
 440       *
 441       * @return  mixed  The return value of this function on success depends on the fetch type. In all cases, boolean false is returned on failure.
 442       *
 443       * @since   2.0.0
 444       */
 445  	public function fetch(?int $fetchStyle = null, int $cursorOrientation = FetchOrientation::NEXT, int $cursorOffset = 0)
 446      {
 447          if (!$this->result)
 448          {
 449              return false;
 450          }
 451  
 452          $fetchStyle = $fetchStyle ?: $this->defaultFetchStyle;
 453  
 454          if ($fetchStyle === FetchMode::COLUMN)
 455          {
 456              return $this->fetchColumn();
 457          }
 458  
 459          if (isset($this->fetchMap[$fetchStyle]))
 460          {
 461              return sqlsrv_fetch_array($this->statement, $this->fetchMap[$fetchStyle]) ?: false;
 462          }
 463  
 464          if (\in_array($fetchStyle, [FetchMode::STANDARD_OBJECT, FetchMode::CUSTOM_OBJECT], true))
 465          {
 466              return sqlsrv_fetch_object($this->statement, $this->defaultObjectClass) ?: false;
 467          }
 468  
 469          throw new \InvalidArgumentException("Unknown fetch type '{$fetchStyle}'");
 470      }
 471  
 472      /**
 473       * Returns a single column from the next row of a result set
 474       *
 475       * @param   integer  $columnIndex  0-indexed number of the column you wish to retrieve from the row.
 476       *                                 If no value is supplied, the first column is retrieved.
 477       *
 478       * @return  mixed  Returns a single column from the next row of a result set or boolean false if there are no more rows.
 479       *
 480       * @since   2.0.0
 481       */
 482  	public function fetchColumn($columnIndex = 0)
 483      {
 484          $row = $this->fetch(FetchMode::NUMERIC);
 485  
 486          if ($row === false)
 487          {
 488              return false;
 489          }
 490  
 491          return $row[$columnIndex] ?? null;
 492      }
 493  
 494      /**
 495       * Prepares the SQL Server statement resource for execution
 496       *
 497       * @return  resource
 498       *
 499       * @since   2.0.0
 500       */
 501  	private function prepare()
 502      {
 503          $params = [];
 504          $options = [];
 505  
 506          foreach ($this->bindedValues as $key => &$value)
 507          {
 508              $variable = [
 509                  &$value,
 510                  SQLSRV_PARAM_IN
 511              ];
 512  
 513              if ($this->typesKeyMapping[$key] === $this->parameterTypeMapping[ParameterType::LARGE_OBJECT])
 514              {
 515                  $variable[] = $this->typesKeyMapping[$key];
 516                  $variable[] = SQLSRV_SQLTYPE_VARBINARY('max');
 517              }
 518  
 519              if (isset($this->parameterKeyMapping[$key]))
 520              {
 521                  $params[$this->parameterKeyMapping[$key]] = $variable;
 522              }
 523              else
 524              {
 525                  $params[] = $variable;
 526              }
 527          }
 528  
 529          // Cleanup referenced variable
 530          unset($value);
 531  
 532          // SQLSRV Function sqlsrv_num_rows requires a static or keyset cursor.
 533          if (strncmp(strtoupper(ltrim($this->query)), 'SELECT', \strlen('SELECT')) === 0)
 534          {
 535              $options = ['Scrollable' => SQLSRV_CURSOR_KEYSET];
 536          }
 537  
 538          $statement = sqlsrv_prepare($this->connection, $this->query, $params, $options);
 539  
 540          if (!$statement)
 541          {
 542              $errors = $this->errorInfo();
 543  
 544              throw new PrepareStatementFailureException($errors[0]['message'], $errors[0]['code']);
 545          }
 546  
 547          return $statement;
 548      }
 549  
 550      /**
 551       * Returns the number of rows affected by the last SQL statement.
 552       *
 553       * @return  integer
 554       *
 555       * @since   2.0.0
 556       */
 557  	public function rowCount(): int
 558      {
 559          if (strncmp(strtoupper(ltrim($this->query)), 'SELECT', \strlen('SELECT')) === 0)
 560          {
 561              return sqlsrv_num_rows($this->statement);
 562          }
 563  
 564          return sqlsrv_rows_affected($this->statement);
 565      }
 566  
 567      /**
 568       * Sets the fetch mode to use while iterating this statement.
 569       *
 570       * @param   integer  $fetchMode  The fetch mode, must be one of the FetchMode constants.
 571       * @param   mixed    ...$args    Optional mode-specific arguments.
 572       *
 573       * @return  void
 574       *
 575       * @since   2.0.0
 576       */
 577  	public function setFetchMode(int $fetchMode, ...$args): void
 578      {
 579          $this->defaultFetchStyle = $fetchMode;
 580  
 581          if (isset($args[0]))
 582          {
 583              $this->defaultObjectClass = $args[0];
 584          }
 585      }
 586  }