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