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