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