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