[TASK] Use real boolean value for sL second param
[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-2013 Xavier Perseguers <typo3@perseguers.ch>
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 textfile 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 result object / DBAL object
131 *
132 * @var boolean|\mysqli_result|object
133 */
134 protected $resource;
135
136 /**
137 * Random token which is wrapped around the markers
138 * that will be replaced by user input.
139 *
140 * @var string
141 */
142 protected $parameterWrapToken;
143
144 /**
145 * Creates a new PreparedStatement. Either $query or $queryComponents
146 * should be used. Typically $query will be used by native MySQL TYPO3_DB
147 * on a ready-to-be-executed query. On the other hand, DBAL will have
148 * parse the query and will be able to safely know where parameters are used
149 * and will use $queryComponents instead.
150 *
151 * This constructor may only be used by \TYPO3\CMS\Core\Database\DatabaseConnection
152 *
153 * @param string $query SQL query to be executed
154 * @param string $table FROM table, used to call $GLOBALS['TYPO3_DB']->fullQuoteStr().
155 * @param array $precompiledQueryParts Components of the query to be executed
156 * @access private
157 */
158 public function __construct($query, $table, array $precompiledQueryParts = array()) {
159 $this->query = $query;
160 $this->precompiledQueryParts = $precompiledQueryParts;
161 $this->table = $table;
162 $this->parameters = array();
163 $this->resource = NULL;
164 $this->parameterWrapToken = $this->generateParameterWrapToken();
165 }
166
167 /**
168 * Binds an array of values to corresponding named or question mark placeholders in the SQL
169 * statement that was use to prepare the statement.
170 *
171 * Example 1:
172 * <code>
173 * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = ? AND bug_status = ?');
174 * $statement->bindValues(array('goofy', 'FIXED'));
175 * </code>
176 *
177 * Example 2:
178 * <code>
179 * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = :nickname AND bug_status = :status');
180 * $statement->bindValues(array(':nickname' => 'goofy', ':status' => 'FIXED'));
181 * </code>
182 *
183 * @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.
184 * @return \TYPO3\CMS\Core\Database\PreparedStatement The current prepared statement to allow method chaining
185 * @api
186 */
187 public function bindValues(array $values) {
188 foreach ($values as $parameter => $value) {
189 $key = is_int($parameter) ? $parameter + 1 : $parameter;
190 $this->bindValue($key, $value, self::PARAM_AUTOTYPE);
191 }
192 return $this;
193 }
194
195 /**
196 * Binds a value to a corresponding named or question mark placeholder in the SQL
197 * statement that was use to prepare the statement.
198 *
199 * Example 1:
200 * <code>
201 * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = ? AND bug_status = ?');
202 * $statement->bindValue(1, 'goofy');
203 * $statement->bindValue(2, 'FIXED');
204 * </code>
205 *
206 * Example 2:
207 * <code>
208 * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = :nickname AND bug_status = :status');
209 * $statement->bindValue(':nickname', 'goofy');
210 * $statement->bindValue(':status', 'FIXED');
211 * </code>
212 *
213 * @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.
214 * @param mixed $value The value to bind to the parameter.
215 * @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).
216 * @return \TYPO3\CMS\Core\Database\PreparedStatement The current prepared statement to allow method chaining
217 * @api
218 */
219 public function bindValue($parameter, $value, $data_type = self::PARAM_AUTOTYPE) {
220 switch ($data_type) {
221 case self::PARAM_INT:
222 if (!is_int($value)) {
223 throw new \InvalidArgumentException('$value is not an integer as expected: ' . $value, 1281868686);
224 }
225 break;
226 case self::PARAM_BOOL:
227 if (!is_bool($value)) {
228 throw new \InvalidArgumentException('$value is not a boolean as expected: ' . $value, 1281868687);
229 }
230 break;
231 case self::PARAM_NULL:
232 if (!is_null($value)) {
233 throw new \InvalidArgumentException('$value is not NULL as expected: ' . $value, 1282489834);
234 }
235 break;
236 }
237 $key = is_int($parameter) ? $parameter - 1 : $parameter;
238 $this->parameters[$key] = array(
239 'value' => $value,
240 'type' => $data_type == self::PARAM_AUTOTYPE ? $this->guessValueType($value) : $data_type
241 );
242 return $this;
243 }
244
245 /**
246 * Executes the prepared statement. If the prepared statement included parameter
247 * markers, you must either:
248 * <ul>
249 * <li>call {@link \TYPO3\CMS\Core\Database\PreparedStatement::bindParam()} to bind PHP variables
250 * to the parameter markers: bound variables pass their value as input</li>
251 * <li>or pass an array of input-only parameter values</li>
252 * </ul>
253 *
254 * $input_parameters behave as in {@link \TYPO3\CMS\Core\Database\PreparedStatement::bindParams()}
255 * and work for both named parameters and question mark parameters.
256 *
257 * Example 1:
258 * <code>
259 * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = ? AND bug_status = ?');
260 * $statement->execute(array('goofy', 'FIXED'));
261 * </code>
262 *
263 * Example 2:
264 * <code>
265 * $statement = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'bugs', 'reported_by = :nickname AND bug_status = :status');
266 * $statement->execute(array(':nickname' => 'goofy', ':status' => 'FIXED'));
267 * </code>
268 *
269 * @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.
270 * @return boolean Returns TRUE on success or FALSE on failure.
271 * @api
272 */
273 public function execute(array $input_parameters = array()) {
274 $query = $this->query;
275 $precompiledQueryParts = $this->precompiledQueryParts;
276 $parameterValues = $this->parameters;
277 if (count($input_parameters) > 0) {
278 $parameterValues = array();
279 foreach ($input_parameters as $key => $value) {
280 $parameterValues[$key] = array(
281 'value' => $value,
282 'type' => $this->guessValueType($value)
283 );
284 }
285 }
286 $this->replaceValuesInQuery($query, $precompiledQueryParts, $parameterValues);
287 if (count($precompiledQueryParts) > 0) {
288 $query = implode('', $precompiledQueryParts['queryParts']);
289 }
290 $this->resource = $GLOBALS['TYPO3_DB']->exec_PREPAREDquery($query, $precompiledQueryParts);
291 // Empty binding parameters
292 $this->parameters = array();
293 // Return the success flag
294 return $this->resource ? TRUE : FALSE;
295 }
296
297 /**
298 * Fetches a row from a result set associated with a \TYPO3\CMS\Core\Database\PreparedStatement object.
299 *
300 * @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.
301 * @return array Array of rows or FALSE if there are no more rows.
302 * @api
303 */
304 public function fetch($fetch_style = 0) {
305 if ($fetch_style == 0) {
306 $fetch_style = $this->defaultFetchMode;
307 }
308 switch ($fetch_style) {
309 case self::FETCH_ASSOC:
310 $row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($this->resource);
311 break;
312 case self::FETCH_NUM:
313 $row = $GLOBALS['TYPO3_DB']->sql_fetch_row($this->resource);
314 break;
315 default:
316 throw new \InvalidArgumentException('$fetch_style must be either TYPO3\\CMS\\Core\\Database\\PreparedStatement::FETCH_ASSOC or TYPO3\\CMS\\Core\\Database\\PreparedStatement::FETCH_NUM', 1281646455);
317 }
318 return $row;
319 }
320
321 /**
322 * Moves internal result pointer.
323 *
324 * @param integer $rowNumber Where to place the result pointer (0 = start)
325 * @return boolean Returns TRUE on success or FALSE on failure.
326 * @api
327 */
328 public function seek($rowNumber) {
329 return $GLOBALS['TYPO3_DB']->sql_data_seek($this->resource, intval($rowNumber));
330 }
331
332 /**
333 * Returns an array containing all of the result set rows.
334 *
335 * @param integer $fetch_style Controls the contents of the returned array as documented in {@link \TYPO3\CMS\Core\Database\PreparedStatement::fetch()}.
336 * @return array Array of rows.
337 * @api
338 */
339 public function fetchAll($fetch_style = 0) {
340 $rows = array();
341 while (($row = $this->fetch($fetch_style)) !== FALSE) {
342 $rows[] = $row;
343 }
344 return $rows;
345 }
346
347 /**
348 * Releases the cursor. Should always be call after having fetched rows from
349 * a query execution.
350 *
351 * @return void
352 * @api
353 */
354 public function free() {
355 $GLOBALS['TYPO3_DB']->sql_free_result($this->resource);
356 }
357
358 /**
359 * Returns the number of rows affected by the last SQL statement.
360 *
361 * @return integer The number of rows.
362 * @api
363 */
364 public function rowCount() {
365 return $GLOBALS['TYPO3_DB']->sql_num_rows($this->resource);
366 }
367
368 /**
369 * Returns the error number on the last execute() call.
370 *
371 * @return integer Driver specific error code.
372 * @api
373 */
374 public function errorCode() {
375 return $GLOBALS['TYPO3_DB']->sql_errno();
376 }
377
378 /**
379 * Returns an array of error information about the last operation performed by this statement handle.
380 * The array consists of the following fields:
381 * <ol start="0">
382 * <li>Driver specific error code.</li>
383 * <li>Driver specific error message</li>
384 * </ol>
385 *
386 * @return array Array of error information.
387 */
388 public function errorInfo() {
389 return array(
390 $GLOBALS['TYPO3_DB']->sql_errno(),
391 $GLOBALS['TYPO3_DB']->sql_error()
392 );
393 }
394
395 /**
396 * Sets the default fetch mode for this prepared query.
397 *
398 * @param integer $mode One of the \TYPO3\CMS\Core\Database\PreparedStatement::FETCH_* constants
399 * @return void
400 * @api
401 */
402 public function setFetchMode($mode) {
403 switch ($mode) {
404 case self::FETCH_ASSOC:
405
406 case self::FETCH_NUM:
407 $this->defaultFetchMode = $mode;
408 break;
409 default:
410 throw new \InvalidArgumentException('$mode must be either TYPO3\\CMS\\Core\\Database\\PreparedStatement::FETCH_ASSOC or TYPO3\\CMS\\Core\\Database\\PreparedStatement::FETCH_NUM', 1281875340);
411 }
412 }
413
414 /**
415 * Guesses the type of a given value.
416 *
417 * @param mixed $value
418 * @return integer One of the \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_* constants
419 */
420 protected function guessValueType($value) {
421 if (is_bool($value)) {
422 $type = self::PARAM_BOOL;
423 } elseif (is_int($value)) {
424 $type = self::PARAM_INT;
425 } elseif (is_null($value)) {
426 $type = self::PARAM_NULL;
427 } else {
428 $type = self::PARAM_STR;
429 }
430 return $type;
431 }
432
433 /**
434 * Replaces values for each parameter in a query.
435 *
436 * @param string $query
437 * @param array $precompiledQueryParts
438 * @param array $parameterValues
439 * @return void
440 */
441 protected function replaceValuesInQuery(&$query, array &$precompiledQueryParts, array $parameterValues) {
442 if (count($precompiledQueryParts['queryParts']) === 0) {
443 $query = $this->tokenizeQueryParameterMarkers($query, $parameterValues);
444 }
445 foreach ($parameterValues as $key => $typeValue) {
446 switch ($typeValue['type']) {
447 case self::PARAM_NULL:
448 $value = 'NULL';
449 break;
450 case self::PARAM_INT:
451 $value = intval($typeValue['value']);
452 break;
453 case self::PARAM_STR:
454 $value = $GLOBALS['TYPO3_DB']->fullQuoteStr($typeValue['value'], $this->table);
455 break;
456 case self::PARAM_BOOL:
457 $value = $typeValue['value'] ? 1 : 0;
458 break;
459 default:
460 throw new \InvalidArgumentException(sprintf('Unknown type %s used for parameter %s.', $typeValue['type'], $key), 1281859196);
461 }
462 if (is_int($key)) {
463 if (count($precompiledQueryParts['queryParts']) > 0) {
464 $precompiledQueryParts['queryParts'][2 * $key + 1] = $value;
465 } else {
466 $parts = explode($this->parameterWrapToken . '?' . $this->parameterWrapToken, $query, 2);
467 $parts[0] .= $value;
468 $query = implode('', $parts);
469 }
470 } else {
471 $queryPartsCount = count($precompiledQueryParts['queryParts']);
472 for ($i = 1; $i < $queryPartsCount; $i++) {
473 if ($precompiledQueryParts['queryParts'][$i] === $key) {
474 $precompiledQueryParts['queryParts'][$i] = $value;
475 }
476 }
477 $query = str_replace($this->parameterWrapToken . $key . $this->parameterWrapToken, $value, $query);
478 }
479 }
480 }
481
482 /**
483 * Replace the markers with unpredictable token markers.
484 *
485 * @param string $query
486 * @param array $parameterValues
487 * @return string
488 * @throws \InvalidArgumentException
489 */
490 protected function tokenizeQueryParameterMarkers($query, array $parameterValues) {
491 $unnamedParameterCount = 0;
492 foreach ($parameterValues as $key => $typeValue) {
493 if (!is_int($key)) {
494 if (!preg_match('/^:[\\w]+$/', $key)) {
495 throw new \InvalidArgumentException('Parameter names must start with ":" followed by an arbitrary number of alphanumerical characters.', 1282348825);
496 }
497 // Replace the marker (not preceeded by a word character or a ':' but
498 // followed by a word boundary)
499 $query = preg_replace('/(?<![\\w:])' . preg_quote($key, '/') . '\\b/', $this->parameterWrapToken . $key . $this->parameterWrapToken, $query);
500 } else {
501 $unnamedParameterCount++;
502 }
503 }
504 $parts = explode('?', $query, $unnamedParameterCount + 1);
505 $query = implode($this->parameterWrapToken . '?' . $this->parameterWrapToken, $parts);
506 return $query;
507 }
508
509 /**
510 * Generate a random token that is used to wrap the query markers
511 *
512 * @return string
513 */
514 protected function generateParameterWrapToken() {
515 return '__' . \TYPO3\CMS\Core\Utility\GeneralUtility::getRandomHexString(16) . '__';
516 }
517
518 }
519
520
521 ?>