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