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