[BUGFIX] Moving files in filelist renames file to "1"
[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 * Returns the date and time formats compatible with the given database table.
883 *
884 * @param string $table Table name for which to return an empty date. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how date and time should be formatted).
885 * @return array
886 */
887 public function getDateTimeFormats($table) {
888 return array(
889 'date' => array(
890 'empty' => '0000-00-00',
891 'format' => 'Y-m-d',
892 ),
893 'datetime' => array(
894 'empty' => '0000-00-00 00:00:00',
895 'format' => 'Y-m-d H:i:s',
896 ),
897 );
898 }
899
900 /**************************************
901 *
902 * MySQL wrapper functions
903 * (For use in your applications)
904 *
905 **************************************/
906
907 /**
908 * Executes query
909 * mysql_query() wrapper function
910 * Beware: Use of this method should be avoided as it is experimentally supported by DBAL. You should consider
911 * using exec_SELECTquery() and similar methods instead.
912 *
913 * @param string $query Query to execute
914 * @return pointer Result pointer / DBAL object
915 */
916 function sql_query($query) {
917 $res = mysql_query($query, $this->link);
918 if ($this->debugOutput) {
919 $this->debug('sql_query', $query);
920 }
921 return $res;
922 }
923
924 /**
925 * Returns the error status on the last sql() execution
926 * mysql_error() wrapper function
927 *
928 * @return string MySQL error string.
929 */
930 function sql_error() {
931 return mysql_error($this->link);
932 }
933
934 /**
935 * Returns the error number on the last sql() execution
936 * mysql_errno() wrapper function
937 *
938 * @return integer MySQL error number.
939 */
940 function sql_errno() {
941 return mysql_errno($this->link);
942 }
943
944 /**
945 * Returns the number of selected rows.
946 * mysql_num_rows() wrapper function
947 *
948 * @param pointer $res MySQL result pointer (of SELECT query) / DBAL object
949 * @return integer Number of resulting rows
950 */
951 function sql_num_rows($res) {
952 if ($this->debug_check_recordset($res)) {
953 return mysql_num_rows($res);
954 } else {
955 return FALSE;
956 }
957 }
958
959 /**
960 * Returns an associative array that corresponds to the fetched row, or FALSE if there are no more rows.
961 * mysql_fetch_assoc() wrapper function
962 *
963 * @param pointer $res MySQL result pointer (of SELECT query) / DBAL object
964 * @return array Associative array of result row.
965 */
966 function sql_fetch_assoc($res) {
967 if ($this->debug_check_recordset($res)) {
968 return mysql_fetch_assoc($res);
969 } else {
970 return FALSE;
971 }
972 }
973
974 /**
975 * Returns an array that corresponds to the fetched row, or FALSE if there are no more rows.
976 * The array contains the values in numerical indices.
977 * mysql_fetch_row() wrapper function
978 *
979 * @param pointer $res MySQL result pointer (of SELECT query) / DBAL object
980 * @return array Array with result rows.
981 */
982 function sql_fetch_row($res) {
983 if ($this->debug_check_recordset($res)) {
984 return mysql_fetch_row($res);
985 } else {
986 return FALSE;
987 }
988 }
989
990 /**
991 * Free result memory
992 * mysql_free_result() wrapper function
993 *
994 * @param pointer $res MySQL result pointer to free / DBAL object
995 * @return boolean Returns TRUE on success or FALSE on failure.
996 */
997 function sql_free_result($res) {
998 if ($this->debug_check_recordset($res)) {
999 return mysql_free_result($res);
1000 } else {
1001 return FALSE;
1002 }
1003 }
1004
1005 /**
1006 * Get the ID generated from the previous INSERT operation
1007 * mysql_insert_id() wrapper function
1008 *
1009 * @return integer The uid of the last inserted record.
1010 */
1011 function sql_insert_id() {
1012 return mysql_insert_id($this->link);
1013 }
1014
1015 /**
1016 * Returns the number of rows affected by the last INSERT, UPDATE or DELETE query
1017 * mysql_affected_rows() wrapper function
1018 *
1019 * @return integer Number of rows affected by last query
1020 */
1021 function sql_affected_rows() {
1022 return mysql_affected_rows($this->link);
1023 }
1024
1025 /**
1026 * Move internal result pointer
1027 * mysql_data_seek() wrapper function
1028 *
1029 * @param pointer $res MySQL result pointer (of SELECT query) / DBAL object
1030 * @param integer $seek Seek result number.
1031 * @return boolean Returns TRUE on success or FALSE on failure.
1032 */
1033 function sql_data_seek($res, $seek) {
1034 if ($this->debug_check_recordset($res)) {
1035 return mysql_data_seek($res, $seek);
1036 } else {
1037 return FALSE;
1038 }
1039 }
1040
1041 /**
1042 * Get the type of the specified field in a result
1043 * mysql_field_type() wrapper function
1044 *
1045 * @param pointer $res MySQL result pointer (of SELECT query) / DBAL object
1046 * @param integer $pointer Field index.
1047 * @return string Returns the name of the specified field index
1048 */
1049 function sql_field_type($res, $pointer) {
1050 if ($this->debug_check_recordset($res)) {
1051 return mysql_field_type($res, $pointer);
1052 } else {
1053 return FALSE;
1054 }
1055 }
1056
1057 /**
1058 * Open a (persistent) connection to a MySQL server
1059 * mysql_pconnect() wrapper function
1060 *
1061 * @param string $TYPO3_db_host Database host IP/domain
1062 * @param string $TYPO3_db_username Username to connect with.
1063 * @param string $TYPO3_db_password Password to connect with.
1064 * @return pointer Returns a positive MySQL persistent link identifier on success, or FALSE on error.
1065 */
1066 function sql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password) {
1067 // mysql_error() is tied to an established connection
1068 // if the connection fails we need a different method to get the error message
1069 @ini_set('track_errors', 1);
1070 @ini_set('html_errors', 0);
1071
1072 // Check if MySQL extension is loaded
1073 if (!extension_loaded('mysql')) {
1074 $message = 'Database Error: It seems that MySQL support for PHP is not installed!';
1075 throw new RuntimeException($message, 1271492606);
1076 }
1077
1078 // Check for client compression
1079 $isLocalhost = ($TYPO3_db_host == 'localhost' || $TYPO3_db_host == '127.0.0.1');
1080 if ($GLOBALS['TYPO3_CONF_VARS']['SYS']['no_pconnect']) {
1081 if ($GLOBALS['TYPO3_CONF_VARS']['SYS']['dbClientCompress'] && !$isLocalhost) {
1082 // We use PHP's default value for 4th parameter (new_link), which is FALSE.
1083 // See PHP sources, for example: file php-5.2.5/ext/mysql/php_mysql.c,
1084 // function php_mysql_do_connect(), near line 525
1085 $this->link = @mysql_connect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password, FALSE, MYSQL_CLIENT_COMPRESS);
1086 } else {
1087 $this->link = @mysql_connect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password);
1088 }
1089 } else {
1090 if ($GLOBALS['TYPO3_CONF_VARS']['SYS']['dbClientCompress'] && !$isLocalhost) {
1091 // See comment about 4th parameter in block above
1092 $this->link = @mysql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password, MYSQL_CLIENT_COMPRESS);
1093 } else {
1094 $this->link = @mysql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password);
1095 }
1096 }
1097
1098 $error_msg = $php_errormsg;
1099 @ini_restore('track_errors');
1100 @ini_restore('html_errors');
1101
1102 if (!$this->link) {
1103 t3lib_div::sysLog('Could not connect to MySQL server ' . $TYPO3_db_host .
1104 ' with user ' . $TYPO3_db_username . ': ' . $error_msg,
1105 'Core',
1106 t3lib_div::SYSLOG_SEVERITY_FATAL
1107 );
1108 } else {
1109 $setDBinit = t3lib_div::trimExplode(LF, str_replace("' . LF . '", LF, $GLOBALS['TYPO3_CONF_VARS']['SYS']['setDBinit']), TRUE);
1110 foreach ($setDBinit as $v) {
1111 if (mysql_query($v, $this->link) === FALSE) {
1112 t3lib_div::sysLog('Could not initialize DB connection with query "' . $v .
1113 '": ' . mysql_error($this->link),
1114 'Core',
1115 t3lib_div::SYSLOG_SEVERITY_ERROR
1116 );
1117 }
1118 }
1119 $this->setSqlMode();
1120 }
1121
1122 return $this->link;
1123 }
1124
1125 /**
1126 * Fixes the SQL mode by unsetting NO_BACKSLASH_ESCAPES if found.
1127 *
1128 * @return void
1129 */
1130 protected function setSqlMode() {
1131 $resource = $this->sql_query('SELECT @@SESSION.sql_mode;');
1132 if (is_resource($resource)) {
1133 $result = $this->sql_fetch_row($resource);
1134 if (isset($result[0]) && $result[0] && strpos($result[0], 'NO_BACKSLASH_ESCAPES') !== FALSE) {
1135 $modes = array_diff(
1136 t3lib_div::trimExplode(',', $result[0]),
1137 array('NO_BACKSLASH_ESCAPES')
1138 );
1139 $query = 'SET sql_mode=\'' . mysql_real_escape_string(implode(',', $modes)) . '\';';
1140 $success = $this->sql_query($query);
1141
1142 t3lib_div::sysLog(
1143 'NO_BACKSLASH_ESCAPES could not be removed from SQL mode: ' . $this->sql_error(),
1144 'Core',
1145 t3lib_div::SYSLOG_SEVERITY_ERROR
1146 );
1147 }
1148 }
1149 }
1150
1151 /**
1152 * Select a MySQL database
1153 * mysql_select_db() wrapper function
1154 *
1155 * @param string $TYPO3_db Database to connect to.
1156 * @return boolean Returns TRUE on success or FALSE on failure.
1157 */
1158 function sql_select_db($TYPO3_db) {
1159 $ret = @mysql_select_db($TYPO3_db, $this->link);
1160 if (!$ret) {
1161 t3lib_div::sysLog('Could not select MySQL database ' . $TYPO3_db . ': ' .
1162 mysql_error(),
1163 'Core',
1164 t3lib_div::SYSLOG_SEVERITY_FATAL
1165 );
1166 }
1167 return $ret;
1168 }
1169
1170 /**************************************
1171 *
1172 * SQL admin functions
1173 * (For use in the Install Tool and Extension Manager)
1174 *
1175 **************************************/
1176
1177 /**
1178 * Listing databases from current MySQL connection. NOTICE: It WILL try to select those databases and thus break selection of current database.
1179 * This is only used as a service function in the (1-2-3 process) of the Install Tool.
1180 * In any case a lookup should be done in the _DEFAULT handler DBMS then.
1181 * Use in Install Tool only!
1182 *
1183 * @return array Each entry represents a database name
1184 */
1185 function admin_get_dbs() {
1186 $dbArr = array();
1187 $db_list = mysql_list_dbs($this->link);
1188 while ($row = mysql_fetch_object($db_list)) {
1189 if ($this->sql_select_db($row->Database)) {
1190 $dbArr[] = $row->Database;
1191 }
1192 }
1193 return $dbArr;
1194 }
1195
1196 /**
1197 * Returns the list of tables from the default database, TYPO3_db (quering the DBMS)
1198 * In a DBAL this method should 1) look up all tables from the DBMS of
1199 * the _DEFAULT handler and then 2) add all tables *configured* to be managed by other handlers
1200 *
1201 * @return array Array with tablenames as key and arrays with status information as value
1202 */
1203 function admin_get_tables() {
1204 $whichTables = array();
1205
1206 $tables_result = mysql_query('SHOW TABLE STATUS FROM `' . TYPO3_db . '`', $this->link);
1207 if (!mysql_error()) {
1208 while ($theTable = mysql_fetch_assoc($tables_result)) {
1209 $whichTables[$theTable['Name']] = $theTable;
1210 }
1211
1212 $this->sql_free_result($tables_result);
1213 }
1214
1215 return $whichTables;
1216 }
1217
1218 /**
1219 * Returns information about each field in the $table (quering the DBMS)
1220 * In a DBAL this should look up the right handler for the table and return compatible information
1221 * This function is important not only for the Install Tool but probably for
1222 * DBALs as well since they might need to look up table specific information
1223 * in order to construct correct queries. In such cases this information should
1224 * probably be cached for quick delivery.
1225 *
1226 * @param string $tableName Table name
1227 * @return array Field information in an associative array with fieldname => field row
1228 */
1229 function admin_get_fields($tableName) {
1230 $output = array();
1231
1232 $columns_res = mysql_query('SHOW COLUMNS FROM `' . $tableName . '`', $this->link);
1233 while ($fieldRow = mysql_fetch_assoc($columns_res)) {
1234 $output[$fieldRow['Field']] = $fieldRow;
1235 }
1236
1237 $this->sql_free_result($columns_res);
1238
1239 return $output;
1240 }
1241
1242 /**
1243 * Returns information about each index key in the $table (quering the DBMS)
1244 * In a DBAL this should look up the right handler for the table and return compatible information
1245 *
1246 * @param string $tableName Table name
1247 * @return array Key information in a numeric array
1248 */
1249 function admin_get_keys($tableName) {
1250 $output = array();
1251
1252 $keyRes = mysql_query('SHOW KEYS FROM `' . $tableName . '`', $this->link);
1253 while ($keyRow = mysql_fetch_assoc($keyRes)) {
1254 $output[] = $keyRow;
1255 }
1256
1257 $this->sql_free_result($keyRes);
1258
1259 return $output;
1260 }
1261
1262 /**
1263 * Returns information about the character sets supported by the current DBM
1264 * This function is important not only for the Install Tool but probably for
1265 * DBALs as well since they might need to look up table specific information
1266 * in order to construct correct queries. In such cases this information should
1267 * probably be cached for quick delivery.
1268 *
1269 * This is used by the Install Tool to convert tables tables with non-UTF8 charsets
1270 * Use in Install Tool only!
1271 *
1272 * @return array Array with Charset as key and an array of "Charset", "Description", "Default collation", "Maxlen" as values
1273 */
1274 function admin_get_charsets() {
1275 $output = array();
1276
1277 $columns_res = mysql_query('SHOW CHARACTER SET', $this->link);
1278 if ($columns_res) {
1279 while (($row = mysql_fetch_assoc($columns_res))) {
1280 $output[$row['Charset']] = $row;
1281 }
1282
1283 $this->sql_free_result($columns_res);
1284 }
1285
1286 return $output;
1287 }
1288
1289 /**
1290 * mysql() wrapper function, used by the Install Tool and EM for all queries regarding management of the database!
1291 *
1292 * @param string $query Query to execute
1293 * @return pointer Result pointer
1294 */
1295 function admin_query($query) {
1296 $res = mysql_query($query, $this->link);
1297 if ($this->debugOutput) {
1298 $this->debug('admin_query', $query);
1299 }
1300 return $res;
1301 }
1302
1303 /******************************
1304 *
1305 * Connecting service
1306 *
1307 ******************************/
1308
1309 /**
1310 * Connects to database for TYPO3 sites:
1311 *
1312 * @param string $host
1313 * @param string $user
1314 * @param string $password
1315 * @param string $db
1316 * @return void
1317 */
1318 function connectDB($host = TYPO3_db_host, $user = TYPO3_db_username, $password = TYPO3_db_password, $db = TYPO3_db) {
1319 // If no db is given we throw immediately. This is a sign for a fresh (not configured)
1320 // TYPO3 installation and is used in FE to redirect to 1-2-3 install tool
1321 if (!$db) {
1322 throw new RuntimeException(
1323 'TYPO3 Fatal Error: No database selected!',
1324 1270853882
1325 );
1326 }
1327
1328 if ($this->sql_pconnect($host, $user, $password)) {
1329 if (!$this->sql_select_db($db)) {
1330 throw new RuntimeException(
1331 'TYPO3 Fatal Error: Cannot connect to the current database, "' . $db . '"!',
1332 1270853883
1333 );
1334 }
1335 } else {
1336 throw new RuntimeException(
1337 'TYPO3 Fatal Error: The current username, password or host was not accepted when the connection to the database was attempted to be established!',
1338 1270853884
1339 );
1340 }
1341
1342 // Prepare user defined objects (if any) for hooks which extend query methods
1343 $this->preProcessHookObjects = array();
1344 $this->postProcessHookObjects = array();
1345 if (is_array($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_db.php']['queryProcessors'])) {
1346 foreach ($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_db.php']['queryProcessors'] as $classRef) {
1347 $hookObject = t3lib_div::getUserObj($classRef);
1348
1349 if (!($hookObject instanceof t3lib_DB_preProcessQueryHook || $hookObject instanceof t3lib_DB_postProcessQueryHook)) {
1350 throw new UnexpectedValueException('$hookObject must either implement interface t3lib_DB_preProcessQueryHook or interface t3lib_DB_postProcessQueryHook', 1299158548);
1351 }
1352 if ($hookObject instanceof t3lib_DB_preProcessQueryHook) {
1353 $this->preProcessHookObjects[] = $hookObject;
1354 }
1355 if ($hookObject instanceof t3lib_DB_postProcessQueryHook) {
1356 $this->postProcessHookObjects[] = $hookObject;
1357 }
1358 }
1359 }
1360 }
1361
1362 /**
1363 * Checks if database is connected
1364 *
1365 * @return boolean
1366 */
1367 public function isConnected() {
1368 return is_resource($this->link);
1369 }
1370
1371 /******************************
1372 *
1373 * Debugging
1374 *
1375 ******************************/
1376
1377 /**
1378 * Debug function: Outputs error if any
1379 *
1380 * @param string $func Function calling debug()
1381 * @param string $query Last query if not last built query
1382 * @return void
1383 */
1384 function debug($func, $query = '') {
1385
1386 $error = $this->sql_error();
1387 if ($error || (int)$this->debugOutput === 2) {
1388 debug(
1389 array(
1390 'caller' => 't3lib_DB::' . $func,
1391 'ERROR' => $error,
1392 'lastBuiltQuery' => ($query ? $query : $this->debug_lastBuiltQuery),
1393 'debug_backtrace' => t3lib_utility_Debug::debugTrail(),
1394 ),
1395 $func,
1396 is_object($GLOBALS['error']) && @is_callable(array($GLOBALS['error'], 'debug')) ? '' : 'DB Error'
1397 );
1398 }
1399 }
1400
1401 /**
1402 * Checks if record set is valid and writes debugging information into devLog if not.
1403 *
1404 * @param resource|boolean $res record set
1405 * @return boolean TRUE if the record set is valid, FALSE otherwise
1406 */
1407 function debug_check_recordset($res) {
1408 if (is_resource($res)) {
1409 return TRUE;
1410 }
1411
1412 $msg = 'Invalid database result resource detected';
1413 $trace = debug_backtrace();
1414 array_shift($trace);
1415 $cnt = count($trace);
1416 for ($i = 0; $i < $cnt; $i++) {
1417 // Complete objects are too large for the log
1418 if (isset($trace['object'])) {
1419 unset($trace['object']);
1420 }
1421 }
1422 $msg .= ': function t3lib_DB->' . $trace[0]['function'] . ' called from file ' .
1423 substr($trace[0]['file'], strlen(PATH_site) + 2) . ' in line ' .
1424 $trace[0]['line'];
1425 t3lib_div::sysLog(
1426 $msg . '. Use a devLog extension to get more details.',
1427 'Core/t3lib_db',
1428 t3lib_div::SYSLOG_SEVERITY_ERROR
1429 );
1430 // Send to devLog if enabled
1431 if (TYPO3_DLOG) {
1432 $debugLogData = array(
1433 'SQL Error' => $this->sql_error(),
1434 'Backtrace' => $trace,
1435 );
1436 if ($this->debug_lastBuiltQuery) {
1437 $debugLogData = array('SQL Query' => $this->debug_lastBuiltQuery) + $debugLogData;
1438 }
1439 t3lib_div::devLog($msg . '.', 'Core/t3lib_db', 3, $debugLogData);
1440 }
1441
1442 return FALSE;
1443 }
1444
1445 /**
1446 * Explain select queries
1447 * If $this->explainOutput is set, SELECT queries will be explained here. Only queries with more than one possible result row will be displayed.
1448 * The output is either printed as raw HTML output or embedded into the TS admin panel (checkbox must be enabled!)
1449 *
1450 * TODO: Feature is not DBAL-compliant
1451 *
1452 * @param string $query SQL query
1453 * @param string $from_table Table(s) from which to select. This is what comes right after "FROM ...". Required value.
1454 * @param integer $row_count Number of resulting rows
1455 * @return boolean TRUE if explain was run, FALSE otherwise
1456 */
1457 protected function explain($query, $from_table, $row_count) {
1458
1459 if ((int) $this->explainOutput == 1 || ((int) $this->explainOutput == 2 &&
1460 t3lib_div::cmpIP(t3lib_div::getIndpEnv('REMOTE_ADDR'), $GLOBALS['TYPO3_CONF_VARS']['SYS']['devIPmask']))
1461 ) {
1462 // Raw HTML output
1463 $explainMode = 1;
1464 } elseif ((int) $this->explainOutput == 3 && is_object($GLOBALS['TT'])) {
1465 // Embed the output into the TS admin panel
1466 $explainMode = 2;
1467 } else {
1468 return FALSE;
1469 }
1470
1471 $error = $this->sql_error();
1472 $trail = t3lib_utility_Debug::debugTrail();
1473
1474 $explain_tables = array();
1475 $explain_output = array();
1476 $res = $this->sql_query('EXPLAIN ' . $query, $this->link);
1477 if (is_resource($res)) {
1478 while ($tempRow = $this->sql_fetch_assoc($res)) {
1479 $explain_output[] = $tempRow;
1480 $explain_tables[] = $tempRow['table'];
1481 }
1482 $this->sql_free_result($res);
1483 }
1484
1485 $indices_output = array();
1486 // Notice: Rows are skipped if there is only one result, or if no conditions are set
1487 if ($explain_output[0]['rows'] > 1 || t3lib_div::inList('ALL', $explain_output[0]['type'])) {
1488 // Only enable output if it's really useful
1489 $debug = TRUE;
1490
1491 foreach ($explain_tables as $table) {
1492 $tableRes = $this->sql_query('SHOW TABLE STATUS LIKE \'' . $table . '\'');
1493 $isTable = $this->sql_num_rows($tableRes);
1494 if ($isTable) {
1495 $res = $this->sql_query('SHOW INDEX FROM ' . $table, $this->link);
1496 if (is_resource($res)) {
1497 while ($tempRow = $this->sql_fetch_assoc($res)) {
1498 $indices_output[] = $tempRow;
1499 }
1500 $this->sql_free_result($res);
1501 }
1502 }
1503 $this->sql_free_result($tableRes);
1504 }
1505 } else {
1506 $debug = FALSE;
1507 }
1508
1509 if ($debug) {
1510 if ($explainMode) {
1511 $data = array();
1512 $data['query'] = $query;
1513 $data['trail'] = $trail;
1514 $data['row_count'] = $row_count;
1515
1516 if ($error) {
1517 $data['error'] = $error;
1518 }
1519 if (count($explain_output)) {
1520 $data['explain'] = $explain_output;
1521 }
1522 if (count($indices_output)) {
1523 $data['indices'] = $indices_output;
1524 }
1525
1526 if ($explainMode == 1) {
1527 t3lib_utility_Debug::debug($data, 'Tables: ' . $from_table, 'DB SQL EXPLAIN');
1528 } elseif ($explainMode == 2) {
1529 $GLOBALS['TT']->setTSselectQuery($data);
1530 }
1531 }
1532 return TRUE;
1533 }
1534
1535 return FALSE;
1536 }
1537 }
1538
1539 ?>