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