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