cc76c0e023e838d47d88118212301a359edfcdee
[Packages/TYPO3.CMS.git] / typo3 / sysext / core / Classes / Database / PreparedStatement.php
1 <?php
2 namespace TYPO3\CMS\Core\Database;
3
4 /**
5 * This file is part of the TYPO3 CMS project.
6 *
7 * It is free software; you can redistribute it and/or modify it under
8 * the terms of the GNU General Public License, either version 2
9 * of the License, or any later version.
10 *
11 * For the full copyright and license information, please read the
12 * LICENSE.txt file that was distributed with this source code.
13 *
14 * The TYPO3 project - inspiring people to share!
15 */
16
17 /**
18 * TYPO3 prepared statement for DatabaseConnection
19 *
20 * USE:
21 * In all TYPO3 scripts when you need to create a prepared query:
22 * <code>
23 * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'pages', 'uid = :uid');
24 * $statement->execute(array(':uid' => 2));
25 * while (($row = $statement->fetch()) !== FALSE) {
26 * ...
27 * }
28 * $statement->free();
29 * </code>
30 *
31 * @author Xavier Perseguers <typo3@perseguers.ch>
32 */
33 class PreparedStatement {
34
35 /**
36 * Represents the SQL NULL data type.
37 *
38 * @var integer
39 */
40 const PARAM_NULL = 0;
41 /**
42 * Represents the SQL INTEGER data type.
43 *
44 * @var integer
45 */
46 const PARAM_INT = 1;
47 /**
48 * Represents the SQL CHAR, VARCHAR, or other string data type.
49 *
50 * @var integer
51 */
52 const PARAM_STR = 2;
53 /**
54 * Represents a boolean data type.
55 *
56 * @var integer
57 */
58 const PARAM_BOOL = 3;
59 /**
60 * Automatically detects underlying type
61 *
62 * @var integer
63 */
64 const PARAM_AUTOTYPE = 4;
65 /**
66 * Specifies that the fetch method shall return each row as an array indexed by
67 * column name as returned in the corresponding result set. If the result set
68 * contains multiple columns with the same name, \TYPO3\CMS\Core\Database\PreparedStatement::FETCH_ASSOC
69 * returns only a single value per column name.
70 *
71 * @var integer
72 */
73 const FETCH_ASSOC = 2;
74 /**
75 * Specifies that the fetch method shall return each row as an array indexed by
76 * column number as returned in the corresponding result set, starting at column 0.
77 *
78 * @var integer
79 */
80 const FETCH_NUM = 3;
81 /**
82 * Query to be executed.
83 *
84 * @var string
85 */
86 protected $query;
87
88 /**
89 * Components of the query to be executed.
90 *
91 * @var array
92 */
93 protected $precompiledQueryParts;
94
95 /**
96 * Table (used to call $GLOBALS['TYPO3_DB']->fullQuoteStr().
97 *
98 * @var string
99 */
100 protected $table;
101
102 /**
103 * Binding parameters.
104 *
105 * @var array
106 */
107 protected $parameters;
108
109 /**
110 * Default fetch mode.
111 *
112 * @var integer
113 */
114 protected $defaultFetchMode = self::FETCH_ASSOC;
115
116 /**
117 * MySQLi statement object / DBAL object
118 *
119 * @var \mysqli_stmt|object
120 */
121 protected $statement;
122
123 /**
124 * @var array
125 */
126 protected $fields;
127
128 /**
129 * @var array
130 */
131 protected $buffer;
132
133 /**
134 * Random token which is wrapped around the markers
135 * that will be replaced by user input.
136 *
137 * @var string
138 */
139 protected $parameterWrapToken;
140
141 /**
142 * Creates a new PreparedStatement. Either $query or $queryComponents
143 * should be used. Typically $query will be used by native MySQL TYPO3_DB
144 * on a ready-to-be-executed query. On the other hand, DBAL will have
145 * parse the query and will be able to safely know where parameters are used
146 * and will use $queryComponents instead.
147 *
148 * This constructor may only be used by \TYPO3\CMS\Core\Database\DatabaseConnection
149 *
150 * @param string $query SQL query to be executed
151 * @param string $table FROM table, used to call $GLOBALS['TYPO3_DB']->fullQuoteStr().
152 * @param array $precompiledQueryParts Components of the query to be executed
153 * @access private
154 */
155 public function __construct($query, $table, array $precompiledQueryParts = array()) {
156 $this->query = $query;
157 $this->precompiledQueryParts = $precompiledQueryParts;
158 $this->table = $table;
159 $this->parameters = array();
160
161 // Test if named placeholders are used
162 if ($this->hasNamedPlaceholders($query) || count($precompiledQueryParts) > 0) {
163 $this->statement = NULL;
164 } else {
165 // Only question mark placeholders are used
166 $this->statement = $GLOBALS['TYPO3_DB']->prepare_PREPAREDquery($this->query, $this->precompiledQueryParts);
167 }
168
169 $this->parameterWrapToken = $this->generateParameterWrapToken();
170 }
171
172 /**
173 * Binds an array of values to corresponding named or question mark placeholders in the SQL
174 * statement that was use to prepare the statement.
175 *
176 * Example 1:
177 * <code>
178 * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = ? AND bug_status = ?');
179 * $statement->bindValues(array('goofy', 'FIXED'));
180 * </code>
181 *
182 * Example 2:
183 * <code>
184 * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = :nickname AND bug_status = :status');
185 * $statement->bindValues(array(':nickname' => 'goofy', ':status' => 'FIXED'));
186 * </code>
187 *
188 * @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.
189 * @return \TYPO3\CMS\Core\Database\PreparedStatement The current prepared statement to allow method chaining
190 * @api
191 */
192 public function bindValues(array $values) {
193 foreach ($values as $parameter => $value) {
194 $key = is_int($parameter) ? $parameter + 1 : $parameter;
195 $this->bindValue($key, $value, self::PARAM_AUTOTYPE);
196 }
197 return $this;
198 }
199
200 /**
201 * Binds a value to a corresponding named or question mark placeholder in the SQL
202 * statement that was use to prepare the statement.
203 *
204 * Example 1:
205 * <code>
206 * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = ? AND bug_status = ?');
207 * $statement->bindValue(1, 'goofy');
208 * $statement->bindValue(2, 'FIXED');
209 * </code>
210 *
211 * Example 2:
212 * <code>
213 * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = :nickname AND bug_status = :status');
214 * $statement->bindValue(':nickname', 'goofy');
215 * $statement->bindValue(':status', 'FIXED');
216 * </code>
217 *
218 * @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.
219 * @param mixed $value The value to bind to the parameter.
220 * @param integer $data_type Explicit data type for the parameter using the \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_* constants. If not given, the PHP type of the value will be used instead (int, string, boolean).
221 * @return \TYPO3\CMS\Core\Database\PreparedStatement The current prepared statement to allow method chaining
222 * @api
223 */
224 public function bindValue($parameter, $value, $data_type = self::PARAM_AUTOTYPE) {
225 switch ($data_type) {
226 case self::PARAM_INT:
227 if (!is_int($value)) {
228 throw new \InvalidArgumentException('$value is not an integer as expected: ' . $value, 1281868686);
229 }
230 break;
231 case self::PARAM_BOOL:
232 if (!is_bool($value)) {
233 throw new \InvalidArgumentException('$value is not a boolean as expected: ' . $value, 1281868687);
234 }
235 break;
236 case self::PARAM_NULL:
237 if (!is_null($value)) {
238 throw new \InvalidArgumentException('$value is not NULL as expected: ' . $value, 1282489834);
239 }
240 break;
241 }
242 if (!is_int($parameter) && !preg_match('/^:[\\w]+$/', $parameter)) {
243 throw new \InvalidArgumentException('Parameter names must start with ":" followed by an arbitrary number of alphanumerical characters.', 1395055513);
244 }
245 $key = is_int($parameter) ? $parameter - 1 : $parameter;
246 $this->parameters[$key] = array(
247 'value' => $value,
248 'type' => $data_type == self::PARAM_AUTOTYPE ? $this->guessValueType($value) : $data_type
249 );
250 return $this;
251 }
252
253 /**
254 * Executes the prepared statement. If the prepared statement included parameter
255 * markers, you must either:
256 * <ul>
257 * <li>call {@link \TYPO3\CMS\Core\Database\PreparedStatement::bindParam()} to bind PHP variables
258 * to the parameter markers: bound variables pass their value as input</li>
259 * <li>or pass an array of input-only parameter values</li>
260 * </ul>
261 *
262 * $input_parameters behave as in {@link \TYPO3\CMS\Core\Database\PreparedStatement::bindParams()}
263 * and work for both named parameters and question mark parameters.
264 *
265 * Example 1:
266 * <code>
267 * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = ? AND bug_status = ?');
268 * $statement->execute(array('goofy', 'FIXED'));
269 * </code>
270 *
271 * Example 2:
272 * <code>
273 * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = :nickname AND bug_status = :status');
274 * $statement->execute(array(':nickname' => 'goofy', ':status' => 'FIXED'));
275 * </code>
276 *
277 * @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.
278 * @return boolean Returns TRUE on success or FALSE on failure.
279 * @throws \InvalidArgumentException
280 * @api
281 */
282 public function execute(array $input_parameters = array()) {
283 $parameterValues = $this->parameters;
284 if (!empty($input_parameters)) {
285 $parameterValues = array();
286 foreach ($input_parameters as $key => $value) {
287 $parameterValues[$key] = array(
288 'value' => $value,
289 'type' => $this->guessValueType($value)
290 );
291 }
292 }
293
294 if ($this->statement !== NULL) {
295 // The statement has already been executed, we try to reset it
296 // for current run but will set it to NULL if it fails for some
297 // reason, just as if it were the first run
298 if (!$this->statement->reset()) {
299 $this->statement = NULL;
300 }
301 }
302 if ($this->statement === NULL) {
303 // The statement has never been executed so we prepare it and
304 // store it for further reuse
305 $query = $this->query;
306 $precompiledQueryParts = $this->precompiledQueryParts;
307
308 $this->convertNamedPlaceholdersToQuestionMarks($query, $parameterValues, $precompiledQueryParts);
309 if (count($precompiledQueryParts) > 0) {
310 $query = implode('', $precompiledQueryParts['queryParts']);
311 }
312 $this->statement = $GLOBALS['TYPO3_DB']->prepare_PREPAREDquery($query, $precompiledQueryParts);
313 if ($this->statement === NULL) {
314 return FALSE;
315 }
316 }
317
318 $combinedTypes = '';
319 $values = array();
320 foreach ($parameterValues as $parameterValue) {
321 switch ($parameterValue['type']) {
322 case self::PARAM_NULL:
323 $type = 's';
324 $value = NULL;
325 break;
326 case self::PARAM_INT:
327 $type = 'i';
328 $value = (int)$parameterValue['value'];
329 break;
330 case self::PARAM_STR:
331 $type = 's';
332 $value = $parameterValue['value'];
333 break;
334 case self::PARAM_BOOL:
335 $type = 'i';
336 $value = $parameterValue['value'] ? 1 : 0;
337 break;
338 default:
339 throw new \InvalidArgumentException(sprintf('Unknown type %s used for parameter %s.', $parameterValue['type'], $key), 1281859196);
340 }
341
342 $combinedTypes .= $type;
343 $values[] = $value;
344 }
345
346 // ->bind_param requires second up to last arguments as references
347 $bindParamArguments = array();
348 $bindParamArguments[] = $combinedTypes;
349 $numberOfExtraParamArguments = count($values);
350 for ($i = 0; $i < $numberOfExtraParamArguments; $i++) {
351 $bindParamArguments[] = &$values[$i];
352 }
353 call_user_func_array(array($this->statement, 'bind_param'), $bindParamArguments);
354
355 $success = $this->statement->execute();
356
357 // Store result
358 if (!$success || $this->statement->store_result() === FALSE) {
359 return FALSE;
360 }
361
362 if (count($this->fields) === 0) {
363 // Store the list of fields
364 if ($this->statement instanceof \mysqli_stmt) {
365 $result = $this->statement->result_metadata();
366 $fields = $result->fetch_fields();
367 $result->close();
368 } else {
369 $fields = $this->statement->fetch_fields();
370 }
371 if (is_array($fields)) {
372 foreach ($fields as $field) {
373 $this->fields[] = $field->name;
374 }
375 }
376
377 }
378
379 // New result set available
380 $this->buffer = NULL;
381
382 // Empty binding parameters
383 $this->parameters = array();
384
385 // Return the success flag
386 return $success;
387 }
388
389 /**
390 * Fetches a row from a result set associated with a \TYPO3\CMS\Core\Database\PreparedStatement object.
391 *
392 * @param integer $fetch_style Controls how the next row will be returned to the caller. This value must be one of the \TYPO3\CMS\Core\Database\PreparedStatement::FETCH_* constants. If omitted, default fetch mode for this prepared query will be used.
393 * @return array Array of rows or FALSE if there are no more rows.
394 * @api
395 */
396 public function fetch($fetch_style = 0) {
397 if ($fetch_style == 0) {
398 $fetch_style = $this->defaultFetchMode;
399 }
400
401 if ($this->statement instanceof \mysqli_stmt) {
402 if ($this->buffer === NULL) {
403 $variables = array();
404 $this->buffer = array();
405 foreach ($this->fields as $field) {
406 $this->buffer[$field] = NULL;
407 $variables[] = &$this->buffer[$field];
408 }
409
410 call_user_func_array(array($this->statement, 'bind_result'), $variables);
411 }
412 $success = $this->statement->fetch();
413 $columns = $this->buffer;
414 } else {
415 $columns = $this->statement->fetch();
416 $success = is_array($columns);
417 }
418
419 if ($success) {
420 $row = array();
421 foreach ($columns as $key => $value) {
422 switch ($fetch_style) {
423 case self::FETCH_ASSOC:
424 $row[$key] = $value;
425 break;
426 case self::FETCH_NUM:
427 $row[] = $value;
428 break;
429 default:
430 throw new \InvalidArgumentException('$fetch_style must be either TYPO3\\CMS\\Core\\Database\\PreparedStatement::FETCH_ASSOC or TYPO3\\CMS\\Core\\Database\\PreparedStatement::FETCH_NUM', 1281646455);
431 }
432 }
433 } else {
434 $row = FALSE;
435 }
436
437 return $row;
438 }
439
440 /**
441 * Moves internal result pointer.
442 *
443 * @param integer $rowNumber Where to place the result pointer (0 = start)
444 * @return boolean Returns TRUE on success or FALSE on failure.
445 * @api
446 */
447 public function seek($rowNumber) {
448 $success = $this->statement->data_seek((int)$rowNumber);
449 if ($this->statement instanceof \mysqli_stmt) {
450 // data_seek() does not return anything
451 $success = TRUE;
452 }
453 return $success;
454 }
455
456 /**
457 * Returns an array containing all of the result set rows.
458 *
459 * @param integer $fetch_style Controls the contents of the returned array as documented in {@link \TYPO3\CMS\Core\Database\PreparedStatement::fetch()}.
460 * @return array Array of rows.
461 * @api
462 */
463 public function fetchAll($fetch_style = 0) {
464 $rows = array();
465 while (($row = $this->fetch($fetch_style)) !== FALSE) {
466 $rows[] = $row;
467 }
468 return $rows;
469 }
470
471 /**
472 * Releases the cursor. Should always be call after having fetched rows from
473 * a query execution.
474 *
475 * @return void
476 * @api
477 */
478 public function free() {
479 $this->statement->close();
480 }
481
482 /**
483 * Returns the number of rows affected by the last SQL statement.
484 *
485 * @return integer The number of rows.
486 * @api
487 */
488 public function rowCount() {
489 return $this->statement->num_rows;
490 }
491
492 /**
493 * Returns the error number on the last execute() call.
494 *
495 * @return integer Driver specific error code.
496 * @api
497 */
498 public function errorCode() {
499 return $this->statement->errno;
500 }
501
502 /**
503 * Returns an array of error information about the last operation performed by this statement handle.
504 * The array consists of the following fields:
505 * <ol start="0">
506 * <li>Driver specific error code.</li>
507 * <li>Driver specific error message</li>
508 * </ol>
509 *
510 * @return array Array of error information.
511 */
512 public function errorInfo() {
513 return array(
514 $this->statement->errno,
515 $this->statement->error
516 );
517 }
518
519 /**
520 * Sets the default fetch mode for this prepared query.
521 *
522 * @param integer $mode One of the \TYPO3\CMS\Core\Database\PreparedStatement::FETCH_* constants
523 * @return void
524 * @api
525 */
526 public function setFetchMode($mode) {
527 switch ($mode) {
528 case self::FETCH_ASSOC:
529
530 case self::FETCH_NUM:
531 $this->defaultFetchMode = $mode;
532 break;
533 default:
534 throw new \InvalidArgumentException('$mode must be either TYPO3\\CMS\\Core\\Database\\PreparedStatement::FETCH_ASSOC or TYPO3\\CMS\\Core\\Database\\PreparedStatement::FETCH_NUM', 1281875340);
535 }
536 }
537
538 /**
539 * Guesses the type of a given value.
540 *
541 * @param mixed $value
542 * @return integer One of the \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_* constants
543 */
544 protected function guessValueType($value) {
545 if (is_bool($value)) {
546 $type = self::PARAM_BOOL;
547 } elseif (is_int($value)) {
548 $type = self::PARAM_INT;
549 } elseif (is_null($value)) {
550 $type = self::PARAM_NULL;
551 } else {
552 $type = self::PARAM_STR;
553 }
554 return $type;
555 }
556
557 /**
558 * Returns TRUE if named placeholers are used in a query.
559 *
560 * @param string $query
561 * @return boolean
562 */
563 protected function hasNamedPlaceholders($query) {
564 $matches = preg_match('/(?<![\\w:]):[\\w]+\\b/', $query);
565 return $matches > 0;
566 }
567
568 /**
569 * Converts named placeholders into question mark placeholders in a query.
570 *
571 * @param string $query
572 * @param array $parameterValues
573 * @param array $precompiledQueryParts
574 * @return void
575 */
576 protected function convertNamedPlaceholdersToQuestionMarks(&$query, array &$parameterValues, array &$precompiledQueryParts) {
577 $queryPartsCount = count($precompiledQueryParts['queryParts']);
578 $newParameterValues = array();
579 $hasNamedPlaceholders = FALSE;
580
581 if ($queryPartsCount === 0) {
582 $hasNamedPlaceholders = $this->hasNamedPlaceholders($query);
583 if ($hasNamedPlaceholders) {
584 $query = $this->tokenizeQueryParameterMarkers($query, $parameterValues);
585 }
586 } elseif (count($parameterValues) > 0) {
587 $hasNamedPlaceholders = !is_int(key($parameterValues));
588 if ($hasNamedPlaceholders) {
589 for ($i = 1; $i < $queryPartsCount; $i += 2) {
590 $key = $precompiledQueryParts['queryParts'][$i];
591 $precompiledQueryParts['queryParts'][$i] = '?';
592 $newParameterValues[] = $parameterValues[$key];
593 }
594 }
595 }
596
597 if ($hasNamedPlaceholders) {
598 if ($queryPartsCount === 0) {
599 // Convert named placeholders to standard question mark placeholders
600 $quotedParamWrapToken = preg_quote($this->parameterWrapToken, '/');
601 while (preg_match(
602 '/' . $quotedParamWrapToken . '(.*?)' . $quotedParamWrapToken . '/',
603 $query,
604 $matches
605 )) {
606 $key = $matches[1];
607
608 $newParameterValues[] = $parameterValues[$key];
609 $query = preg_replace(
610 '/' . $quotedParamWrapToken . $key . $quotedParamWrapToken . '/',
611 '?',
612 $query,
613 1
614 );
615 }
616 }
617
618 $parameterValues = $newParameterValues;
619 }
620 }
621
622 /**
623 * Replace the markers with unpredictable token markers.
624 *
625 * @param string $query
626 * @param array $parameterValues
627 * @return string
628 * @throws \InvalidArgumentException
629 */
630 protected function tokenizeQueryParameterMarkers($query, array $parameterValues) {
631 $unnamedParameterCount = 0;
632 foreach ($parameterValues as $key => $typeValue) {
633 if (!is_int($key)) {
634 if (!preg_match('/^:[\\w]+$/', $key)) {
635 throw new \InvalidArgumentException('Parameter names must start with ":" followed by an arbitrary number of alphanumerical characters.', 1282348825);
636 }
637 // Replace the marker (not preceeded by a word character or a ':' but
638 // followed by a word boundary)
639 $query = preg_replace('/(?<![\\w:])' . preg_quote($key, '/') . '\\b/', $this->parameterWrapToken . $key . $this->parameterWrapToken, $query);
640 } else {
641 $unnamedParameterCount++;
642 }
643 }
644 $parts = explode('?', $query, $unnamedParameterCount + 1);
645 $query = implode($this->parameterWrapToken . '?' . $this->parameterWrapToken, $parts);
646 return $query;
647 }
648
649 /**
650 * Generate a random token that is used to wrap the query markers
651 *
652 * @return string
653 */
654 protected function generateParameterWrapToken() {
655 return '__' . \TYPO3\CMS\Core\Utility\GeneralUtility::getRandomHexString(16) . '__';
656 }
657
658 }