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