t3lib/db/class.t3lib_db_preparedstatement.php

   1 <?php
   2 /***************************************************************
   3  *  Copyright notice
   4  *
   5  *  (c) 2010 Xavier Perseguers <typo3@perseguers.ch>
   6  *  All rights reserved
   7  *
   8  *  This script is part of the TYPO3 project. The TYPO3 project is
   9  *  free software; you can redistribute it and/or modify
  10  *  it under the terms of the GNU General Public License as published by
  11  *  the Free Software Foundation; either version 2 of the License, or
  12  *  (at your option) any later version.
  13  *
  14  *  The GNU General Public License can be found at
  15  *  http://www.gnu.org/copyleft/gpl.html.
  16  *  A copy is found in the textfile GPL.txt and important notices to the license
  17  *  from the author is found in LICENSE.txt distributed with these scripts.
  18  *
  19  *
  20  *  This script is distributed in the hope that it will be useful,
  21  *  but WITHOUT ANY WARRANTY; without even the implied warranty of
  22  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  23  *  GNU General Public License for more details.
  24  *
  25  *  This copyright notice MUST APPEAR in all copies of the script!
  26  ***************************************************************/
  27
  28 /**
  29  * TYPO3 prepared statement for t3lib_db class.
  30  *
  31  * USE:
  32  * In all TYPO3 scripts when you need to create a prepared query:
  33  * <code>
  34  * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'pages', 'uid = :uid');
  35  * $statement->execute(array(':uid' => 2));
  36  * while (($row = $statement->fetch()) !== FALSE) {
  37  *      // ...
  38  * }
  39  * $statement->free();
  40  * </code>
  41  *
  42  * @author      Xavier Perseguers <typo3@perseguers.ch>
  43  * @package TYPO3
  44  * @subpackage t3lib
  45  */
  46 class t3lib_db_PreparedStatement {
  47
  48         /**
  49          * Represents the SQL NULL data type.
  50          * @var integer
  51          */
  52         const PARAM_NULL = 0;
  53
  54         /**
  55          * Represents the SQL INTEGER data type.
  56          * @var integer
  57          */
  58         const PARAM_INT = 1;
  59
  60         /**
  61          * Represents the SQL CHAR, VARCHAR, or other string data type.
  62          * @var integer
  63          */
  64         const PARAM_STR = 2;
  65
  66         /**
  67          * Represents a boolean data type.
  68          * @var integer
  69          */
  70         const PARAM_BOOL = 3;
  71
  72         /**
  73          * Automatically detects underlying type
  74          * @var integer
  75          */
  76         const PARAM_AUTOTYPE = 4;
  77
  78         /**
  79          * Specifies that the fetch method shall return each row as an array indexed by
  80          * column name as returned in the corresponding result set. If the result set
  81          * contains multiple columns with the same name, t3lib_db_PreparedStatement::FETCH_ASSOC
  82          * returns only a single value per column name.
  83          * @var integer
  84          */
  85         const FETCH_ASSOC = 2;
  86
  87         /**
  88          * Specifies that the fetch method shall return each row as an array indexed by
  89          * column number as returned in the corresponding result set, starting at column 0.
  90          * @var integer
  91          */
  92         const FETCH_NUM = 3;
  93
  94         /**
  95          * Query to be executed.
  96          * @var string
  97          */
  98         protected $query;
  99
 100         /**
 101          * Components of the query to be executed.
 102          * @var array
 103          */
 104         protected $precompiledQueryParts;
 105
 106         /**
 107          * Table (used to call $GLOBALS['TYPO3_DB']->fullQuoteStr().
 108          * @var string
 109          */
 110         protected $table;
 111
 112         /**
 113          * Binding parameters.
 114          * @var array
 115          */
 116         protected $parameters;
 117
 118         /**
 119          * Default fetch mode.
 120          * @var integer
 121          */
 122         protected $defaultFetchMode = self::FETCH_ASSOC;
 123
 124         /**
 125          * MySQL result pointer (of SELECT query) / DBAL object.
 126          * @var pointer
 127          */
 128         protected $resource;
 129
 130         /**
 131          * Creates a new PreparedStatement. Either $query or $queryComponents
 132          * should be used. Typically $query will be used by native MySQL TYPO3_DB
 133          * on a ready-to-be-executed query. On the other hand, DBAL will have
 134          * parse the query and will be able to safely know where parameters are used
 135          * and will use $queryComponents instead.
 136          * This constructor may only be used by t3lib_DB.
 137          *
 138          * @param string $query SQL query to be executed
 139          * @param string FROM table, used to call $GLOBALS['TYPO3_DB']->fullQuoteStr().
 140          * @param array $precompiledQueryParts Components of the query to be executed
 141          * @access private
 142          */
 143         public function __construct($query, $table, array $precompiledQueryParts = array()) {
 144                 $this->query = $query;
 145                 $this->precompiledQueryParts = $precompiledQueryParts;
 146                 $this->table = $table;
 147                 $this->parameters = array();
 148                 $this->resource = NULL;
 149         }
 150
 151         /**
 152          * Binds an array of values to corresponding named or question mark placeholders in the SQL
 153          * statement that was use to prepare the statement.
 154          *
 155          * Example 1:
 156          * <code>
 157          * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = ? AND bug_status = ?');
 158          * $statement->bindValues(array('goofy', 'FIXED'));
 159          * </code>
 160          *
 161          * Example 2:
 162          * <code>
 163          * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = :nickname AND bug_status = :status');
 164          * $statement->bindValues(array(':nickname' => 'goofy', ':status' => 'FIXED'));
 165          * </code>
 166          *
 167          * @param array $values The values to bind to the parameter. The PHP type of each array value will be used to decide which PARAM_* type to use (int, string, boolean, null), so make sure your variables are properly casted, if needed.
 168          * @return t3lib_db_PreparedStatement The current prepared statement to allow method chaining
 169          * @api
 170          */
 171         public function bindValues(array $values) {
 172                 foreach ($values as $parameter => $value) {
 173                         $key = is_int($parameter) ? $parameter + 1 : $parameter;
 174                         $this->bindValue($key, $value, self::PARAM_AUTOTYPE);
 175                 }
 176
 177                 return $this;
 178         }
 179
 180         /**
 181          * Binds a value to a corresponding named or question mark placeholder in the SQL
 182          * statement that was use to prepare the statement.
 183          *
 184          * Example 1:
 185          * <code>
 186          * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = ? AND bug_status = ?');
 187          * $statement->bindValue(1, 'goofy');
 188          * $statement->bindValue(2, 'FIXED');
 189          * </code>
 190          *
 191          * Example 2:
 192          * <code>
 193          * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = :nickname AND bug_status = :status');
 194          * $statement->bindValue(':nickname', 'goofy');
 195          * $statement->bindValue(':status', 'FIXED');
 196          * </code>
 197          *
 198          * @param mixed $parameter Parameter identifier. For a prepared statement using named placeholders, this will be a parameter name of the form :name. For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter.
 199          * @param mixed $value The value to bind to the parameter.
 200          * @param integer $data_type Explicit data type for the parameter using the t3lib_db_PreparedStatement::PARAM_* constants. If not given, the PHP type of the value will be used instead (int, string, boolean).
 201          * @return t3lib_db_PreparedStatement The current prepared statement to allow method chaining
 202          * @api
 203          */
 204         public function bindValue($parameter, $value, $data_type = self::PARAM_AUTOTYPE) {
 205                 switch ($data_type) {
 206                         case self::PARAM_INT:
 207                                 if (!is_int($value)) {
 208                                         throw new InvalidArgumentException('$value is not an integer as expected: ' . $value, 1281868686);
 209                                 }
 210                                 break;
 211                         case self::PARAM_BOOL:
 212                                 if (!is_bool($value)) {
 213                                         throw new InvalidArgumentException('$value is not a boolean as expected: ' . $value, 1281868687);
 214                                 }
 215                                 break;
 216                         case self::PARAM_NULL:
 217                                 if (!is_null($value)) {
 218                                         throw new InvalidArgumentException('$value is not NULL as expected: ' . $value, 1282489834);
 219                                 }
 220                                 break;
 221                 }
 222
 223                 $key = is_int($parameter) ? $parameter - 1 : $parameter;
 224                 $this->parameters[$key] = array(
 225                         'value' => $value,
 226                         'type' => ($data_type == self::PARAM_AUTOTYPE ? $this->guessValueType($value) : $data_type),
 227                 );
 228
 229                 return $this;
 230         }
 231
 232         /**
 233          * Executes the prepared statement. If the prepared statement included parameter
 234          * markers, you must either:
 235          * <ul>
 236          *       <li>call {@link t3lib_db_PreparedStatement::bindParam()} to bind PHP variables
 237          *   to the parameter markers: bound variables pass their value as input</li>
 238          *       <li>or pass an array of input-only parameter values</li>
 239          * </ul>
 240          *
 241          * $input_parameters behave as in {@link t3lib_db_PreparedStatement::bindParams()}
 242          * and work for both named parameters and question mark parameters.
 243          *
 244          * Example 1:
 245          * <code>
 246          * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = ? AND bug_status = ?');
 247          * $statement->execute(array('goofy', 'FIXED'));
 248          * </code>
 249          *
 250          * Example 2:
 251          * <code>
 252          * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = :nickname AND bug_status = :status');
 253          * $statement->execute(array(':nickname' => 'goofy', ':status' => 'FIXED'));
 254          * </code>
 255          *
 256          * @param array $input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed. The PHP type of each array value will be used to decide which PARAM_* type to use (int, string, boolean, null), so make sure your variables are properly casted, if needed.
 257          * @return boolean Returns TRUE on success or FALSE on failure.
 258          * @api
 259          */
 260         public function execute(array $input_parameters = array()) {
 261                 $query = $this->query;
 262                 $precompiledQueryParts = $this->precompiledQueryParts;
 263                 $parameterValues = $this->parameters;
 264
 265                 if (count($input_parameters) > 0) {
 266                         $parameterValues = array();
 267                         foreach ($input_parameters as $key => $value) {
 268                                 $parameterValues[$key] = array(
 269                                         'value' => $value,
 270                                         'type' => $this->guessValueType($value),
 271                                 );
 272                         }
 273                 }
 274
 275                 $this->replaceValuesInQuery($query, $precompiledQueryParts, $parameterValues);
 276                 if (count($precompiledQueryParts) > 0) {
 277                         $query = implode('', $precompiledQueryParts['queryParts']);
 278                 }
 279                 $this->resource = $GLOBALS['TYPO3_DB']->exec_PREPAREDquery($query, $precompiledQueryParts);
 280
 281                         // Empty binding parameters
 282                 $this->parameters = array();
 283
 284                         // Return the success flag
 285                 return ($this->resource ? TRUE : FALSE);
 286         }
 287
 288         /**
 289          * Fetches a row from a result set associated with a t3lib_db_PreparedStatement object.
 290          *
 291          * @param integer $fetch_style Controls how the next row will be returned to the caller. This value must be one of the t3lib_db_PreparedStatement::FETCH_* constants. If omitted, default fetch mode for this prepared query will be used.
 292          * @return array Array of rows or FALSE if there are no more rows.
 293          * @api
 294          */
 295         public function fetch($fetch_style = 0) {
 296                 if ($fetch_style == 0) {
 297                         $fetch_style = $this->defaultFetchMode;
 298                 }
 299                 switch ($fetch_style) {
 300                         case self::FETCH_ASSOC:
 301                                 $row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($this->resource);
 302                                 break;
 303                         case self::FETCH_NUM:
 304                                 $row = $GLOBALS['TYPO3_DB']->sql_fetch_row($this->resource);
 305                                 break;
 306                         default:
 307                                 throw new InvalidArgumentException('$fetch_style must be either t3lib_db_PreparedStatement::FETCH_ASSOC or t3lib_db_PreparedStatement::FETCH_NUM', 1281646455);
 308                 }
 309                 return $row;
 310         }
 311
 312         /**
 313          * Moves internal result pointer.
 314          *
 315          * @param integer $rowNumber Where to place the result pointer (0 = start)
 316          * @return boolean Returns TRUE on success or FALSE on failure.
 317          * @api
 318          */
 319         public function seek($rowNumber) {
 320                 return $GLOBALS['TYPO3_DB']->sql_data_seek($this->resource, intval($rowNumber));
 321         }
 322
 323         /**
 324          * Returns an array containing all of the result set rows.
 325          *
 326          * @param integer $fetch_style Controls the contents of the returned array as documented in {@link t3lib_db_PreparedStatement::fetch()}.
 327          * @return array Array of rows.
 328          * @api
 329          */
 330         public function fetchAll($fetch_style = 0) {
 331                 $rows = array();
 332                 while (($row = $this->fetch($fetch_style)) !== FALSE) {
 333                         $rows[] = $row;
 334                 }
 335                 return $rows;
 336         }
 337
 338         /**
 339          * Releases the cursor. Should always be call after having fetched rows from
 340          * a query execution.
 341          *
 342          * @return void
 343          * @api
 344          */
 345         public function free() {
 346                 $GLOBALS['TYPO3_DB']->sql_free_result($this->resource);
 347         }
 348
 349         /**
 350          * Returns the number of rows affected by the last SQL statement.
 351          *
 352          * @return integer The number of rows.
 353          * @api
 354          */
 355         public function rowCount() {
 356                 return $GLOBALS['TYPO3_DB']->sql_num_rows($this->resource);
 357         }
 358
 359         /**
 360          * Returns the error number on the last execute() call.
 361          *
 362          * @return integer Driver specific error code.
 363          * @api
 364          */
 365         public function errorCode() {
 366                 return $GLOBALS['TYPO3_DB']->sql_errno();
 367         }
 368
 369         /**
 370          * Returns an array of error information about the last operation performed by this statement handle.
 371          * The array consists of the following fields:
 372          * <ol start="0">
 373          *  <li>Driver specific error code.</li>
 374          *  <li>Driver specific error message</li>
 375          * </ol>
 376          *
 377          * @return array Array of error information.
 378          */
 379         public function errorInfo() {
 380                 return array(
 381                         $GLOBALS['TYPO3_DB']->sql_errno(),
 382                         $GLOBALS['TYPO3_DB']->sql_error(),
 383                 );
 384         }
 385
 386         /**
 387          * Sets the default fetch mode for this prepared query.
 388          *
 389          * @param integer $mode One of the t3lib_db_PreparedStatement::FETCH_* constants
 390          * @return void
 391          * @api
 392          */
 393         public function setFetchMode($mode) {
 394                 switch ($mode) {
 395                         case self::FETCH_ASSOC:
 396                         case self::FETCH_NUM:
 397                                 $this->defaultFetchMode = $mode;
 398                                 break;
 399                         default:
 400                                 throw new InvalidArgumentException('$mode must be either t3lib_db_PreparedStatement::FETCH_ASSOC or t3lib_db_PreparedStatement::FETCH_NUM', 1281875340);
 401                 }
 402         }
 403
 404         /**
 405          * Guesses the type of a given value.
 406          *
 407          * @param mixed $value
 408          * @return integer One of the t3lib_db_PreparedStatement::PARAM_* constants
 409          */
 410         protected function guessValueType($value) {
 411                 if (is_bool($value)) {
 412                         $type = self::PARAM_BOOL;
 413                 } elseif (is_int($value)) {
 414                         $type = self::PARAM_INT;
 415                 } elseif (is_null($value)) {
 416                         $type = self::PARAM_NULL;
 417                 } else {
 418                         $type = self::PARAM_STR;
 419                 }
 420
 421                 return $type;
 422         }
 423
 424         /**
 425          * Replaces values for each parameter in a query.
 426          *
 427          * @param string $query
 428          * @param array $precompiledQueryParts
 429          * @param array $parameterValues
 430          * @return void
 431          */
 432         protected function replaceValuesInQuery(&$query, array &$precompiledQueryParts, array $parameterValues) {
 433                 foreach ($parameterValues as $key => $typeValue) {
 434                         switch ($typeValue['type']) {
 435                                 case self::PARAM_NULL:
 436                                         $value = 'NULL';
 437                                         break;
 438                                 case self::PARAM_INT:
 439                                         $value = intval($typeValue['value']);
 440                                         break;
 441                                 case self::PARAM_STR:
 442                                         $value = $GLOBALS['TYPO3_DB']->fullQuoteStr($typeValue['value'], $this->table);
 443                                         break;
 444                                 case self::PARAM_BOOL:
 445                                         $value = $typeValue['value'] ? 1 : 0;
 446                                         break;
 447                                 default:
 448                                         throw new InvalidArgumentException(
 449                                                 sprintf('Unknown type %s used for parameter %s.', $typeValue['type'], $key),
 450                                                 1281859196
 451                                         );
 452                         }
 453
 454                         if (is_int($key)) {
 455                                 if (count($precompiledQueryParts['queryParts']) > 0) {
 456                                         $precompiledQueryParts['queryParts'][2 * $key + 1] = $value;
 457                                 } else {
 458                                         $parts = explode('?', $query, 2);
 459                                         $parts[0] .= $value;
 460                                         $query = implode('', $parts);
 461                                 }
 462                         } else {
 463                                 if (!preg_match('/^:[\w]+$/', $key)) {
 464                                         throw new InvalidArgumentException('Parameter names must start with ":" followed by an arbitrary number of alphanumerical characters.', 1282348825);
 465                                 }
 466
 467                                 for ($i = 1; $i < count($precompiledQueryParts['queryParts']); $i++) {
 468                                         if ($precompiledQueryParts['queryParts'][$i] === $key) {
 469                                                 $precompiledQueryParts['queryParts'][$i] = $value;
 470                                         }
 471                                 }
 472                                         // Replace the marker (not preceeded by a word character or a ':' but
 473                                         // followed by a word boundary)
 474                                 $query = preg_replace('/(?<![\w:])' . $key . '\b/', $value, $query);
 475                         }
 476                 }
 477         }
 478 }
 479
 480
 481 if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/db/class.t3lib_db_PreparedStatement.php']) {
 482         include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/db/class.t3lib_db_PreparedStatement.php']);
 483 }
 484
 485 ?>