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