[BUGFIX] Add missing check in PreparedStatement->execute()
[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
354 call_user_func_array(array($this->statement, 'bind_param'), $bindParamArguments);
355
356 $success = $this->statement->execute();
357
358 // Store result
359 if (!$success || $this->statement->store_result() === FALSE) {
360 return FALSE;
361 }
362
363 if (count($this->fields) === 0) {
364 // Store the list of fields
365 if ($this->statement instanceof \mysqli_stmt) {
366 $result = $this->statement->result_metadata();
367 if ($result instanceof \mysqli_result) {
368 $fields = $result->fetch_fields();
369 $result->close();
370 }
371 } else {
372 $fields = $this->statement->fetch_fields();
373 }
374 if (is_array($fields)) {
375 foreach ($fields as $field) {
376 $this->fields[] = $field->name;
377 }
378 }
379
380 }
381
382 // New result set available
383 $this->buffer = NULL;
384
385 // Empty binding parameters
386 $this->parameters = array();
387
388 // Return the success flag
389 return $success;
390 }
391
392 /**
393 * Fetches a row from a result set associated with a \TYPO3\CMS\Core\Database\PreparedStatement object.
394 *
395 * @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.
396 * @return array Array of rows or FALSE if there are no more rows.
397 * @api
398 */
399 public function fetch($fetch_style = 0) {
400 if ($fetch_style == 0) {
401 $fetch_style = $this->defaultFetchMode;
402 }
403
404 if ($this->statement instanceof \mysqli_stmt) {
405 if ($this->buffer === NULL) {
406 $variables = array();
407 $this->buffer = array();
408 foreach ($this->fields as $field) {
409 $this->buffer[$field] = NULL;
410 $variables[] = &$this->buffer[$field];
411 }
412
413 call_user_func_array(array($this->statement, 'bind_result'), $variables);
414 }
415 $success = $this->statement->fetch();
416 $columns = $this->buffer;
417 } else {
418 $columns = $this->statement->fetch();
419 $success = is_array($columns);
420 }
421
422 if ($success) {
423 $row = array();
424 foreach ($columns as $key => $value) {
425 switch ($fetch_style) {
426 case self::FETCH_ASSOC:
427 $row[$key] = $value;
428 break;
429 case self::FETCH_NUM:
430 $row[] = $value;
431 break;
432 default:
433 throw new \InvalidArgumentException('$fetch_style must be either TYPO3\\CMS\\Core\\Database\\PreparedStatement::FETCH_ASSOC or TYPO3\\CMS\\Core\\Database\\PreparedStatement::FETCH_NUM', 1281646455);
434 }
435 }
436 } else {
437 $row = FALSE;
438 }
439
440 return $row;
441 }
442
443 /**
444 * Moves internal result pointer.
445 *
446 * @param integer $rowNumber Where to place the result pointer (0 = start)
447 * @return boolean Returns TRUE on success or FALSE on failure.
448 * @api
449 */
450 public function seek($rowNumber) {
451 $success = $this->statement->data_seek((int)$rowNumber);
452 if ($this->statement instanceof \mysqli_stmt) {
453 // data_seek() does not return anything
454 $success = TRUE;
455 }
456 return $success;
457 }
458
459 /**
460 * Returns an array containing all of the result set rows.
461 *
462 * @param integer $fetch_style Controls the contents of the returned array as documented in {@link \TYPO3\CMS\Core\Database\PreparedStatement::fetch()}.
463 * @return array Array of rows.
464 * @api
465 */
466 public function fetchAll($fetch_style = 0) {
467 $rows = array();
468 while (($row = $this->fetch($fetch_style)) !== FALSE) {
469 $rows[] = $row;
470 }
471 return $rows;
472 }
473
474 /**
475 * Releases the cursor. Should always be call after having fetched rows from
476 * a query execution.
477 *
478 * @return void
479 * @api
480 */
481 public function free() {
482 $this->statement->close();
483 }
484
485 /**
486 * Returns the number of rows affected by the last SQL statement.
487 *
488 * @return integer The number of rows.
489 * @api
490 */
491 public function rowCount() {
492 return $this->statement->num_rows;
493 }
494
495 /**
496 * Returns the error number on the last execute() call.
497 *
498 * @return integer Driver specific error code.
499 * @api
500 */
501 public function errorCode() {
502 return $this->statement->errno;
503 }
504
505 /**
506 * Returns an array of error information about the last operation performed by this statement handle.
507 * The array consists of the following fields:
508 * <ol start="0">
509 * <li>Driver specific error code.</li>
510 * <li>Driver specific error message</li>
511 * </ol>
512 *
513 * @return array Array of error information.
514 */
515 public function errorInfo() {
516 return array(
517 $this->statement->errno,
518 $this->statement->error
519 );
520 }
521
522 /**
523 * Sets the default fetch mode for this prepared query.
524 *
525 * @param integer $mode One of the \TYPO3\CMS\Core\Database\PreparedStatement::FETCH_* constants
526 * @return void
527 * @api
528 */
529 public function setFetchMode($mode) {
530 switch ($mode) {
531 case self::FETCH_ASSOC:
532
533 case self::FETCH_NUM:
534 $this->defaultFetchMode = $mode;
535 break;
536 default:
537 throw new \InvalidArgumentException('$mode must be either TYPO3\\CMS\\Core\\Database\\PreparedStatement::FETCH_ASSOC or TYPO3\\CMS\\Core\\Database\\PreparedStatement::FETCH_NUM', 1281875340);
538 }
539 }
540
541 /**
542 * Guesses the type of a given value.
543 *
544 * @param mixed $value
545 * @return integer One of the \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_* constants
546 */
547 protected function guessValueType($value) {
548 if (is_bool($value)) {
549 $type = self::PARAM_BOOL;
550 } elseif (is_int($value)) {
551 $type = self::PARAM_INT;
552 } elseif (is_null($value)) {
553 $type = self::PARAM_NULL;
554 } else {
555 $type = self::PARAM_STR;
556 }
557 return $type;
558 }
559
560 /**
561 * Returns TRUE if named placeholers are used in a query.
562 *
563 * @param string $query
564 * @return boolean
565 */
566 protected function hasNamedPlaceholders($query) {
567 $matches = preg_match('/(?<![\\w:]):[\\w]+\\b/', $query);
568 return $matches > 0;
569 }
570
571 /**
572 * Converts named placeholders into question mark placeholders in a query.
573 *
574 * @param string $query
575 * @param array $parameterValues
576 * @param array $precompiledQueryParts
577 * @return void
578 */
579 protected function convertNamedPlaceholdersToQuestionMarks(&$query, array &$parameterValues, array &$precompiledQueryParts) {
580 $queryPartsCount = count($precompiledQueryParts['queryParts']);
581 $newParameterValues = array();
582 $hasNamedPlaceholders = FALSE;
583
584 if ($queryPartsCount === 0) {
585 $hasNamedPlaceholders = $this->hasNamedPlaceholders($query);
586 if ($hasNamedPlaceholders) {
587 $query = $this->tokenizeQueryParameterMarkers($query, $parameterValues);
588 }
589 } elseif (count($parameterValues) > 0) {
590 $hasNamedPlaceholders = !is_int(key($parameterValues));
591 if ($hasNamedPlaceholders) {
592 for ($i = 1; $i < $queryPartsCount; $i += 2) {
593 $key = $precompiledQueryParts['queryParts'][$i];
594 $precompiledQueryParts['queryParts'][$i] = '?';
595 $newParameterValues[] = $parameterValues[$key];
596 }
597 }
598 }
599
600 if ($hasNamedPlaceholders) {
601 if ($queryPartsCount === 0) {
602 // Convert named placeholders to standard question mark placeholders
603 $quotedParamWrapToken = preg_quote($this->parameterWrapToken, '/');
604 while (preg_match(
605 '/' . $quotedParamWrapToken . '(.*?)' . $quotedParamWrapToken . '/',
606 $query,
607 $matches
608 )) {
609 $key = $matches[1];
610
611 $newParameterValues[] = $parameterValues[$key];
612 $query = preg_replace(
613 '/' . $quotedParamWrapToken . $key . $quotedParamWrapToken . '/',
614 '?',
615 $query,
616 1
617 );
618 }
619 }
620
621 $parameterValues = $newParameterValues;
622 }
623 }
624
625 /**
626 * Replace the markers with unpredictable token markers.
627 *
628 * @param string $query
629 * @param array $parameterValues
630 * @return string
631 * @throws \InvalidArgumentException
632 */
633 protected function tokenizeQueryParameterMarkers($query, array $parameterValues) {
634 $unnamedParameterCount = 0;
635 foreach ($parameterValues as $key => $typeValue) {
636 if (!is_int($key)) {
637 if (!preg_match('/^:[\\w]+$/', $key)) {
638 throw new \InvalidArgumentException('Parameter names must start with ":" followed by an arbitrary number of alphanumerical characters.', 1282348825);
639 }
640 // Replace the marker (not preceeded by a word character or a ':' but
641 // followed by a word boundary)
642 $query = preg_replace('/(?<![\\w:])' . preg_quote($key, '/') . '\\b/', $this->parameterWrapToken . $key . $this->parameterWrapToken, $query);
643 } else {
644 $unnamedParameterCount++;
645 }
646 }
647 $parts = explode('?', $query, $unnamedParameterCount + 1);
648 $query = implode($this->parameterWrapToken . '?' . $this->parameterWrapToken, $parts);
649 return $query;
650 }
651
652 /**
653 * Generate a random token that is used to wrap the query markers
654 *
655 * @return string
656 */
657 protected function generateParameterWrapToken() {
658 return '__' . \TYPO3\CMS\Core\Utility\GeneralUtility::getRandomHexString(16) . '__';
659 }
660
661 }