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