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