[FEATURE] Add SELECT_mm_query to DatabaseConnection
[Packages/TYPO3.CMS.git] / typo3 / sysext / core / Classes / Database / DatabaseConnection.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\Utility\GeneralUtility;
18 use TYPO3\CMS\Core\Utility\StringUtility;
19
20 /**
21 * Contains the class "DatabaseConnection" containing functions for building SQL queries
22 * and mysqli wrappers, thus providing a foundational API to all database
23 * interaction.
24 * This class is instantiated globally as $TYPO3_DB in TYPO3 scripts.
25 *
26 * TYPO3 "database wrapper" class (new in 3.6.0)
27 * This class contains
28 * - abstraction functions for executing INSERT/UPDATE/DELETE/SELECT queries ("Query execution"; These are REQUIRED for all future connectivity to the database, thus ensuring DBAL compliance!)
29 * - functions for building SQL queries (INSERT/UPDATE/DELETE/SELECT) ("Query building"); These are transitional functions for building SQL queries in a more automated way. Use these to build queries instead of doing it manually in your code!
30 * - mysqli wrapper functions; These are transitional functions. By a simple search/replace you should be able to substitute all mysql*() calls with $GLOBALS['TYPO3_DB']->sql*() and your application will work out of the box. YOU CANNOT (legally) use any mysqli functions not found as wrapper functions in this class!
31 * See the Project Coding Guidelines (doc_core_cgl) for more instructions on best-practise
32 *
33 * This class is not in itself a complete database abstraction layer but can be extended to be a DBAL (by extensions, see "dbal" for example)
34 * ALL connectivity to the database in TYPO3 must be done through this class!
35 * The points of this class are:
36 * - To direct all database calls through this class so it becomes possible to implement DBAL with extensions.
37 * - To keep it very easy to use for developers used to MySQL in PHP - and preserve as much performance as possible when TYPO3 is used with MySQL directly...
38 * - To create an interface for DBAL implemented by extensions; (Eg. making possible escaping characters, clob/blob handling, reserved words handling)
39 * - Benchmarking the DB bottleneck queries will become much easier; Will make it easier to find optimization possibilities.
40 *
41 * USE:
42 * In all TYPO3 scripts the global variable $TYPO3_DB is an instance of this class. Use that.
43 * Eg. $GLOBALS['TYPO3_DB']->sql_fetch_assoc()
44 */
45 class DatabaseConnection {
46
47 /**
48 * The AND constraint in where clause
49 *
50 * @var string
51 */
52 const AND_Constraint = 'AND';
53
54 /**
55 * The OR constraint in where clause
56 *
57 * @var string
58 */
59 const OR_Constraint = 'OR';
60
61 /**
62 * Set "TRUE" or "1" if you want database errors outputted. Set to "2" if you also want successful database actions outputted.
63 *
64 * @var bool|int
65 */
66 public $debugOutput = FALSE;
67
68 /**
69 * Internally: Set to last built query (not necessarily executed...)
70 *
71 * @var string
72 */
73 public $debug_lastBuiltQuery = '';
74
75 /**
76 * Set "TRUE" if you want the last built query to be stored in $debug_lastBuiltQuery independent of $this->debugOutput
77 *
78 * @var bool
79 */
80 public $store_lastBuiltQuery = FALSE;
81
82 /**
83 * Set this to 1 to get queries explained (devIPmask must match). Set the value to 2 to the same but disregarding the devIPmask.
84 * There is an alternative option to enable explain output in the admin panel under "TypoScript", which will produce much nicer output, but only works in FE.
85 *
86 * @var bool
87 */
88 public $explainOutput = 0;
89
90 /**
91 * @var string Database host to connect to
92 */
93 protected $databaseHost = '';
94
95 /**
96 * @var int Database port to connect to
97 */
98 protected $databasePort = 3306;
99
100 /**
101 * @var string|NULL Database socket to connect to
102 */
103 protected $databaseSocket = NULL;
104
105 /**
106 * @var string Database name to connect to
107 */
108 protected $databaseName = '';
109
110 /**
111 * @var string Database user to connect with
112 */
113 protected $databaseUsername = '';
114
115 /**
116 * @var string Database password to connect with
117 */
118 protected $databaseUserPassword = '';
119
120 /**
121 * @var bool TRUE if database connection should be persistent
122 * @see http://php.net/manual/de/mysqli.persistconns.php
123 */
124 protected $persistentDatabaseConnection = FALSE;
125
126 /**
127 * @var bool TRUE if connection between client and sql server is compressed
128 */
129 protected $connectionCompression = FALSE;
130
131 /**
132 * The charset for the connection; will be passed on to
133 * mysqli_set_charset during connection initialization.
134 *
135 * @var string
136 */
137 protected $connectionCharset = 'utf8';
138
139 /**
140 * @var array List of commands executed after connection was established
141 */
142 protected $initializeCommandsAfterConnect = array();
143
144 /**
145 * @var bool TRUE if database connection is established
146 */
147 protected $isConnected = FALSE;
148
149 /**
150 * @var \mysqli $link Default database link object
151 */
152 protected $link = NULL;
153
154 /**
155 * Default character set, applies unless character set or collation are explicitly set
156 *
157 * @var string
158 */
159 public $default_charset = 'utf8';
160
161 /**
162 * @var array<PostProcessQueryHookInterface>
163 */
164 protected $preProcessHookObjects = array();
165
166 /**
167 * @var array<PreProcessQueryHookInterface>
168 */
169 protected $postProcessHookObjects = array();
170
171 /**
172 * the date and time formats compatible with the database in general
173 *
174 * @var array
175 */
176 static protected $dateTimeFormats = array(
177 'date' => array(
178 'empty' => '0000-00-00',
179 'format' => 'Y-m-d'
180 ),
181 'datetime' => array(
182 'empty' => '0000-00-00 00:00:00',
183 'format' => 'Y-m-d H:i:s'
184 )
185 );
186
187 /**
188 * Initialize the database connection
189 *
190 * @return void
191 */
192 public function initialize() {
193 // Intentionally blank as this will be overloaded by DBAL
194 }
195
196 /************************************
197 *
198 * Query execution
199 *
200 * These functions are the RECOMMENDED DBAL functions for use in your applications
201 * Using these functions will allow the DBAL to use alternative ways of accessing data (contrary to if a query is returned!)
202 * They compile a query AND execute it immediately and then return the result
203 * This principle heightens our ability to create various forms of DBAL of the functions.
204 * Generally: We want to return a result pointer/object, never queries.
205 * Also, having the table name together with the actual query execution allows us to direct the request to other databases.
206 *
207 **************************************/
208
209 /**
210 * Creates and executes an INSERT SQL-statement for $table from the array with field/value pairs $fields_values.
211 * Using this function specifically allows us to handle BLOB and CLOB fields depending on DB
212 *
213 * @param string $table Table name
214 * @param array $fields_values Field values as key=>value pairs. Values will be escaped internally. Typically you would fill an array like "$insertFields" with 'fieldname'=>'value' and pass it to this function as argument.
215 * @param bool|array|string $no_quote_fields See fullQuoteArray()
216 * @return bool|\mysqli_result|object MySQLi result object / DBAL object
217 */
218 public function exec_INSERTquery($table, $fields_values, $no_quote_fields = FALSE) {
219 $res = $this->query($this->INSERTquery($table, $fields_values, $no_quote_fields));
220 if ($this->debugOutput) {
221 $this->debug('exec_INSERTquery');
222 }
223 foreach ($this->postProcessHookObjects as $hookObject) {
224 /** @var $hookObject PostProcessQueryHookInterface */
225 $hookObject->exec_INSERTquery_postProcessAction($table, $fields_values, $no_quote_fields, $this);
226 }
227 return $res;
228 }
229
230 /**
231 * Creates and executes an INSERT SQL-statement for $table with multiple rows.
232 *
233 * @param string $table Table name
234 * @param array $fields Field names
235 * @param array $rows Table rows. Each row should be an array with field values mapping to $fields
236 * @param bool|array|string $no_quote_fields See fullQuoteArray()
237 * @return bool|\mysqli_result|object MySQLi result object / DBAL object
238 */
239 public function exec_INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = FALSE) {
240 $res = $this->query($this->INSERTmultipleRows($table, $fields, $rows, $no_quote_fields));
241 if ($this->debugOutput) {
242 $this->debug('exec_INSERTmultipleRows');
243 }
244 foreach ($this->postProcessHookObjects as $hookObject) {
245 /** @var $hookObject PostProcessQueryHookInterface */
246 $hookObject->exec_INSERTmultipleRows_postProcessAction($table, $fields, $rows, $no_quote_fields, $this);
247 }
248 return $res;
249 }
250
251 /**
252 * Creates and executes an UPDATE SQL-statement for $table where $where-clause (typ. 'uid=...') from the array with field/value pairs $fields_values.
253 * Using this function specifically allow us to handle BLOB and CLOB fields depending on DB
254 *
255 * @param string $table Database tablename
256 * @param string $where WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
257 * @param array $fields_values Field values as key=>value pairs. Values will be escaped internally. Typically you would fill an array like "$updateFields" with 'fieldname'=>'value' and pass it to this function as argument.
258 * @param bool|array|string $no_quote_fields See fullQuoteArray()
259 * @return bool|\mysqli_result|object MySQLi result object / DBAL object
260 */
261 public function exec_UPDATEquery($table, $where, $fields_values, $no_quote_fields = FALSE) {
262 $res = $this->query($this->UPDATEquery($table, $where, $fields_values, $no_quote_fields));
263 if ($this->debugOutput) {
264 $this->debug('exec_UPDATEquery');
265 }
266 foreach ($this->postProcessHookObjects as $hookObject) {
267 /** @var $hookObject PostProcessQueryHookInterface */
268 $hookObject->exec_UPDATEquery_postProcessAction($table, $where, $fields_values, $no_quote_fields, $this);
269 }
270 return $res;
271 }
272
273 /**
274 * Creates and executes a DELETE SQL-statement for $table where $where-clause
275 *
276 * @param string $table Database tablename
277 * @param string $where WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
278 * @return bool|\mysqli_result|object MySQLi result object / DBAL object
279 */
280 public function exec_DELETEquery($table, $where) {
281 $res = $this->query($this->DELETEquery($table, $where));
282 if ($this->debugOutput) {
283 $this->debug('exec_DELETEquery');
284 }
285 foreach ($this->postProcessHookObjects as $hookObject) {
286 /** @var $hookObject PostProcessQueryHookInterface */
287 $hookObject->exec_DELETEquery_postProcessAction($table, $where, $this);
288 }
289 return $res;
290 }
291
292 /**
293 * Creates and executes a SELECT SQL-statement
294 * Using this function specifically allow us to handle the LIMIT feature independently of DB.
295 *
296 * @param string $select_fields List of fields to select from the table. This is what comes right after "SELECT ...". Required value.
297 * @param string $from_table Table(s) from which to select. This is what comes right after "FROM ...". Required value.
298 * @param string $where_clause Additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT!
299 * @param string $groupBy Optional GROUP BY field(s), if none, supply blank string.
300 * @param string $orderBy Optional ORDER BY field(s), if none, supply blank string.
301 * @param string $limit Optional LIMIT value ([begin,]max), if none, supply blank string.
302 * @return bool|\mysqli_result|object MySQLi result object / DBAL object
303 */
304 public function exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '') {
305 $query = $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
306 $res = $this->query($query);
307 if ($this->debugOutput) {
308 $this->debug('exec_SELECTquery');
309 }
310 if ($this->explainOutput) {
311 $this->explain($query, $from_table, $res->num_rows);
312 }
313 foreach ($this->postProcessHookObjects as $hookObject) {
314 /** @var $hookObject PostProcessQueryHookInterface */
315 $hookObject->exec_SELECTquery_postProcessAction($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', $this);
316 }
317 return $res;
318 }
319
320 /**
321 * Creates and executes a SELECT query, selecting fields ($select) from two/three tables joined
322 * Use $mm_table together with $local_table or $foreign_table to select over two tables. Or use all three tables to select the full MM-relation.
323 * The JOIN is done with [$local_table].uid <--> [$mm_table].uid_local / [$mm_table].uid_foreign <--> [$foreign_table].uid
324 * The function is very useful for selecting MM-relations between tables adhering to the MM-format used by TCE (TYPO3 Core Engine). See the section on $GLOBALS['TCA'] in Inside TYPO3 for more details.
325 *
326 * @param string $select Field list for SELECT
327 * @param string $local_table Tablename, local table
328 * @param string $mm_table Tablename, relation table
329 * @param string $foreign_table Tablename, foreign table
330 * @param string $whereClause Optional additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT! You have to prepend 'AND ' to this parameter yourself!
331 * @param string $groupBy Optional GROUP BY field(s), if none, supply blank string.
332 * @param string $orderBy Optional ORDER BY field(s), if none, supply blank string.
333 * @param string $limit Optional LIMIT value ([begin,]max), if none, supply blank string.
334 * @return bool|\mysqli_result|object MySQLi result object / DBAL object
335 * @see exec_SELECTquery()
336 */
337 public function exec_SELECT_mm_query($select, $local_table, $mm_table, $foreign_table, $whereClause = '', $groupBy = '', $orderBy = '', $limit = '') {
338 $queryParts = $this->getSelectMmQueryParts($select, $local_table, $mm_table, $foreign_table, $whereClause, $groupBy, $orderBy, $limit);
339 return $this->exec_SELECT_queryArray($queryParts);
340 }
341
342 /**
343 * Executes a select based on input query parts array
344 *
345 * @param array $queryParts Query parts array
346 * @return bool|\mysqli_result|object MySQLi result object / DBAL object
347 * @see exec_SELECTquery()
348 */
349 public function exec_SELECT_queryArray($queryParts) {
350 return $this->exec_SELECTquery($queryParts['SELECT'], $queryParts['FROM'], $queryParts['WHERE'], $queryParts['GROUPBY'], $queryParts['ORDERBY'], $queryParts['LIMIT']);
351 }
352
353 /**
354 * Creates and executes a SELECT SQL-statement AND traverse result set and returns array with records in.
355 *
356 * @param string $select_fields List of fields to select from the table. This is what comes right after "SELECT ...". Required value.
357 * @param string $from_table Table(s) from which to select. This is what comes right after "FROM ...". Required value.
358 * @param string $where_clause Additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT!
359 * @param string $groupBy Optional GROUP BY field(s), if none, supply blank string.
360 * @param string $orderBy Optional ORDER BY field(s), if none, supply blank string.
361 * @param string $limit Optional LIMIT value ([begin,]max), if none, supply blank string.
362 * @param string $uidIndexField If set, the result array will carry this field names value as index. Requires that field to be selected of course!
363 * @return array|NULL Array of rows, or NULL in case of SQL error
364 * @see exec_SELECTquery()
365 * @throws \InvalidArgumentException
366 */
367 public function exec_SELECTgetRows($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', $uidIndexField = '') {
368 $res = $this->exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
369 if ($this->sql_error()) {
370 $this->sql_free_result($res);
371 return NULL;
372 }
373 $output = array();
374 $firstRecord = TRUE;
375 while ($record = $this->sql_fetch_assoc($res)) {
376 if ($uidIndexField) {
377 if ($firstRecord) {
378 $firstRecord = FALSE;
379 if (!array_key_exists($uidIndexField, $record)) {
380 $this->sql_free_result($res);
381 throw new \InvalidArgumentException('The given $uidIndexField "' . $uidIndexField . '" is not available in the result.', 1432933855);
382 }
383 }
384 $output[$record[$uidIndexField]] = $record;
385 } else {
386 $output[] = $record;
387 }
388 }
389 $this->sql_free_result($res);
390 return $output;
391 }
392
393 /**
394 * Creates and executes a SELECT SQL-statement AND gets a result set and returns an array with a single record in.
395 * LIMIT is automatically set to 1 and can not be overridden.
396 *
397 * @param string $select_fields List of fields to select from the table.
398 * @param string $from_table Table(s) from which to select.
399 * @param string $where_clause Optional additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
400 * @param string $groupBy Optional GROUP BY field(s), if none, supply blank string.
401 * @param string $orderBy Optional ORDER BY field(s), if none, supply blank string.
402 * @param bool $numIndex If set, the result will be fetched with sql_fetch_row, otherwise sql_fetch_assoc will be used.
403 * @return array|FALSE|NULL Single row, FALSE on empty result, NULL on error
404 */
405 public function exec_SELECTgetSingleRow($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $numIndex = FALSE) {
406 $res = $this->exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, '1');
407 $output = NULL;
408 if ($res !== FALSE) {
409 if ($numIndex) {
410 $output = $this->sql_fetch_row($res);
411 } else {
412 $output = $this->sql_fetch_assoc($res);
413 }
414 $this->sql_free_result($res);
415 }
416 return $output;
417 }
418
419 /**
420 * Counts the number of rows in a table.
421 *
422 * @param string $field Name of the field to use in the COUNT() expression (e.g. '*')
423 * @param string $table Name of the table to count rows for
424 * @param string $where (optional) WHERE statement of the query
425 * @return mixed Number of rows counter (int) or FALSE if something went wrong (bool)
426 */
427 public function exec_SELECTcountRows($field, $table, $where = '1=1') {
428 $count = FALSE;
429 $resultSet = $this->exec_SELECTquery('COUNT(' . $field . ')', $table, $where);
430 if ($resultSet !== FALSE) {
431 list($count) = $this->sql_fetch_row($resultSet);
432 $count = (int)$count;
433 $this->sql_free_result($resultSet);
434 }
435 return $count;
436 }
437
438 /**
439 * Truncates a table.
440 *
441 * @param string $table Database tablename
442 * @return mixed Result from handler
443 */
444 public function exec_TRUNCATEquery($table) {
445 $res = $this->query($this->TRUNCATEquery($table));
446 if ($this->debugOutput) {
447 $this->debug('exec_TRUNCATEquery');
448 }
449 foreach ($this->postProcessHookObjects as $hookObject) {
450 /** @var $hookObject PostProcessQueryHookInterface */
451 $hookObject->exec_TRUNCATEquery_postProcessAction($table, $this);
452 }
453 return $res;
454 }
455
456 /**
457 * Central query method. Also checks if there is a database connection.
458 * Use this to execute database queries instead of directly calling $this->link->query()
459 *
460 * @param string $query The query to send to the database
461 * @return bool|\mysqli_result
462 */
463 protected function query($query) {
464 if (!$this->isConnected) {
465 $this->connectDB();
466 }
467 return $this->link->query($query);
468 }
469
470 /**************************************
471 *
472 * Query building
473 *
474 **************************************/
475 /**
476 * Creates an INSERT SQL-statement for $table from the array with field/value pairs $fields_values.
477 *
478 * @param string $table See exec_INSERTquery()
479 * @param array $fields_values See exec_INSERTquery()
480 * @param bool|array|string $no_quote_fields See fullQuoteArray()
481 * @return string|NULL Full SQL query for INSERT, NULL if $fields_values is empty
482 */
483 public function INSERTquery($table, $fields_values, $no_quote_fields = FALSE) {
484 // Table and fieldnames should be "SQL-injection-safe" when supplied to this
485 // function (contrary to values in the arrays which may be insecure).
486 if (!is_array($fields_values) || empty($fields_values)) {
487 return NULL;
488 }
489 foreach ($this->preProcessHookObjects as $hookObject) {
490 $hookObject->INSERTquery_preProcessAction($table, $fields_values, $no_quote_fields, $this);
491 }
492 // Quote and escape values
493 $fields_values = $this->fullQuoteArray($fields_values, $table, $no_quote_fields, TRUE);
494 // Build query
495 $query = 'INSERT INTO ' . $table . ' (' . implode(',', array_keys($fields_values)) . ') VALUES ' . '(' . implode(',', $fields_values) . ')';
496 // Return query
497 if ($this->debugOutput || $this->store_lastBuiltQuery) {
498 $this->debug_lastBuiltQuery = $query;
499 }
500 return $query;
501 }
502
503 /**
504 * Creates an INSERT SQL-statement for $table with multiple rows.
505 *
506 * @param string $table Table name
507 * @param array $fields Field names
508 * @param array $rows Table rows. Each row should be an array with field values mapping to $fields
509 * @param bool|array|string $no_quote_fields See fullQuoteArray()
510 * @return string|NULL Full SQL query for INSERT, NULL if $rows is empty
511 */
512 public function INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = FALSE) {
513 // Table and fieldnames should be "SQL-injection-safe" when supplied to this
514 // function (contrary to values in the arrays which may be insecure).
515 if (empty($rows)) {
516 return NULL;
517 }
518 foreach ($this->preProcessHookObjects as $hookObject) {
519 /** @var $hookObject PreProcessQueryHookInterface */
520 $hookObject->INSERTmultipleRows_preProcessAction($table, $fields, $rows, $no_quote_fields, $this);
521 }
522 // Build query
523 $query = 'INSERT INTO ' . $table . ' (' . implode(', ', $fields) . ') VALUES ';
524 $rowSQL = array();
525 foreach ($rows as $row) {
526 // Quote and escape values
527 $row = $this->fullQuoteArray($row, $table, $no_quote_fields);
528 $rowSQL[] = '(' . implode(', ', $row) . ')';
529 }
530 $query .= implode(', ', $rowSQL);
531 // Return query
532 if ($this->debugOutput || $this->store_lastBuiltQuery) {
533 $this->debug_lastBuiltQuery = $query;
534 }
535 return $query;
536 }
537
538 /**
539 * Creates an UPDATE SQL-statement for $table where $where-clause (typ. 'uid=...') from the array with field/value pairs $fields_values.
540 *
541 *
542 * @param string $table See exec_UPDATEquery()
543 * @param string $where See exec_UPDATEquery()
544 * @param array $fields_values See exec_UPDATEquery()
545 * @param bool|array|string $no_quote_fields See fullQuoteArray()
546 * @throws \InvalidArgumentException
547 * @return string Full SQL query for UPDATE
548 */
549 public function UPDATEquery($table, $where, $fields_values, $no_quote_fields = FALSE) {
550 // Table and fieldnames should be "SQL-injection-safe" when supplied to this
551 // function (contrary to values in the arrays which may be insecure).
552 if (is_string($where)) {
553 foreach ($this->preProcessHookObjects as $hookObject) {
554 /** @var $hookObject PreProcessQueryHookInterface */
555 $hookObject->UPDATEquery_preProcessAction($table, $where, $fields_values, $no_quote_fields, $this);
556 }
557 $fields = array();
558 if (is_array($fields_values) && !empty($fields_values)) {
559 // Quote and escape values
560 $nArr = $this->fullQuoteArray($fields_values, $table, $no_quote_fields, TRUE);
561 foreach ($nArr as $k => $v) {
562 $fields[] = $k . '=' . $v;
563 }
564 }
565 // Build query
566 $query = 'UPDATE ' . $table . ' SET ' . implode(',', $fields) . ((string)$where !== '' ? ' WHERE ' . $where : '');
567 if ($this->debugOutput || $this->store_lastBuiltQuery) {
568 $this->debug_lastBuiltQuery = $query;
569 }
570 return $query;
571 } else {
572 throw new \InvalidArgumentException('TYPO3 Fatal Error: "Where" clause argument for UPDATE query was not a string in $this->UPDATEquery() !', 1270853880);
573 }
574 }
575
576 /**
577 * Creates a DELETE SQL-statement for $table where $where-clause
578 *
579 * @param string $table See exec_DELETEquery()
580 * @param string $where See exec_DELETEquery()
581 * @return string Full SQL query for DELETE
582 * @throws \InvalidArgumentException
583 */
584 public function DELETEquery($table, $where) {
585 if (is_string($where)) {
586 foreach ($this->preProcessHookObjects as $hookObject) {
587 /** @var $hookObject PreProcessQueryHookInterface */
588 $hookObject->DELETEquery_preProcessAction($table, $where, $this);
589 }
590 // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
591 $query = 'DELETE FROM ' . $table . ((string)$where !== '' ? ' WHERE ' . $where : '');
592 if ($this->debugOutput || $this->store_lastBuiltQuery) {
593 $this->debug_lastBuiltQuery = $query;
594 }
595 return $query;
596 } else {
597 throw new \InvalidArgumentException('TYPO3 Fatal Error: "Where" clause argument for DELETE query was not a string in $this->DELETEquery() !', 1270853881);
598 }
599 }
600
601 /**
602 * Creates a SELECT SQL-statement
603 *
604 * @param string $select_fields See exec_SELECTquery()
605 * @param string $from_table See exec_SELECTquery()
606 * @param string $where_clause See exec_SELECTquery()
607 * @param string $groupBy See exec_SELECTquery()
608 * @param string $orderBy See exec_SELECTquery()
609 * @param string $limit See exec_SELECTquery()
610 * @return string Full SQL query for SELECT
611 */
612 public function SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '') {
613 foreach ($this->preProcessHookObjects as $hookObject) {
614 /** @var $hookObject PreProcessQueryHookInterface */
615 $hookObject->SELECTquery_preProcessAction($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit, $this);
616 }
617 // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
618 // Build basic query
619 $query = 'SELECT ' . $select_fields . ' FROM ' . $from_table . ((string)$where_clause !== '' ? ' WHERE ' . $where_clause : '');
620 // Group by
621 $query .= (string)$groupBy !== '' ? ' GROUP BY ' . $groupBy : '';
622 // Order by
623 $query .= (string)$orderBy !== '' ? ' ORDER BY ' . $orderBy : '';
624 // Group by
625 $query .= (string)$limit !== '' ? ' LIMIT ' . $limit : '';
626 // Return query
627 if ($this->debugOutput || $this->store_lastBuiltQuery) {
628 $this->debug_lastBuiltQuery = $query;
629 }
630 return $query;
631 }
632
633 /**
634 * Creates a SELECT SQL-statement to be used as subquery within another query.
635 * BEWARE: This method should not be overriden within DBAL to prevent quoting from happening.
636 *
637 * @param string $select_fields List of fields to select from the table.
638 * @param string $from_table Table from which to select.
639 * @param string $where_clause Conditional WHERE statement
640 * @return string Full SQL query for SELECT
641 */
642 public function SELECTsubquery($select_fields, $from_table, $where_clause) {
643 // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
644 // Build basic query:
645 $query = 'SELECT ' . $select_fields . ' FROM ' . $from_table . ((string)$where_clause !== '' ? ' WHERE ' . $where_clause : '');
646 // Return query
647 if ($this->debugOutput || $this->store_lastBuiltQuery) {
648 $this->debug_lastBuiltQuery = $query;
649 }
650 return $query;
651 }
652
653 /**
654 * Creates a SELECT query, selecting fields ($select) from two/three tables joined
655 * Use $mm_table together with $local_table or $foreign_table to select over two tables. Or use all three tables to select the full MM-relation.
656 * The JOIN is done with [$local_table].uid <--> [$mm_table].uid_local / [$mm_table].uid_foreign <--> [$foreign_table].uid
657 * The function is very useful for selecting MM-relations between tables adhering to the MM-format used by TCE (TYPO3 Core Engine). See the section on $GLOBALS['TCA'] in Inside TYPO3 for more details.
658 *
659 * @param string $select See exec_SELECT_mm_query()
660 * @param string $local_table See exec_SELECT_mm_query()
661 * @param string $mm_table See exec_SELECT_mm_query()
662 * @param string $foreign_table See exec_SELECT_mm_query()
663 * @param string $whereClause See exec_SELECT_mm_query()
664 * @param string $groupBy See exec_SELECT_mm_query()
665 * @param string $orderBy See exec_SELECT_mm_query()
666 * @param string $limit See exec_SELECT_mm_query()
667 * @return string Full SQL query for SELECT
668 * @see SELECTquery()
669 */
670 public function SELECT_mm_query($select, $local_table, $mm_table, $foreign_table, $whereClause = '', $groupBy = '', $orderBy = '', $limit = '') {
671 $queryParts = $this->getSelectMmQueryParts($select, $local_table, $mm_table, $foreign_table, $whereClause, $groupBy, $orderBy, $limit);
672 return $this->SELECTquery($queryParts['SELECT'], $queryParts['FROM'], $queryParts['WHERE'], $queryParts['GROUPBY'], $queryParts['ORDERBY'], $queryParts['LIMIT']);
673 }
674
675 /**
676 * Creates a TRUNCATE TABLE SQL-statement
677 *
678 * @param string $table See exec_TRUNCATEquery()
679 * @return string Full SQL query for TRUNCATE TABLE
680 */
681 public function TRUNCATEquery($table) {
682 foreach ($this->preProcessHookObjects as $hookObject) {
683 /** @var $hookObject PreProcessQueryHookInterface */
684 $hookObject->TRUNCATEquery_preProcessAction($table, $this);
685 }
686 // Table should be "SQL-injection-safe" when supplied to this function
687 // Build basic query:
688 $query = 'TRUNCATE TABLE ' . $table;
689 // Return query:
690 if ($this->debugOutput || $this->store_lastBuiltQuery) {
691 $this->debug_lastBuiltQuery = $query;
692 }
693 return $query;
694 }
695
696 /**
697 * Returns a WHERE clause that can find a value ($value) in a list field ($field)
698 * For instance a record in the database might contain a list of numbers,
699 * "34,234,5" (with no spaces between). This query would be able to select that
700 * record based on the value "34", "234" or "5" regardless of their position in
701 * the list (left, middle or right).
702 * The value must not contain a comma (,)
703 * Is nice to look up list-relations to records or files in TYPO3 database tables.
704 *
705 * @param string $field Field name
706 * @param string $value Value to find in list
707 * @param string $table Table in which we are searching (for DBAL detection of quoteStr() method)
708 * @return string WHERE clause for a query
709 * @throws \InvalidArgumentException
710 */
711 public function listQuery($field, $value, $table) {
712 $value = (string)$value;
713 if (strpos($value, ',') !== FALSE) {
714 throw new \InvalidArgumentException('$value must not contain a comma (,) in $this->listQuery() !', 1294585862);
715 }
716 $pattern = $this->quoteStr($value, $table);
717 $where = 'FIND_IN_SET(\'' . $pattern . '\',' . $field . ')';
718 return $where;
719 }
720
721 /**
722 * Returns a WHERE clause which will make an AND or OR search for the words in the $searchWords array in any of the fields in array $fields.
723 *
724 * @param array $searchWords Array of search words
725 * @param array $fields Array of fields
726 * @param string $table Table in which we are searching (for DBAL detection of quoteStr() method)
727 * @param string $constraint How multiple search words have to match ('AND' or 'OR')
728 * @return string WHERE clause for search
729 */
730 public function searchQuery($searchWords, $fields, $table, $constraint = self::AND_Constraint) {
731 switch ($constraint) {
732 case self::OR_Constraint:
733 $constraint = 'OR';
734 break;
735 default:
736 $constraint = 'AND';
737 }
738
739 $queryParts = array();
740 foreach ($searchWords as $sw) {
741 $like = ' LIKE \'%' . $this->quoteStr($this->escapeStrForLike($sw, $table), $table) . '%\'';
742 $queryParts[] = $table . '.' . implode(($like . ' OR ' . $table . '.'), $fields) . $like;
743 }
744 $query = '(' . implode(') ' . $constraint . ' (', $queryParts) . ')';
745
746 return $query;
747 }
748
749 /**************************************
750 *
751 * Prepared Query Support
752 *
753 **************************************/
754 /**
755 * Creates a SELECT prepared SQL statement.
756 *
757 * @param string $select_fields See exec_SELECTquery()
758 * @param string $from_table See exec_SELECTquery()
759 * @param string $where_clause See exec_SELECTquery()
760 * @param string $groupBy See exec_SELECTquery()
761 * @param string $orderBy See exec_SELECTquery()
762 * @param string $limit See exec_SELECTquery()
763 * @param array $input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_AUTOTYPE.
764 * @return \TYPO3\CMS\Core\Database\PreparedStatement Prepared statement
765 */
766 public function prepare_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', array $input_parameters = array()) {
767 $query = $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
768 /** @var $preparedStatement \TYPO3\CMS\Core\Database\PreparedStatement */
769 $preparedStatement = GeneralUtility::makeInstance(\TYPO3\CMS\Core\Database\PreparedStatement::class, $query, $from_table, array());
770 // Bind values to parameters
771 foreach ($input_parameters as $key => $value) {
772 $preparedStatement->bindValue($key, $value, PreparedStatement::PARAM_AUTOTYPE);
773 }
774 // Return prepared statement
775 return $preparedStatement;
776 }
777
778 /**
779 * Creates a SELECT prepared SQL statement based on input query parts array
780 *
781 * @param array $queryParts Query parts array
782 * @param array $input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_AUTOTYPE.
783 * @return \TYPO3\CMS\Core\Database\PreparedStatement Prepared statement
784 */
785 public function prepare_SELECTqueryArray(array $queryParts, array $input_parameters = array()) {
786 return $this->prepare_SELECTquery($queryParts['SELECT'], $queryParts['FROM'], $queryParts['WHERE'], $queryParts['GROUPBY'], $queryParts['ORDERBY'], $queryParts['LIMIT'], $input_parameters);
787 }
788
789 /**
790 * Prepares a prepared query.
791 *
792 * @param string $query The query to execute
793 * @param array $queryComponents The components of the query to execute
794 * @return \mysqli_stmt|object MySQLi statement / DBAL object
795 * @internal This method may only be called by \TYPO3\CMS\Core\Database\PreparedStatement
796 */
797 public function prepare_PREPAREDquery($query, array $queryComponents) {
798 if (!$this->isConnected) {
799 $this->connectDB();
800 }
801 $stmt = $this->link->stmt_init();
802 $success = $stmt->prepare($query);
803 if ($this->debugOutput) {
804 $this->debug('stmt_execute', $query);
805 }
806 return $success ? $stmt : NULL;
807 }
808
809 /**************************************
810 *
811 * Various helper functions
812 *
813 * Functions recommended to be used for
814 * - escaping values,
815 * - cleaning lists of values,
816 * - stripping of excess ORDER BY/GROUP BY keywords
817 *
818 **************************************/
819 /**
820 * Escaping and quoting values for SQL statements.
821 *
822 * @param string $str Input string
823 * @param string $table Table name for which to quote string. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how to quote the string!).
824 * @param bool $allowNull Whether to allow NULL values
825 * @return string Output string; Wrapped in single quotes and quotes in the string (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
826 * @see quoteStr()
827 */
828 public function fullQuoteStr($str, $table, $allowNull = FALSE) {
829 if (!$this->isConnected) {
830 $this->connectDB();
831 }
832 if ($allowNull && $str === NULL) {
833 return 'NULL';
834 }
835 if (is_bool($str)) {
836 $str = (int)$str;
837 }
838
839 return '\'' . $this->link->real_escape_string($str) . '\'';
840 }
841
842 /**
843 * Will fullquote all values in the one-dimensional array so they are ready to "implode" for an sql query.
844 *
845 * @param array $arr Array with values (either associative or non-associative array)
846 * @param string $table Table name for which to quote
847 * @param bool|array $noQuote List/array of keys NOT to quote (eg. SQL functions) - ONLY for associative arrays
848 * @param bool $allowNull Whether to allow NULL values
849 * @return array The input array with the values quoted
850 * @see cleanIntArray()
851 */
852 public function fullQuoteArray($arr, $table, $noQuote = FALSE, $allowNull = FALSE) {
853 if (is_string($noQuote)) {
854 $noQuote = explode(',', $noQuote);
855 } elseif (!is_array($noQuote)) {
856 $noQuote = FALSE;
857 }
858 foreach ($arr as $k => $v) {
859 if ($noQuote === FALSE || !in_array($k, $noQuote)) {
860 $arr[$k] = $this->fullQuoteStr($v, $table, $allowNull);
861 }
862 }
863 return $arr;
864 }
865
866 /**
867 * Substitution for PHP function "addslashes()"
868 * Use this function instead of the PHP addslashes() function when you build queries - this will prepare your code for DBAL.
869 * NOTICE: You must wrap the output of this function in SINGLE QUOTES to be DBAL compatible. Unless you have to apply the single quotes yourself you should rather use ->fullQuoteStr()!
870 *
871 * @param string $str Input string
872 * @param string $table Table name for which to quote string. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how to quote the string!).
873 * @return string Output string; Quotes (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
874 * @see quoteStr()
875 */
876 public function quoteStr($str, $table) {
877 if (!$this->isConnected) {
878 $this->connectDB();
879 }
880 return $this->link->real_escape_string($str);
881 }
882
883 /**
884 * Escaping values for SQL LIKE statements.
885 *
886 * @param string $str Input string
887 * @param string $table Table name for which to escape string. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how to quote the string!).
888 * @return string Output string; % and _ will be escaped with \ (or otherwise based on DBAL handler)
889 * @see quoteStr()
890 */
891 public function escapeStrForLike($str, $table) {
892 return addcslashes($str, '_%');
893 }
894
895 /**
896 * Will convert all values in the one-dimensional array to integers.
897 * Useful when you want to make sure an array contains only integers before imploding them in a select-list.
898 *
899 * @param array $arr Array with values
900 * @return array The input array with all values cast to (int)
901 * @see cleanIntList()
902 */
903 public function cleanIntArray($arr) {
904 return array_map('intval', $arr);
905 }
906
907 /**
908 * Will force all entries in the input comma list to integers
909 * Useful when you want to make sure a commalist of supposed integers really contain only integers; You want to know that when you don't trust content that could go into an SQL statement.
910 *
911 * @param string $list List of comma-separated values which should be integers
912 * @return string The input list but with every value cast to (int)
913 * @see cleanIntArray()
914 */
915 public function cleanIntList($list) {
916 return implode(',', GeneralUtility::intExplode(',', $list));
917 }
918
919 /**
920 * Removes the prefix "ORDER BY" from the input string.
921 * This function is used when you call the exec_SELECTquery() function and want to pass the ORDER BY parameter by can't guarantee that "ORDER BY" is not prefixed.
922 * Generally; This function provides a work-around to the situation where you cannot pass only the fields by which to order the result.
923 *
924 * @param string $str eg. "ORDER BY title, uid
925 * @return string eg. "title, uid
926 * @see exec_SELECTquery(), stripGroupBy()
927 */
928 public function stripOrderBy($str) {
929 return preg_replace('/^(?:ORDER[[:space:]]*BY[[:space:]]*)+/i', '', trim($str));
930 }
931
932 /**
933 * Removes the prefix "GROUP BY" from the input string.
934 * This function is used when you call the SELECTquery() function and want to pass the GROUP BY parameter by can't guarantee that "GROUP BY" is not prefixed.
935 * Generally; This function provides a work-around to the situation where you cannot pass only the fields by which to order the result.
936 *
937 * @param string $str eg. "GROUP BY title, uid
938 * @return string eg. "title, uid
939 * @see exec_SELECTquery(), stripOrderBy()
940 */
941 public function stripGroupBy($str) {
942 return preg_replace('/^(?:GROUP[[:space:]]*BY[[:space:]]*)+/i', '', trim($str));
943 }
944
945 /**
946 * Takes the last part of a query, eg. "... uid=123 GROUP BY title ORDER BY title LIMIT 5,2" and splits each part into a table (WHERE, GROUPBY, ORDERBY, LIMIT)
947 * Work-around function for use where you know some userdefined end to an SQL clause is supplied and you need to separate these factors.
948 *
949 * @param string $str Input string
950 * @return array
951 * @deprecated since TYPO3 CMS 7, will be removed in TYPO3 CMS 8
952 */
953 public function splitGroupOrderLimit($str) {
954 GeneralUtility::logDeprecatedFunction();
955 // Prepending a space to make sure "[[:space:]]+" will find a space there
956 // for the first element.
957 $str = ' ' . $str;
958 // Init output array:
959 $wgolParts = array(
960 'WHERE' => '',
961 'GROUPBY' => '',
962 'ORDERBY' => '',
963 'LIMIT' => ''
964 );
965 // Find LIMIT
966 $reg = array();
967 if (preg_match('/^(.*)[[:space:]]+LIMIT[[:space:]]+([[:alnum:][:space:],._]+)$/i', $str, $reg)) {
968 $wgolParts['LIMIT'] = trim($reg[2]);
969 $str = $reg[1];
970 }
971 // Find ORDER BY
972 $reg = array();
973 if (preg_match('/^(.*)[[:space:]]+ORDER[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$/i', $str, $reg)) {
974 $wgolParts['ORDERBY'] = trim($reg[2]);
975 $str = $reg[1];
976 }
977 // Find GROUP BY
978 $reg = array();
979 if (preg_match('/^(.*)[[:space:]]+GROUP[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$/i', $str, $reg)) {
980 $wgolParts['GROUPBY'] = trim($reg[2]);
981 $str = $reg[1];
982 }
983 // Rest is assumed to be "WHERE" clause
984 $wgolParts['WHERE'] = $str;
985 return $wgolParts;
986 }
987
988 /**
989 * Returns the date and time formats compatible with the given database table.
990 *
991 * @param string $table Table name for which to return an empty date. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how date and time should be formatted).
992 * @return array
993 */
994 public function getDateTimeFormats($table) {
995 return self::$dateTimeFormats;
996 }
997
998 /**
999 * Creates SELECT query components for selecting fields ($select) from two/three tables joined
1000 * Use $mm_table together with $local_table or $foreign_table to select over two tables. Or use all three tables to select the full MM-relation.
1001 * The JOIN is done with [$local_table].uid <--> [$mm_table].uid_local / [$mm_table].uid_foreign <--> [$foreign_table].uid
1002 * The function is very useful for selecting MM-relations between tables adhering to the MM-format used by TCE (TYPO3 Core Engine). See the section on $GLOBALS['TCA'] in Inside TYPO3 for more details.
1003 *
1004 * @param string $select See exec_SELECT_mm_query()
1005 * @param string $local_table See exec_SELECT_mm_query()
1006 * @param string $mm_table See exec_SELECT_mm_query()
1007 * @param string $foreign_table See exec_SELECT_mm_query()
1008 * @param string $whereClause See exec_SELECT_mm_query()
1009 * @param string $groupBy See exec_SELECT_mm_query()
1010 * @param string $orderBy See exec_SELECT_mm_query()
1011 * @param string $limit See exec_SELECT_mm_query()
1012 * @return array SQL query components
1013 */
1014 protected function getSelectMmQueryParts($select, $local_table, $mm_table, $foreign_table, $whereClause = '', $groupBy = '', $orderBy = '', $limit = '') {
1015 $foreign_table_as = $foreign_table == $local_table ? $foreign_table . StringUtility::getUniqueId('_join') : '';
1016 $mmWhere = $local_table ? $local_table . '.uid=' . $mm_table . '.uid_local' : '';
1017 $mmWhere .= ($local_table and $foreign_table) ? ' AND ' : '';
1018 $tables = ($local_table ? $local_table . ',' : '') . $mm_table;
1019 if ($foreign_table) {
1020 $mmWhere .= ($foreign_table_as ?: $foreign_table) . '.uid=' . $mm_table . '.uid_foreign';
1021 $tables .= ',' . $foreign_table . ($foreign_table_as ? ' AS ' . $foreign_table_as : '');
1022 }
1023 return array(
1024 'SELECT' => $select,
1025 'FROM' => $tables,
1026 'WHERE' => $mmWhere . ' ' . $whereClause,
1027 'GROUPBY' => $groupBy,
1028 'ORDERBY' => $orderBy,
1029 'LIMIT' => $limit
1030 );
1031 }
1032
1033 /**************************************
1034 *
1035 * MySQL(i) wrapper functions
1036 * (For use in your applications)
1037 *
1038 **************************************/
1039 /**
1040 * Executes query
1041 * MySQLi query() wrapper function
1042 * Beware: Use of this method should be avoided as it is experimentally supported by DBAL. You should consider
1043 * using exec_SELECTquery() and similar methods instead.
1044 *
1045 * @param string $query Query to execute
1046 * @return bool|\mysqli_result|object MySQLi result object / DBAL object
1047 */
1048 public function sql_query($query) {
1049 $res = $this->query($query);
1050 if ($this->debugOutput) {
1051 $this->debug('sql_query', $query);
1052 }
1053 return $res;
1054 }
1055
1056 /**
1057 * Returns the error status on the last query() execution
1058 *
1059 * @return string MySQLi error string.
1060 */
1061 public function sql_error() {
1062 return $this->link->error;
1063 }
1064
1065 /**
1066 * Returns the error number on the last query() execution
1067 *
1068 * @return int MySQLi error number
1069 */
1070 public function sql_errno() {
1071 return $this->link->errno;
1072 }
1073
1074 /**
1075 * Returns the number of selected rows.
1076 *
1077 * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
1078 * @return int Number of resulting rows
1079 */
1080 public function sql_num_rows($res) {
1081 if ($this->debug_check_recordset($res)) {
1082 return $res->num_rows;
1083 } else {
1084 return FALSE;
1085 }
1086 }
1087
1088 /**
1089 * Returns an associative array that corresponds to the fetched row, or FALSE if there are no more rows.
1090 * MySQLi fetch_assoc() wrapper function
1091 *
1092 * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
1093 * @return array|boolean Associative array of result row.
1094 */
1095 public function sql_fetch_assoc($res) {
1096 if ($this->debug_check_recordset($res)) {
1097 $result = $res->fetch_assoc();
1098 if ($result === NULL) {
1099 // Needed for compatibility
1100 $result = FALSE;
1101 }
1102 return $result;
1103 } else {
1104 return FALSE;
1105 }
1106 }
1107
1108 /**
1109 * Returns an array that corresponds to the fetched row, or FALSE if there are no more rows.
1110 * The array contains the values in numerical indices.
1111 * MySQLi fetch_row() wrapper function
1112 *
1113 * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
1114 * @return array|boolean Array with result rows.
1115 */
1116 public function sql_fetch_row($res) {
1117 if ($this->debug_check_recordset($res)) {
1118 $result = $res->fetch_row();
1119 if ($result === NULL) {
1120 // Needed for compatibility
1121 $result = FALSE;
1122 }
1123 return $result;
1124 } else {
1125 return FALSE;
1126 }
1127 }
1128
1129 /**
1130 * Free result memory
1131 * free_result() wrapper function
1132 *
1133 * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
1134 * @return bool Returns TRUE on success or FALSE on failure.
1135 */
1136 public function sql_free_result($res) {
1137 if ($this->debug_check_recordset($res) && is_object($res)) {
1138 $res->free();
1139 return TRUE;
1140 } else {
1141 return FALSE;
1142 }
1143 }
1144
1145 /**
1146 * Get the ID generated from the previous INSERT operation
1147 *
1148 * @return int The uid of the last inserted record.
1149 */
1150 public function sql_insert_id() {
1151 return $this->link->insert_id;
1152 }
1153
1154 /**
1155 * Returns the number of rows affected by the last INSERT, UPDATE or DELETE query
1156 *
1157 * @return int Number of rows affected by last query
1158 */
1159 public function sql_affected_rows() {
1160 return $this->link->affected_rows;
1161 }
1162
1163 /**
1164 * Move internal result pointer
1165 *
1166 * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
1167 * @param int $seek Seek result number.
1168 * @return bool Returns TRUE on success or FALSE on failure.
1169 */
1170 public function sql_data_seek($res, $seek) {
1171 if ($this->debug_check_recordset($res)) {
1172 return $res->data_seek($seek);
1173 } else {
1174 return FALSE;
1175 }
1176 }
1177
1178 /**
1179 * Get the type of the specified field in a result
1180 * mysql_field_type() wrapper function
1181 *
1182 * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
1183 * @param int $pointer Field index.
1184 * @return string Returns the name of the specified field index, or FALSE on error
1185 */
1186 public function sql_field_type($res, $pointer) {
1187 // mysql_field_type compatibility map
1188 // taken from: http://www.php.net/manual/en/mysqli-result.fetch-field-direct.php#89117
1189 // Constant numbers see http://php.net/manual/en/mysqli.constants.php
1190 $mysql_data_type_hash = array(
1191 1=>'tinyint',
1192 2=>'smallint',
1193 3=>'int',
1194 4=>'float',
1195 5=>'double',
1196 7=>'timestamp',
1197 8=>'bigint',
1198 9=>'mediumint',
1199 10=>'date',
1200 11=>'time',
1201 12=>'datetime',
1202 13=>'year',
1203 16=>'bit',
1204 //252 is currently mapped to all text and blob types (MySQL 5.0.51a)
1205 253=>'varchar',
1206 254=>'char',
1207 246=>'decimal'
1208 );
1209 if ($this->debug_check_recordset($res)) {
1210 $metaInfo = $res->fetch_field_direct($pointer);
1211 if ($metaInfo === FALSE) {
1212 return FALSE;
1213 }
1214 return $mysql_data_type_hash[$metaInfo->type];
1215 } else {
1216 return FALSE;
1217 }
1218 }
1219
1220 /**
1221 * Open a (persistent) connection to a MySQL server
1222 *
1223 * @return bool|void
1224 * @throws \RuntimeException
1225 */
1226 public function sql_pconnect() {
1227 if ($this->isConnected) {
1228 return $this->link;
1229 }
1230
1231 if (!extension_loaded('mysqli')) {
1232 throw new \RuntimeException(
1233 'Database Error: PHP mysqli extension not loaded. This is a must have for TYPO3 CMS!',
1234 1271492607
1235 );
1236 }
1237
1238 $host = $this->persistentDatabaseConnection
1239 ? 'p:' . $this->databaseHost
1240 : $this->databaseHost;
1241
1242 $this->link = mysqli_init();
1243 $connected = $this->link->real_connect(
1244 $host,
1245 $this->databaseUsername,
1246 $this->databaseUserPassword,
1247 NULL,
1248 (int)$this->databasePort,
1249 $this->databaseSocket,
1250 $this->connectionCompression ? MYSQLI_CLIENT_COMPRESS : 0
1251 );
1252
1253 if ($connected) {
1254 $this->isConnected = TRUE;
1255
1256 if ($this->link->set_charset($this->connectionCharset) === FALSE) {
1257 GeneralUtility::sysLog(
1258 'Error setting connection charset to "' . $this->connectionCharset . '"',
1259 'core',
1260 GeneralUtility::SYSLOG_SEVERITY_ERROR
1261 );
1262 }
1263
1264 foreach ($this->initializeCommandsAfterConnect as $command) {
1265 if ($this->query($command) === FALSE) {
1266 GeneralUtility::sysLog(
1267 'Could not initialize DB connection with query "' . $command . '": ' . $this->sql_error(),
1268 'core',
1269 GeneralUtility::SYSLOG_SEVERITY_ERROR
1270 );
1271 }
1272 }
1273 $this->checkConnectionCharset();
1274 } else {
1275 // @todo This should raise an exception. Would be useful especially to work during installation.
1276 $error_msg = $this->link->connect_error;
1277 $this->link = NULL;
1278 GeneralUtility::sysLog(
1279 'Could not connect to MySQL server ' . $host . ' with user ' . $this->databaseUsername . ': ' . $error_msg,
1280 'core',
1281 GeneralUtility::SYSLOG_SEVERITY_FATAL
1282 );
1283 }
1284 return $this->link;
1285 }
1286
1287 /**
1288 * Select a SQL database
1289 *
1290 * @return bool Returns TRUE on success or FALSE on failure.
1291 */
1292 public function sql_select_db() {
1293 if (!$this->isConnected) {
1294 $this->connectDB();
1295 }
1296
1297 $ret = $this->link->select_db($this->databaseName);
1298 if (!$ret) {
1299 GeneralUtility::sysLog(
1300 'Could not select MySQL database ' . $this->databaseName . ': ' . $this->sql_error(),
1301 'core',
1302 GeneralUtility::SYSLOG_SEVERITY_FATAL
1303 );
1304 }
1305 return $ret;
1306 }
1307
1308 /**************************************
1309 *
1310 * SQL admin functions
1311 * (For use in the Install Tool and Extension Manager)
1312 *
1313 **************************************/
1314 /**
1315 * Listing databases from current MySQL connection. NOTICE: It WILL try to select those databases and thus break selection of current database.
1316 * This is only used as a service function in the (1-2-3 process) of the Install Tool.
1317 * In any case a lookup should be done in the _DEFAULT handler DBMS then.
1318 * Use in Install Tool only!
1319 *
1320 * @return array Each entry represents a database name
1321 * @throws \RuntimeException
1322 */
1323 public function admin_get_dbs() {
1324 $dbArr = array();
1325 $db_list = $this->query("SELECT SCHEMA_NAME FROM information_schema.SCHEMATA");
1326 if ($db_list === FALSE) {
1327 throw new \RuntimeException(
1328 'MySQL Error: Cannot get tablenames: "' . $this->sql_error() . '"!',
1329 1378457171
1330 );
1331 } else {
1332 while ($row = $db_list->fetch_object()) {
1333 try {
1334 $this->setDatabaseName($row->SCHEMA_NAME);
1335 if ($this->sql_select_db()) {
1336 $dbArr[] = $row->SCHEMA_NAME;
1337 }
1338 } catch (\RuntimeException $exception) {
1339 // The exception happens if we cannot connect to the database
1340 // (usually due to missing permissions). This is ok here.
1341 // We catch the exception, skip the database and continue.
1342 }
1343 }
1344 }
1345 return $dbArr;
1346 }
1347
1348 /**
1349 * Returns the list of tables from the default database, TYPO3_db (quering the DBMS)
1350 * In a DBAL this method should 1) look up all tables from the DBMS of
1351 * the _DEFAULT handler and then 2) add all tables *configured* to be managed by other handlers
1352 *
1353 * @return array Array with tablenames as key and arrays with status information as value
1354 */
1355 public function admin_get_tables() {
1356 $whichTables = array();
1357 $tables_result = $this->query('SHOW TABLE STATUS FROM `' . $this->databaseName . '`');
1358 if ($tables_result !== FALSE) {
1359 while ($theTable = $tables_result->fetch_assoc()) {
1360 $whichTables[$theTable['Name']] = $theTable;
1361 }
1362 $tables_result->free();
1363 }
1364 return $whichTables;
1365 }
1366
1367 /**
1368 * Returns information about each field in the $table (quering the DBMS)
1369 * In a DBAL this should look up the right handler for the table and return compatible information
1370 * This function is important not only for the Install Tool but probably for
1371 * DBALs as well since they might need to look up table specific information
1372 * in order to construct correct queries. In such cases this information should
1373 * probably be cached for quick delivery.
1374 *
1375 * @param string $tableName Table name
1376 * @return array Field information in an associative array with fieldname => field row
1377 */
1378 public function admin_get_fields($tableName) {
1379 $output = array();
1380 $columns_res = $this->query('SHOW FULL COLUMNS FROM `' . $tableName . '`');
1381 if ($columns_res !== FALSE) {
1382 while ($fieldRow = $columns_res->fetch_assoc()) {
1383 $output[$fieldRow['Field']] = $fieldRow;
1384 }
1385 $columns_res->free();
1386 }
1387 return $output;
1388 }
1389
1390 /**
1391 * Returns information about each index key in the $table (quering the DBMS)
1392 * In a DBAL this should look up the right handler for the table and return compatible information
1393 *
1394 * @param string $tableName Table name
1395 * @return array Key information in a numeric array
1396 */
1397 public function admin_get_keys($tableName) {
1398 $output = array();
1399 $keyRes = $this->query('SHOW KEYS FROM `' . $tableName . '`');
1400 if ($keyRes !== FALSE) {
1401 while ($keyRow = $keyRes->fetch_assoc()) {
1402 $output[] = $keyRow;
1403 }
1404 $keyRes->free();
1405 }
1406 return $output;
1407 }
1408
1409 /**
1410 * Returns information about the character sets supported by the current DBM
1411 * This function is important not only for the Install Tool but probably for
1412 * DBALs as well since they might need to look up table specific information
1413 * in order to construct correct queries. In such cases this information should
1414 * probably be cached for quick delivery.
1415 *
1416 * This is used by the Install Tool to convert tables with non-UTF8 charsets
1417 * Use in Install Tool only!
1418 *
1419 * @return array Array with Charset as key and an array of "Charset", "Description", "Default collation", "Maxlen" as values
1420 */
1421 public function admin_get_charsets() {
1422 $output = array();
1423 $columns_res = $this->query('SHOW CHARACTER SET');
1424 if ($columns_res !== FALSE) {
1425 while ($row = $columns_res->fetch_assoc()) {
1426 $output[$row['Charset']] = $row;
1427 }
1428 $columns_res->free();
1429 }
1430 return $output;
1431 }
1432
1433 /**
1434 * mysqli() wrapper function, used by the Install Tool and EM for all queries regarding management of the database!
1435 *
1436 * @param string $query Query to execute
1437 * @return bool|\mysqli_result|object MySQLi result object / DBAL object
1438 */
1439 public function admin_query($query) {
1440 $res = $this->query($query);
1441 if ($this->debugOutput) {
1442 $this->debug('admin_query', $query);
1443 }
1444 return $res;
1445 }
1446
1447 /******************************
1448 *
1449 * Connect handling
1450 *
1451 ******************************/
1452
1453 /**
1454 * Set database host
1455 *
1456 * @param string $host
1457 */
1458 public function setDatabaseHost($host = 'localhost') {
1459 $this->disconnectIfConnected();
1460 $this->databaseHost = $host;
1461 }
1462
1463 /**
1464 * Set database port
1465 *
1466 * @param int $port
1467 */
1468 public function setDatabasePort($port = 3306) {
1469 $this->disconnectIfConnected();
1470 $this->databasePort = (int)$port;
1471 }
1472
1473 /**
1474 * Set database socket
1475 *
1476 * @param string|NULL $socket
1477 */
1478 public function setDatabaseSocket($socket = NULL) {
1479 $this->disconnectIfConnected();
1480 $this->databaseSocket = $socket;
1481 }
1482
1483 /**
1484 * Set database name
1485 *
1486 * @param string $name
1487 */
1488 public function setDatabaseName($name) {
1489 $this->disconnectIfConnected();
1490 $this->databaseName = $name;
1491 }
1492
1493 /**
1494 * Set database username
1495 *
1496 * @param string $username
1497 */
1498 public function setDatabaseUsername($username) {
1499 $this->disconnectIfConnected();
1500 $this->databaseUsername = $username;
1501 }
1502
1503 /**
1504 * Set database password
1505 *
1506 * @param string $password
1507 */
1508 public function setDatabasePassword($password) {
1509 $this->disconnectIfConnected();
1510 $this->databaseUserPassword = $password;
1511 }
1512
1513 /**
1514 * Set persistent database connection
1515 *
1516 * @param bool $persistentDatabaseConnection
1517 * @see http://php.net/manual/de/mysqli.persistconns.php
1518 */
1519 public function setPersistentDatabaseConnection($persistentDatabaseConnection) {
1520 $this->disconnectIfConnected();
1521 $this->persistentDatabaseConnection = (bool)$persistentDatabaseConnection;
1522 }
1523
1524 /**
1525 * Set connection compression. Might be an advantage, if SQL server is not on localhost
1526 *
1527 * @param bool $connectionCompression TRUE if connection should be compressed
1528 */
1529 public function setConnectionCompression($connectionCompression) {
1530 $this->disconnectIfConnected();
1531 $this->connectionCompression = (bool)$connectionCompression;
1532 }
1533
1534 /**
1535 * Set commands to be fired after connection was established
1536 *
1537 * @param array $commands List of SQL commands to be executed after connect
1538 */
1539 public function setInitializeCommandsAfterConnect(array $commands) {
1540 $this->disconnectIfConnected();
1541 $this->initializeCommandsAfterConnect = $commands;
1542 }
1543
1544 /**
1545 * Set the charset that should be used for the MySQL connection.
1546 * The given value will be passed on to mysqli_set_charset().
1547 *
1548 * The default value of this setting is utf8.
1549 *
1550 * @param string $connectionCharset The connection charset that will be passed on to mysqli_set_charset() when connecting the database. Default is utf8.
1551 * @return void
1552 */
1553 public function setConnectionCharset($connectionCharset = 'utf8') {
1554 $this->disconnectIfConnected();
1555 $this->connectionCharset = $connectionCharset;
1556 }
1557
1558 /**
1559 * Connects to database for TYPO3 sites:
1560 *
1561 * @throws \RuntimeException
1562 * @throws \UnexpectedValueException
1563 * @return void
1564 */
1565 public function connectDB() {
1566 // Early return if connected already
1567 if ($this->isConnected) {
1568 return;
1569 }
1570
1571 if (!$this->databaseName) {
1572 throw new \RuntimeException(
1573 'TYPO3 Fatal Error: No database selected!',
1574 1270853882
1575 );
1576 }
1577
1578 if ($this->sql_pconnect()) {
1579 if (!$this->sql_select_db()) {
1580 throw new \RuntimeException(
1581 'TYPO3 Fatal Error: Cannot connect to the current database, "' . $this->databaseName . '"!',
1582 1270853883
1583 );
1584 }
1585 } else {
1586 throw new \RuntimeException(
1587 'TYPO3 Fatal Error: The current username, password or host was not accepted when the connection to the database was attempted to be established!',
1588 1270853884
1589 );
1590 }
1591
1592 // Prepare user defined objects (if any) for hooks which extend query methods
1593 $this->preProcessHookObjects = array();
1594 $this->postProcessHookObjects = array();
1595 if (is_array($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_db.php']['queryProcessors'])) {
1596 foreach ($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_db.php']['queryProcessors'] as $classRef) {
1597 $hookObject = GeneralUtility::getUserObj($classRef);
1598 if (!(
1599 $hookObject instanceof PreProcessQueryHookInterface
1600 || $hookObject instanceof PostProcessQueryHookInterface
1601 )) {
1602 throw new \UnexpectedValueException(
1603 '$hookObject must either implement interface TYPO3\\CMS\\Core\\Database\\PreProcessQueryHookInterface or interface TYPO3\\CMS\\Core\\Database\\PostProcessQueryHookInterface',
1604 1299158548
1605 );
1606 }
1607 if ($hookObject instanceof PreProcessQueryHookInterface) {
1608 $this->preProcessHookObjects[] = $hookObject;
1609 }
1610 if ($hookObject instanceof PostProcessQueryHookInterface) {
1611 $this->postProcessHookObjects[] = $hookObject;
1612 }
1613 }
1614 }
1615 }
1616
1617 /**
1618 * Checks if database is connected
1619 *
1620 * @return bool
1621 */
1622 public function isConnected() {
1623 // We think we're still connected
1624 if ($this->isConnected) {
1625 // Check if this is really the case or if the database server has gone away for some reason
1626 // Using mysqlnd ping() does not reconnect (which we would not want anyway since charset etc would not be reinitialized that way)
1627 $this->isConnected = $this->link->ping();
1628 }
1629 return $this->isConnected;
1630 }
1631
1632 /**
1633 * Checks if the current connection character set has the same value
1634 * as the connectionCharset variable.
1635 *
1636 * To determine the character set these MySQL session variables are
1637 * checked: character_set_client, character_set_results and
1638 * character_set_connection.
1639 *
1640 * If the character set does not match or if the session variables
1641 * can not be read a RuntimeException is thrown.
1642 *
1643 * @return void
1644 * @throws \RuntimeException
1645 */
1646 protected function checkConnectionCharset() {
1647 $sessionResult = $this->sql_query('SHOW SESSION VARIABLES LIKE \'character_set%\'');
1648
1649 if ($sessionResult === FALSE) {
1650 GeneralUtility::sysLog(
1651 'Error while retrieving the current charset session variables from the database: ' . $this->sql_error(),
1652 'core',
1653 GeneralUtility::SYSLOG_SEVERITY_ERROR
1654 );
1655 throw new \RuntimeException(
1656 'TYPO3 Fatal Error: Could not determine the current charset of the database.',
1657 1381847136
1658 );
1659 }
1660
1661 $charsetVariables = array();
1662 while (($row = $this->sql_fetch_row($sessionResult)) !== FALSE) {
1663 $variableName = $row[0];
1664 $variableValue = $row[1];
1665 $charsetVariables[$variableName] = $variableValue;
1666 }
1667 $this->sql_free_result($sessionResult);
1668
1669 // These variables are set with the "Set names" command which was
1670 // used in the past. This is why we check them.
1671 $charsetRequiredVariables = array(
1672 'character_set_client',
1673 'character_set_results',
1674 'character_set_connection',
1675 );
1676
1677 $hasValidCharset = TRUE;
1678 foreach ($charsetRequiredVariables as $variableName) {
1679 if (empty($charsetVariables[$variableName])) {
1680 GeneralUtility::sysLog(
1681 'A required session variable is missing in the current MySQL connection: ' . $variableName,
1682 'core',
1683 GeneralUtility::SYSLOG_SEVERITY_ERROR
1684 );
1685 throw new \RuntimeException(
1686 'TYPO3 Fatal Error: Could not determine the value of the database session variable: ' . $variableName,
1687 1381847779
1688 );
1689 }
1690
1691 if ($charsetVariables[$variableName] !== $this->connectionCharset) {
1692 $hasValidCharset = FALSE;
1693 break;
1694 }
1695 }
1696
1697 if (!$hasValidCharset) {
1698 throw new \RuntimeException(
1699 'It looks like the character set ' . $this->connectionCharset . ' is not used for this connection even though it is configured as connection charset. ' .
1700 'This TYPO3 installation is using the $GLOBALS[\'TYPO3_CONF_VARS\'][\'SYS\'][\'setDBinit\'] property with the following value: "' .
1701 $GLOBALS['TYPO3_CONF_VARS']['SYS']['setDBinit'] . '". Please make sure that this command does not overwrite the configured charset. ' .
1702 'Please note that for the TYPO3 database everything other than utf8 is unsupported since version 4.7.',
1703 1389697515
1704 );
1705 }
1706 }
1707
1708 /**
1709 * Disconnect from database if connected
1710 *
1711 * @return void
1712 */
1713 protected function disconnectIfConnected() {
1714 if ($this->isConnected) {
1715 $this->link->close();
1716 $this->isConnected = FALSE;
1717 }
1718 }
1719
1720 /**
1721 * Returns current database handle
1722 *
1723 * @return \mysqli|NULL
1724 */
1725 public function getDatabaseHandle() {
1726 return $this->link;
1727 }
1728
1729 /**
1730 * Set current database handle, usually \mysqli
1731 *
1732 * @param \mysqli $handle
1733 */
1734 public function setDatabaseHandle($handle) {
1735 $this->link = $handle;
1736 }
1737
1738 /**
1739 * Get the MySQL server version
1740 *
1741 * @return string
1742 */
1743 public function getServerVersion() {
1744 return $this->link->server_info;
1745 }
1746
1747 /******************************
1748 *
1749 * Debugging
1750 *
1751 ******************************/
1752 /**
1753 * Debug function: Outputs error if any
1754 *
1755 * @param string $func Function calling debug()
1756 * @param string $query Last query if not last built query
1757 * @return void
1758 */
1759 public function debug($func, $query = '') {
1760 $error = $this->sql_error();
1761 if ($error || (int)$this->debugOutput === 2) {
1762 \TYPO3\CMS\Core\Utility\DebugUtility::debug(
1763 array(
1764 'caller' => \TYPO3\CMS\Core\Database\DatabaseConnection::class . '::' . $func,
1765 'ERROR' => $error,
1766 'lastBuiltQuery' => $query ? $query : $this->debug_lastBuiltQuery,
1767 'debug_backtrace' => \TYPO3\CMS\Core\Utility\DebugUtility::debugTrail()
1768 ),
1769 $func,
1770 is_object($GLOBALS['error']) && @is_callable(array($GLOBALS['error'], 'debug'))
1771 ? ''
1772 : 'DB Error'
1773 );
1774 }
1775 }
1776
1777 /**
1778 * Checks if record set is valid and writes debugging information into devLog if not.
1779 *
1780 * @param bool|\mysqli_result|object MySQLi result object / DBAL object
1781 * @return bool TRUE if the record set is valid, FALSE otherwise
1782 */
1783 public function debug_check_recordset($res) {
1784 if ($res !== FALSE) {
1785 return TRUE;
1786 }
1787 $msg = 'Invalid database result detected';
1788 $trace = debug_backtrace();
1789 array_shift($trace);
1790 $cnt = count($trace);
1791 for ($i = 0; $i < $cnt; $i++) {
1792 // Complete objects are too large for the log
1793 if (isset($trace['object'])) {
1794 unset($trace['object']);
1795 }
1796 }
1797 $msg .= ': function TYPO3\\CMS\\Core\\Database\\DatabaseConnection->' . $trace[0]['function'] . ' called from file ' . substr($trace[0]['file'], (strlen(PATH_site) + 2)) . ' in line ' . $trace[0]['line'];
1798 GeneralUtility::sysLog(
1799 $msg . '. Use a devLog extension to get more details.',
1800 'core',
1801 GeneralUtility::SYSLOG_SEVERITY_ERROR
1802 );
1803 // Send to devLog if enabled
1804 if (TYPO3_DLOG) {
1805 $debugLogData = array(
1806 'SQL Error' => $this->sql_error(),
1807 'Backtrace' => $trace
1808 );
1809 if ($this->debug_lastBuiltQuery) {
1810 $debugLogData = array('SQL Query' => $this->debug_lastBuiltQuery) + $debugLogData;
1811 }
1812 GeneralUtility::devLog($msg . '.', 'Core/t3lib_db', 3, $debugLogData);
1813 }
1814 return FALSE;
1815 }
1816
1817 /**
1818 * Explain select queries
1819 * If $this->explainOutput is set, SELECT queries will be explained here. Only queries with more than one possible result row will be displayed.
1820 * The output is either printed as raw HTML output or embedded into the TS admin panel (checkbox must be enabled!)
1821 *
1822 * @todo Feature is not DBAL-compliant
1823 *
1824 * @param string $query SQL query
1825 * @param string $from_table Table(s) from which to select. This is what comes right after "FROM ...". Required value.
1826 * @param int $row_count Number of resulting rows
1827 * @return bool TRUE if explain was run, FALSE otherwise
1828 */
1829 protected function explain($query, $from_table, $row_count) {
1830 $debugAllowedForIp = GeneralUtility::cmpIP(
1831 GeneralUtility::getIndpEnv('REMOTE_ADDR'),
1832 $GLOBALS['TYPO3_CONF_VARS']['SYS']['devIPmask']
1833 );
1834 if (
1835 (int)$this->explainOutput == 1
1836 || ((int)$this->explainOutput == 2 && $debugAllowedForIp)
1837 ) {
1838 // Raw HTML output
1839 $explainMode = 1;
1840 } elseif ((int)$this->explainOutput == 3 && is_object($GLOBALS['TT'])) {
1841 // Embed the output into the TS admin panel
1842 $explainMode = 2;
1843 } else {
1844 return FALSE;
1845 }
1846 $error = $this->sql_error();
1847 $trail = \TYPO3\CMS\Core\Utility\DebugUtility::debugTrail();
1848 $explain_tables = array();
1849 $explain_output = array();
1850 $res = $this->sql_query('EXPLAIN ' . $query, $this->link);
1851 if (is_a($res, '\\mysqli_result')) {
1852 while ($tempRow = $this->sql_fetch_assoc($res)) {
1853 $explain_output[] = $tempRow;
1854 $explain_tables[] = $tempRow['table'];
1855 }
1856 $this->sql_free_result($res);
1857 }
1858 $indices_output = array();
1859 // Notice: Rows are skipped if there is only one result, or if no conditions are set
1860 if (
1861 $explain_output[0]['rows'] > 1
1862 || GeneralUtility::inList('ALL', $explain_output[0]['type'])
1863 ) {
1864 // Only enable output if it's really useful
1865 $debug = TRUE;
1866 foreach ($explain_tables as $table) {
1867 $tableRes = $this->sql_query('SHOW TABLE STATUS LIKE \'' . $table . '\'');
1868 $isTable = $this->sql_num_rows($tableRes);
1869 if ($isTable) {
1870 $res = $this->sql_query('SHOW INDEX FROM ' . $table, $this->link);
1871 if (is_a($res, '\\mysqli_result')) {
1872 while ($tempRow = $this->sql_fetch_assoc($res)) {
1873 $indices_output[] = $tempRow;
1874 }
1875 $this->sql_free_result($res);
1876 }
1877 }
1878 $this->sql_free_result($tableRes);
1879 }
1880 } else {
1881 $debug = FALSE;
1882 }
1883 if ($debug) {
1884 if ($explainMode) {
1885 $data = array();
1886 $data['query'] = $query;
1887 $data['trail'] = $trail;
1888 $data['row_count'] = $row_count;
1889 if ($error) {
1890 $data['error'] = $error;
1891 }
1892 if (!empty($explain_output)) {
1893 $data['explain'] = $explain_output;
1894 }
1895 if (!empty($indices_output)) {
1896 $data['indices'] = $indices_output;
1897 }
1898 if ($explainMode == 1) {
1899 \TYPO3\CMS\Core\Utility\DebugUtility::debug($data, 'Tables: ' . $from_table, 'DB SQL EXPLAIN');
1900 } elseif ($explainMode == 2) {
1901 $GLOBALS['TT']->setTSselectQuery($data);
1902 }
1903 }
1904 return TRUE;
1905 }
1906 return FALSE;
1907 }
1908
1909 /**
1910 * Serialize destructs current connection
1911 *
1912 * @return array All protected properties that should be saved
1913 */
1914 public function __sleep() {
1915 $this->disconnectIfConnected();
1916 return array(
1917 'debugOutput',
1918 'explainOutput',
1919 'databaseHost',
1920 'databasePort',
1921 'databaseSocket',
1922 'databaseName',
1923 'databaseUsername',
1924 'databaseUserPassword',
1925 'persistentDatabaseConnection',
1926 'connectionCompression',
1927 'initializeCommandsAfterConnect',
1928 'default_charset',
1929 );
1930 }
1931
1932 }