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