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