[TASK] MySQLi for DBAL
[Packages/TYPO3.CMS.git] / typo3 / sysext / dbal / Classes / Database / DatabaseConnection.php
1 <?php
2 namespace TYPO3\CMS\Dbal\Database;
3
4 /***************************************************************
5 * Copyright notice
6 *
7 * (c) 2004-2013 Kasper Skårhøj (kasperYYYY@typo3.com)
8 * (c) 2004-2013 Karsten Dambekalns <karsten@typo3.org>
9 * (c) 2009-2013 Xavier Perseguers <xavier@typo3.org>
10 * (c) 2014 Markus Klein <klein.t3@mfc-linz.at>
11 * All rights reserved
12 *
13 * This script is part of the TYPO3 project. The TYPO3 project is
14 * free software; you can redistribute it and/or modify
15 * it under the terms of the GNU General Public License as published by
16 * the Free Software Foundation; either version 2 of the License, or
17 * (at your option) any later version.
18 *
19 * The GNU General Public License can be found at
20 * http://www.gnu.org/copyleft/gpl.html.
21 * A copy is found in the text file GPL.txt and important notices to the license
22 * from the author is found in LICENSE.txt distributed with these scripts.
23 *
24 *
25 * This script is distributed in the hope that it will be useful,
26 * but WITHOUT ANY WARRANTY; without even the implied warranty of
27 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
28 * GNU General Public License for more details.
29 *
30 * This copyright notice MUST APPEAR in all copies of the script!
31 ***************************************************************/
32
33 use TYPO3\CMS\Core\Utility\GeneralUtility;
34
35 /**
36 * TYPO3 database abstraction layer
37 *
38 * @author Kasper Skårhøj <kasper@typo3.com>
39 * @author Karsten Dambekalns <k.dambekalns@fishfarm.de>
40 * @author Xavier Perseguers <xavier@typo3.org>
41 */
42 class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
43
44 /**
45 * @var bool
46 */
47 protected $printErrors = FALSE;
48
49 /**
50 * Enable output of SQL errors after query executions.
51 * @var bool
52 */
53 public $debug = FALSE;
54
55 /**
56 * Enable debug mode
57 * @var bool
58 */
59 public $conf = array();
60
61 /**
62 * Configuration array, copied from TYPO3_CONF_VARS in constructor.
63 * @var array
64 */
65 public $mapping = array();
66
67 /**
68 * See manual
69 * @var array
70 */
71 protected $table2handlerKeys = array();
72
73 /**
74 * See manual
75 * @var array
76 */
77 public $handlerCfg = array(
78 '_DEFAULT' => array(
79 'type' => 'native',
80 'config' => array(
81 'username' => '',
82 // Set by default (overridden)
83 'password' => '',
84 // Set by default (overridden)
85 'host' => '',
86 // Set by default (overridden)
87 'database' => '',
88 // Set by default (overridden)
89 'driver' => '',
90 // ONLY "adodb" type; eg. "mysql"
91 'sequenceStart' => 1,
92 // ONLY "adodb", first number in sequences/serials/...
93 'useNameQuote' => 0,
94 // ONLY "adodb", whether to use NameQuote() method from ADOdb to quote names
95 'quoteClob' => FALSE
96 )
97 )
98 );
99
100 /**
101 * Contains instance of the handler objects as they are created.
102 *
103 * Exception is the native mySQL calls, which are registered as an array with keys
104 * "handlerType" = "native" and
105 * "link" pointing to the link object of the connection.
106 *
107 * @var array
108 */
109 public $handlerInstance = array();
110
111 /**
112 * Storage of the handler key of last ( SELECT) query - used for subsequent fetch-row calls etc.
113 * @var string
114 */
115 public $lastHandlerKey = '';
116
117 /**
118 * Storage of last SELECT query
119 * @var string
120 */
121 protected $lastQuery = '';
122
123 /**
124 * The last parsed query array
125 * @var array
126 */
127 protected $lastParsedAndMappedQueryArray = array();
128
129 /**
130 * @var array
131 */
132 protected $resourceIdToTableNameMap = array();
133
134 /**
135 * @var array
136 */
137 protected $cache_handlerKeyFromTableList = array();
138
139 /**
140 * @var array
141 */
142 protected $cache_mappingFromTableList = array();
143
144 /**
145 * parsed SQL from standard DB dump file
146 * @var array
147 */
148 public $cache_autoIncFields = array();
149
150 /**
151 * @var array
152 */
153 public $cache_fieldType = array();
154
155 /**
156 * @var array
157 */
158 public $cache_primaryKeys = array();
159
160 /**
161 * @var string
162 */
163 protected $cacheIdentifier = 't3lib_db_fieldInfo';
164
165 /**
166 * SQL parser
167 *
168 * @var \TYPO3\CMS\Core\Database\SqlParser
169 * @todo Define visibility
170 */
171 public $SQLparser;
172
173 /**
174 * @var \TYPO3\CMS\Install\Service\SqlSchemaMigrationService
175 */
176 protected $installerSql = NULL;
177
178 /**
179 * Cache for queries
180 *
181 * @var \TYPO3\CMS\Core\Cache\Frontend\VariableFrontend
182 */
183 protected $queryCache;
184
185 /**
186 * mysql_field_type compatibility map
187 * taken from: http://www.php.net/manual/en/mysqli-result.fetch-field-direct.php#89117
188 * Constant numbers see http://php.net/manual/en/mysqli.constants.php
189 *
190 * @var array
191 */
192 protected $mysqlDataTypeMapping = array(
193 MYSQLI_TYPE_TINY => 'tinyint',
194 MYSQLI_TYPE_CHAR => 'tinyint',
195 MYSQLI_TYPE_SHORT => 'smallint',
196 MYSQLI_TYPE_LONG => 'int',
197 MYSQLI_TYPE_FLOAT => 'float',
198 MYSQLI_TYPE_DOUBLE => 'double',
199 MYSQLI_TYPE_TIMESTAMP => 'timestamp',
200 MYSQLI_TYPE_LONGLONG => 'bigint',
201 MYSQLI_TYPE_INT24 => 'mediumint',
202 MYSQLI_TYPE_DATE => 'date',
203 MYSQLI_TYPE_NEWDATE => 'date',
204 MYSQLI_TYPE_TIME => 'time',
205 MYSQLI_TYPE_DATETIME => 'datetime',
206 MYSQLI_TYPE_YEAR => 'year',
207 MYSQLI_TYPE_BIT => 'bit',
208 MYSQLI_TYPE_INTERVAL => 'interval',
209 MYSQLI_TYPE_ENUM => 'enum',
210 MYSQLI_TYPE_SET => 'set',
211 MYSQLI_TYPE_TINY_BLOB => 'blob',
212 MYSQLI_TYPE_MEDIUM_BLOB => 'blob',
213 MYSQLI_TYPE_LONG_BLOB => 'blob',
214 MYSQLI_TYPE_BLOB => 'blob',
215 MYSQLI_TYPE_VAR_STRING => 'varchar',
216 MYSQLI_TYPE_STRING => 'char',
217 MYSQLI_TYPE_DECIMAL => 'decimal',
218 MYSQLI_TYPE_NEWDECIMAL => 'decimal',
219 MYSQLI_TYPE_GEOMETRY => 'geometry'
220 );
221
222 /**
223 * Constructor.
224 * Creates SQL parser object and imports configuration from $TYPO3_CONF_VARS['EXTCONF']['dbal']
225 */
226 public function __construct() {
227 // Set SQL parser object for internal use:
228 $this->SQLparser = GeneralUtility::makeInstance('TYPO3\\CMS\\Core\\Database\\SqlParser', $this);
229 $this->installerSql = GeneralUtility::makeInstance('TYPO3\\CMS\\Install\\Service\\SqlSchemaMigrationService');
230 $this->queryCache = GeneralUtility::makeInstance('TYPO3\\CMS\\Core\\Cache\\CacheManager')->getCache('dbal');
231 // Set internal variables with configuration:
232 $this->conf = $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal'];
233 }
234
235 /**
236 * Initialize the database connection
237 *
238 * @return void
239 */
240 public function initialize() {
241 // Set outside configuration:
242 if (isset($this->conf['mapping'])) {
243 $this->mapping = $this->conf['mapping'];
244 }
245 if (isset($this->conf['table2handlerKeys'])) {
246 $this->table2handlerKeys = $this->conf['table2handlerKeys'];
247 }
248 if (isset($this->conf['handlerCfg'])) {
249 $this->handlerCfg = $this->conf['handlerCfg'];
250 }
251 $this->cacheFieldInfo();
252 // Debugging settings:
253 $this->printErrors = !empty($this->conf['debugOptions']['printErrors']);
254 $this->debug = !empty($this->conf['debugOptions']['enabled']);
255 }
256
257 /**
258 * @return \TYPO3\CMS\Core\Cache\Frontend\PhpFrontend
259 */
260 protected function getFieldInfoCache() {
261 return GeneralUtility::makeInstance('TYPO3\\CMS\\Core\\Cache\\CacheManager')->getCache('cache_phpcode');
262 }
263
264 /**
265 * Clears the cached field information file.
266 *
267 * @return void
268 */
269 public function clearCachedFieldInfo() {
270 $this->getFieldInfoCache()->flushByTag('t3lib_db');
271 }
272
273 /**
274 * Caches the field information.
275 *
276 * @return void
277 */
278 public function cacheFieldInfo() {
279 $phpCodeCache = $this->getFieldInfoCache();
280 // try to fetch cache
281 // cache is flushed when admin_query() is called
282 if ($phpCodeCache->has($this->cacheIdentifier)) {
283 $fieldInformation = $phpCodeCache->requireOnce($this->cacheIdentifier);
284 $this->cache_autoIncFields = $fieldInformation['incFields'];
285 $this->cache_fieldType = $fieldInformation['fieldTypes'];
286 $this->cache_primaryKeys = $fieldInformation['primaryKeys'];
287 } else {
288 $this->analyzeCachingTables();
289 $this->analyzeExtensionTables();
290 $completeFieldInformation = $this->getCompleteFieldInformation();
291 $phpCodeCache->set($this->cacheIdentifier, $this->getCacheableString($completeFieldInformation), array('t3lib_db'));
292 }
293 }
294
295 /**
296 * Loop through caching configurations
297 * to find the usage of database backends and
298 * parse and analyze table definitions
299 *
300 * @return void
301 */
302 protected function analyzeCachingTables() {
303 $this->parseAndAnalyzeSql(\TYPO3\CMS\Core\Cache\Cache::getDatabaseTableDefinitions());
304 }
305
306 /**
307 * Loop over all installed extensions
308 * parse and analyze table definitions (if any)
309 *
310 * @return void
311 */
312 protected function analyzeExtensionTables() {
313 if (isset($GLOBALS['TYPO3_LOADED_EXT']) && (is_array($GLOBALS['TYPO3_LOADED_EXT']) || $GLOBALS['TYPO3_LOADED_EXT'] instanceof \ArrayAccess)) {
314 foreach ($GLOBALS['TYPO3_LOADED_EXT'] as $extensionConfiguration) {
315 $isArray = (is_array($extensionConfiguration) || $extensionConfiguration instanceof \ArrayAccess);
316 if (!$isArray || ($isArray && !isset($extensionConfiguration['ext_tables.sql']))) {
317 continue;
318 }
319 $extensionsSql = file_get_contents($extensionConfiguration['ext_tables.sql']);
320 $this->parseAndAnalyzeSql($extensionsSql);
321 }
322 }
323 }
324
325 /**
326 * Parse and analyze given SQL string
327 *
328 * @param $sql
329 * @return void
330 */
331 protected function parseAndAnalyzeSql($sql) {
332 $parsedSql = $this->installerSql->getFieldDefinitions_fileContent($sql);
333 $this->analyzeFields($parsedSql);
334 }
335
336 /**
337 * Returns all field information gathered during
338 * analyzing all tables and fields.
339 *
340 * @return array
341 */
342 protected function getCompleteFieldInformation() {
343 return array('incFields' => $this->cache_autoIncFields, 'fieldTypes' => $this->cache_fieldType, 'primaryKeys' => $this->cache_primaryKeys);
344 }
345
346 /**
347 * Creates a PHP code representation of the array that can be cached
348 * in the PHP code cache.
349 *
350 * @param array $fieldInformation
351 * @return string
352 */
353 protected function getCacheableString(array $fieldInformation) {
354 $cacheString = 'return ';
355 $cacheString .= var_export($fieldInformation, TRUE);
356 $cacheString .= ';';
357 return $cacheString;
358 }
359
360 /**
361 * Analyzes fields and adds the extracted information to the field type, auto increment and primary key info caches.
362 *
363 * @param array $parsedExtSQL The output produced by \TYPO3\CMS\Install\Service\SqlSchemaMigrationService->getFieldDefinitions_fileContent()
364 * @return void
365 */
366 protected function analyzeFields($parsedExtSQL) {
367 foreach ($parsedExtSQL as $table => $tdef) {
368 if (is_array($tdef['fields'])) {
369 foreach ($tdef['fields'] as $field => $fdefString) {
370 $fdef = $this->SQLparser->parseFieldDef($fdefString);
371 $this->cache_fieldType[$table][$field]['type'] = $fdef['fieldType'];
372 $this->cache_fieldType[$table][$field]['metaType'] = $this->MySQLMetaType($fdef['fieldType']);
373 $this->cache_fieldType[$table][$field]['notnull'] = isset($fdef['featureIndex']['NOTNULL']) && !$this->SQLparser->checkEmptyDefaultValue($fdef['featureIndex']) ? 1 : 0;
374 if (isset($fdef['featureIndex']['DEFAULT'])) {
375 $default = $fdef['featureIndex']['DEFAULT']['value'][0];
376 if (isset($fdef['featureIndex']['DEFAULT']['value'][1])) {
377 $default = $fdef['featureIndex']['DEFAULT']['value'][1] . $default . $fdef['featureIndex']['DEFAULT']['value'][1];
378 }
379 $this->cache_fieldType[$table][$field]['default'] = $default;
380 }
381 if (isset($fdef['featureIndex']['AUTO_INCREMENT'])) {
382 $this->cache_autoIncFields[$table] = $field;
383 }
384 if (isset($tdef['keys']['PRIMARY'])) {
385 $this->cache_primaryKeys[$table] = substr($tdef['keys']['PRIMARY'], 13, -1);
386 }
387 }
388 }
389 }
390 }
391
392 /**
393 * This function builds all definitions for mapped tables and fields
394 *
395 * @param array $fieldInfo
396 * @return array
397 *
398 * @see cacheFieldInfo()
399 */
400 protected function mapCachedFieldInfo(array $fieldInfo) {
401 if (is_array($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal']['mapping'])) {
402 foreach ($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal']['mapping'] as $mappedTable => $mappedConf) {
403 if (array_key_exists($mappedTable, $fieldInfo['incFields'])) {
404 $mappedTableAlias = $mappedConf['mapTableName'];
405 if (isset($mappedConf['mapFieldNames'][$fieldInfo['incFields'][$mappedTable]])) {
406 $fieldInfo['incFields'][$mappedTableAlias] = $mappedConf['mapFieldNames'][$fieldInfo['incFields'][$mappedTable]];
407 } else {
408 $fieldInfo['incFields'][$mappedTableAlias] = $fieldInfo['incFields'][$mappedTable];
409 }
410 }
411 if (array_key_exists($mappedTable, $fieldInfo['fieldTypes'])) {
412 $tempMappedFieldConf = array();
413 foreach ($fieldInfo['fieldTypes'][$mappedTable] as $field => $fieldConf) {
414 $tempMappedFieldConf[$mappedConf['mapFieldNames'][$field]] = $fieldConf;
415 }
416 $fieldInfo['fieldTypes'][$mappedConf['mapTableName']] = $tempMappedFieldConf;
417 }
418 if (array_key_exists($mappedTable, $fieldInfo['primaryKeys'])) {
419 $mappedTableAlias = $mappedConf['mapTableName'];
420 if (isset($mappedConf['mapFieldNames'][$fieldInfo['primaryKeys'][$mappedTable]])) {
421 $fieldInfo['primaryKeys'][$mappedTableAlias] = $mappedConf['mapFieldNames'][$fieldInfo['primaryKeys'][$mappedTable]];
422 } else {
423 $fieldInfo['primaryKeys'][$mappedTableAlias] = $fieldInfo['primaryKeys'][$mappedTable];
424 }
425 }
426 }
427 }
428 return $fieldInfo;
429 }
430
431 /************************************
432 *
433 * Query Building (Overriding parent methods)
434 * These functions are extending counterparts in the parent class.
435 *
436 **************************************/
437 /*
438 * From the ADOdb documentation, this is what we do (_Execute for SELECT, _query for the other actions)Execute()
439 * is the default way to run queries. You can use the low-level functions _Execute() and _query() to reduce query overhead.
440 * Both these functions share the same parameters as Execute().If you do not have any bind parameters or your database
441 * supports binding (without emulation), then you can call _Execute() directly.
442 * Calling this function bypasses bind emulation. Debugging is still supported in _Execute().If you do not require
443 * debugging facilities nor emulated binding, and do not require a recordset to be returned, then you can call _query.
444 * This is great for inserts, updates and deletes. Calling this function bypasses emulated binding, debugging,
445 * and recordset handling. Either the resultid, TRUE or FALSE are returned by _query().
446 */
447
448 /**
449 * Creates and executes an INSERT SQL-statement for $table from the array with field/value pairs $fields_values.
450 * Using this function specifically allows us to handle BLOB and CLOB fields depending on DB
451 *
452 * @param string $table Table name
453 * @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.
454 * @param bool $no_quote_fields See fullQuoteArray()
455 * @return bool|\mysqli_result|object MySQLi result object / DBAL object
456 * @throws \RuntimeException
457 */
458 public function exec_INSERTquery($table, $fields_values, $no_quote_fields = FALSE) {
459 $pt = $this->debug ? GeneralUtility::milliseconds() : 0;
460 // Do field mapping if needed:
461 $ORIG_tableName = $table;
462 if ($tableArray = $this->map_needMapping($table)) {
463 // Field mapping of array:
464 $fields_values = $this->map_assocArray($fields_values, $tableArray);
465 // Table name:
466 if ($this->mapping[$table]['mapTableName']) {
467 $table = $this->mapping[$table]['mapTableName'];
468 }
469 }
470 // Select API:
471 $this->lastHandlerKey = $this->handler_getFromTableList($table);
472 $hType = (string)$this->handlerCfg[$this->lastHandlerKey]['type'];
473 $sqlResult = NULL;
474 switch ($hType) {
475 case 'native':
476 $this->lastQuery = $this->INSERTquery($table, $fields_values, $no_quote_fields);
477 if (is_string($this->lastQuery)) {
478 $sqlResult = $this->query($this->lastQuery);
479 } else {
480 $sqlResult = $this->query($this->lastQuery[0]);
481 $new_id = $this->sql_insert_id();
482 $where = $this->cache_autoIncFields[$table] . '=' . $new_id;
483 foreach ($this->lastQuery[1] as $field => $content) {
484 $stmt = 'UPDATE ' . $this->quoteFromTables($table) . ' SET ' . $this->quoteFromTables($field) . '=' . $this->fullQuoteStr($content, $table) . ' WHERE ' . $this->quoteWhereClause($where);
485 $this->query($stmt);
486 }
487 }
488 break;
489 case 'adodb':
490 // auto generate ID for auto_increment fields if not present (static import needs this!)
491 // should we check the table name here (static_*)?
492 if (isset($this->cache_autoIncFields[$table])) {
493 if (isset($fields_values[$this->cache_autoIncFields[$table]])) {
494 $new_id = $fields_values[$this->cache_autoIncFields[$table]];
495 if ($table != 'tx_dbal_debuglog') {
496 $this->handlerInstance[$this->lastHandlerKey]->last_insert_id = $new_id;
497 }
498 } else {
499 $new_id = $this->handlerInstance[$this->lastHandlerKey]->GenID($table . '_' . $this->cache_autoIncFields[$table], $this->handlerInstance[$this->lastHandlerKey]->sequenceStart);
500 $fields_values[$this->cache_autoIncFields[$table]] = $new_id;
501 if ($table != 'tx_dbal_debuglog') {
502 $this->handlerInstance[$this->lastHandlerKey]->last_insert_id = $new_id;
503 }
504 }
505 }
506 $this->lastQuery = $this->INSERTquery($table, $fields_values, $no_quote_fields);
507 if (is_string($this->lastQuery)) {
508 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_query($this->lastQuery, FALSE);
509 } else {
510 $this->handlerInstance[$this->lastHandlerKey]->StartTrans();
511 if (strlen($this->lastQuery[0])) {
512 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_query($this->lastQuery[0], FALSE);
513 }
514 if (is_array($this->lastQuery[1])) {
515 foreach ($this->lastQuery[1] as $field => $content) {
516 if (empty($content)) {
517 continue;
518 }
519 if (isset($this->cache_autoIncFields[$table]) && isset($new_id)) {
520 $this->handlerInstance[$this->lastHandlerKey]->UpdateBlob($this->quoteFromTables($table), $field, $content, $this->quoteWhereClause($this->cache_autoIncFields[$table] . '=' . $new_id));
521 } elseif (isset($this->cache_primaryKeys[$table])) {
522 $where = '';
523 $pks = explode(',', $this->cache_primaryKeys[$table]);
524 foreach ($pks as $pk) {
525 if (isset($fields_values[$pk])) {
526 $where .= $pk . '=' . $this->fullQuoteStr($fields_values[$pk], $table) . ' AND ';
527 }
528 }
529 $where = $this->quoteWhereClause($where . '1=1');
530 $this->handlerInstance[$this->lastHandlerKey]->UpdateBlob($this->quoteFromTables($table), $field, $content, $where);
531 } else {
532 $this->handlerInstance[$this->lastHandlerKey]->CompleteTrans(FALSE);
533 // Should never ever happen
534 throw new \RuntimeException('Could not update BLOB >>>> no WHERE clause found!', 1321860519);
535 }
536 }
537 }
538 if (is_array($this->lastQuery[2])) {
539 foreach ($this->lastQuery[2] as $field => $content) {
540 if (empty($content)) {
541 continue;
542 }
543 if (isset($this->cache_autoIncFields[$table]) && isset($new_id)) {
544 $this->handlerInstance[$this->lastHandlerKey]->UpdateClob($this->quoteFromTables($table), $field, $content, $this->quoteWhereClause($this->cache_autoIncFields[$table] . '=' . $new_id));
545 } elseif (isset($this->cache_primaryKeys[$table])) {
546 $where = '';
547 $pks = explode(',', $this->cache_primaryKeys[$table]);
548 foreach ($pks as $pk) {
549 if (isset($fields_values[$pk])) {
550 $where .= $pk . '=' . $this->fullQuoteStr($fields_values[$pk], $table) . ' AND ';
551 }
552 }
553 $where = $this->quoteWhereClause($where . '1=1');
554 $this->handlerInstance[$this->lastHandlerKey]->UpdateClob($this->quoteFromTables($table), $field, $content, $where);
555 } else {
556 $this->handlerInstance[$this->lastHandlerKey]->CompleteTrans(FALSE);
557 // Should never ever happen
558 throw new \RuntimeException('Could not update CLOB >>>> no WHERE clause found!', 1310027337);
559 }
560 }
561 }
562 $this->handlerInstance[$this->lastHandlerKey]->CompleteTrans();
563 }
564 break;
565 case 'userdefined':
566 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->exec_INSERTquery($table, $fields_values, $no_quote_fields);
567 break;
568 }
569 if ($this->printErrors && $this->sql_error()) {
570 debug(array($this->lastQuery, $this->sql_error()));
571 }
572 if ($this->debug) {
573 $this->debugHandler('exec_INSERTquery', GeneralUtility::milliseconds() - $pt, array(
574 'handlerType' => $hType,
575 'args' => array($table, $fields_values),
576 'ORIG_tablename' => $ORIG_tableName
577 ));
578 }
579 foreach ($this->postProcessHookObjects as $hookObject) {
580 $hookObject->exec_INSERTquery_postProcessAction($table, $fields_values, $no_quote_fields, $this);
581 }
582 // Return output:
583 return $sqlResult;
584 }
585
586 /**
587 * Creates and executes an INSERT SQL-statement for $table with multiple rows.
588 *
589 * @param string $table Table name
590 * @param array $fields Field names
591 * @param array $rows Table rows. Each row should be an array with field values mapping to $fields
592 * @param bool $no_quote_fields See fullQuoteArray()
593 * @return bool|\mysqli_result|object MySQLi result object / DBAL object
594 */
595 public function exec_INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = FALSE) {
596 $res = NULL;
597 if ((string)$this->handlerCfg[$this->lastHandlerKey]['type'] === 'native') {
598 $this->lastHandlerKey = $this->handler_getFromTableList($table);
599 $res = $this->query(parent::INSERTmultipleRows($table, $fields, $rows, $no_quote_fields));
600 } else {
601 foreach ($rows as $row) {
602 $fields_values = array();
603 foreach ($fields as $key => $value) {
604 $fields_values[$value] = $row[$key];
605 }
606 $res = $this->exec_INSERTquery($table, $fields_values, $no_quote_fields);
607 }
608 }
609 foreach ($this->postProcessHookObjects as $hookObject) {
610 $hookObject->exec_INSERTmultipleRows_postProcessAction($table, $fields, $rows, $no_quote_fields, $this);
611 }
612 return $res;
613 }
614
615 /**
616 * Creates and executes an UPDATE SQL-statement for $table where $where-clause (typ. 'uid=...') from the array with field/value pairs $fields_values.
617 * Using this function specifically allow us to handle BLOB and CLOB fields depending on DB
618 *
619 * @param string $table Database tablename
620 * @param string $where WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
621 * @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.
622 * @param bool $no_quote_fields See fullQuoteArray()
623 * @return boolean|\mysqli_result|object MySQLi result object / DBAL object
624 */
625 public function exec_UPDATEquery($table, $where, $fields_values, $no_quote_fields = FALSE) {
626 $pt = $this->debug ? GeneralUtility::milliseconds() : 0;
627 // Do table/field mapping:
628 $ORIG_tableName = $table;
629 if ($tableArray = $this->map_needMapping($table)) {
630 // Field mapping of array:
631 $fields_values = $this->map_assocArray($fields_values, $tableArray);
632 // Where clause table and field mapping:
633 $whereParts = $this->SQLparser->parseWhereClause($where);
634 $this->map_sqlParts($whereParts, $tableArray[0]['table']);
635 $where = $this->SQLparser->compileWhereClause($whereParts, FALSE);
636 // Table name:
637 if ($this->mapping[$table]['mapTableName']) {
638 $table = $this->mapping[$table]['mapTableName'];
639 }
640 }
641 // Select API
642 $this->lastHandlerKey = $this->handler_getFromTableList($table);
643 $hType = (string) $this->handlerCfg[$this->lastHandlerKey]['type'];
644 $sqlResult = NULL;
645 switch ($hType) {
646 case 'native':
647 $this->lastQuery = $this->UPDATEquery($table, $where, $fields_values, $no_quote_fields);
648 if (is_string($this->lastQuery)) {
649 $sqlResult = $this->query($this->lastQuery);
650 } else {
651 $sqlResult = $this->query($this->lastQuery[0]);
652 foreach ($this->lastQuery[1] as $field => $content) {
653 $stmt = 'UPDATE ' . $this->quoteFromTables($table) . ' SET ' . $this->quoteFromTables($field) . '=' . $this->fullQuoteStr($content, $table) . ' WHERE ' . $this->quoteWhereClause($where);
654 $this->query($stmt);
655 }
656 }
657 break;
658 case 'adodb':
659 $this->lastQuery = $this->UPDATEquery($table, $where, $fields_values, $no_quote_fields);
660 if (is_string($this->lastQuery)) {
661 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_query($this->lastQuery, FALSE);
662 } else {
663 $this->handlerInstance[$this->lastHandlerKey]->StartTrans();
664 if (strlen($this->lastQuery[0])) {
665 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_query($this->lastQuery[0], FALSE);
666 }
667 if (is_array($this->lastQuery[1])) {
668 foreach ($this->lastQuery[1] as $field => $content) {
669 $this->handlerInstance[$this->lastHandlerKey]->UpdateBlob($this->quoteFromTables($table), $field, $content, $this->quoteWhereClause($where));
670 }
671 }
672 if (is_array($this->lastQuery[2])) {
673 foreach ($this->lastQuery[2] as $field => $content) {
674 $this->handlerInstance[$this->lastHandlerKey]->UpdateClob($this->quoteFromTables($table), $field, $content, $this->quoteWhereClause($where));
675 }
676 }
677 $this->handlerInstance[$this->lastHandlerKey]->CompleteTrans();
678 }
679 break;
680 case 'userdefined':
681 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->exec_UPDATEquery($table, $where, $fields_values, $no_quote_fields);
682 break;
683 }
684 if ($this->printErrors && $this->sql_error()) {
685 debug(array($this->lastQuery, $this->sql_error()));
686 }
687 if ($this->debug) {
688 $this->debugHandler('exec_UPDATEquery', GeneralUtility::milliseconds() - $pt, array(
689 'handlerType' => $hType,
690 'args' => array($table, $where, $fields_values),
691 'ORIG_from_table' => $ORIG_tableName
692 ));
693 }
694 foreach ($this->postProcessHookObjects as $hookObject) {
695 $hookObject->exec_UPDATEquery_postProcessAction($table, $where, $fields_values, $no_quote_fields, $this);
696 }
697 // Return result:
698 return $sqlResult;
699 }
700
701 /**
702 * Creates and executes a DELETE SQL-statement for $table where $where-clause
703 *
704 * @param string $table Database tablename
705 * @param string $where WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
706 * @return boolean|\mysqli_result|object MySQLi result object / DBAL object
707 */
708 public function exec_DELETEquery($table, $where) {
709 $pt = $this->debug ? GeneralUtility::milliseconds() : 0;
710 // Do table/field mapping:
711 $ORIG_tableName = $table;
712 if ($tableArray = $this->map_needMapping($table)) {
713 // Where clause:
714 $whereParts = $this->SQLparser->parseWhereClause($where);
715 $this->map_sqlParts($whereParts, $tableArray[0]['table']);
716 $where = $this->SQLparser->compileWhereClause($whereParts, FALSE);
717 // Table name:
718 if ($this->mapping[$table]['mapTableName']) {
719 $table = $this->mapping[$table]['mapTableName'];
720 }
721 }
722 // Select API
723 $this->lastHandlerKey = $this->handler_getFromTableList($table);
724 $hType = (string) $this->handlerCfg[$this->lastHandlerKey]['type'];
725 $sqlResult = NULL;
726 switch ($hType) {
727 case 'native':
728 $this->lastQuery = $this->DELETEquery($table, $where);
729 $sqlResult = $this->query($this->lastQuery);
730 break;
731 case 'adodb':
732 $this->lastQuery = $this->DELETEquery($table, $where);
733 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_query($this->lastQuery, FALSE);
734 break;
735 case 'userdefined':
736 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->exec_DELETEquery($table, $where);
737 break;
738 }
739 if ($this->printErrors && $this->sql_error()) {
740 debug(array($this->lastQuery, $this->sql_error()));
741 }
742 if ($this->debug) {
743 $this->debugHandler('exec_DELETEquery', GeneralUtility::milliseconds() - $pt, array(
744 'handlerType' => $hType,
745 'args' => array($table, $where),
746 'ORIG_from_table' => $ORIG_tableName
747 ));
748 }
749 foreach ($this->postProcessHookObjects as $hookObject) {
750 $hookObject->exec_DELETEquery_postProcessAction($table, $where, $this);
751 }
752 // Return result:
753 return $sqlResult;
754 }
755
756 /**
757 * Creates and executes a SELECT SQL-statement
758 * Using this function specifically allow us to handle the LIMIT feature independently of DB.
759 *
760 * @param string $select_fields List of fields to select from the table. This is what comes right after "SELECT ...". Required value.
761 * @param string $from_table Table(s) from which to select. This is what comes right after "FROM ...". Required value.
762 * @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!
763 * @param string $groupBy Optional GROUP BY field(s), if none, supply blank string.
764 * @param string $orderBy Optional ORDER BY field(s), if none, supply blank string.
765 * @param string $limit Optional LIMIT value ([begin,]max), if none, supply blank string.
766 * @return boolean|\mysqli_result|object MySQLi result object / DBAL object
767 */
768 public function exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '') {
769 $pt = $this->debug ? GeneralUtility::milliseconds() : 0;
770 // Map table / field names if needed:
771 $ORIG_tableName = $from_table;
772 // Saving table names in $ORIG_from_table since $from_table is transformed beneath:
773 $parsedFromTable = array();
774 $remappedParameters = array();
775 if ($tableArray = $this->map_needMapping($ORIG_tableName, FALSE, $parsedFromTable)) {
776 $from = $parsedFromTable ? $parsedFromTable : $from_table;
777 $remappedParameters = $this->map_remapSELECTQueryParts($select_fields, $from, $where_clause, $groupBy, $orderBy);
778 }
779 // Get handler key and select API:
780 if (count($remappedParameters) > 0) {
781 $mappedQueryParts = $this->compileSelectParameters($remappedParameters);
782 $fromTable = $mappedQueryParts[1];
783 } else {
784 $fromTable = $from_table;
785 }
786 $this->lastHandlerKey = $this->handler_getFromTableList($fromTable);
787 $hType = (string) $this->handlerCfg[$this->lastHandlerKey]['type'];
788 $sqlResult = NULL;
789 switch ($hType) {
790 case 'native':
791 if (count($remappedParameters) > 0) {
792 list($select_fields, $from_table, $where_clause, $groupBy, $orderBy) = $this->compileSelectParameters($remappedParameters);
793 }
794 $this->lastQuery = $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
795 $sqlResult = $this->query($this->lastQuery);
796 $this->resourceIdToTableNameMap[serialize($sqlResult)] = $ORIG_tableName;
797 break;
798 case 'adodb':
799 if ($limit != '') {
800 $splitLimit = GeneralUtility::intExplode(',', $limit);
801 // Splitting the limit values:
802 if ($splitLimit[1]) {
803 // If there are two parameters, do mapping differently than otherwise:
804 $numrows = $splitLimit[1];
805 $offset = $splitLimit[0];
806 } else {
807 $numrows = $splitLimit[0];
808 $offset = 0;
809 }
810 if (count($remappedParameters) > 0) {
811 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->SelectLimit($this->SELECTqueryFromArray($remappedParameters), $numrows, $offset);
812 } else {
813 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->SelectLimit($this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy), $numrows, $offset);
814 }
815 $this->lastQuery = $sqlResult->sql;
816 } else {
817 if (count($remappedParameters) > 0) {
818 $this->lastQuery = $this->SELECTqueryFromArray($remappedParameters);
819 } else {
820 $this->lastQuery = $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy);
821 }
822 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_Execute($this->lastQuery);
823 }
824 $sqlResult->TYPO3_DBAL_handlerType = 'adodb';
825 // Setting handler type in result object (for later recognition!)
826 $sqlResult->TYPO3_DBAL_tableList = $ORIG_tableName;
827 break;
828 case 'userdefined':
829 if (count($remappedParameters) > 0) {
830 list($select_fields, $from_table, $where_clause, $groupBy, $orderBy) = $this->compileSelectParameters($remappedParameters);
831 }
832 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
833 if (is_object($sqlResult)) {
834 $sqlResult->TYPO3_DBAL_handlerType = 'userdefined';
835 // Setting handler type in result object (for later recognition!)
836 $sqlResult->TYPO3_DBAL_tableList = $ORIG_tableName;
837 }
838 break;
839 }
840 if ($this->printErrors && $this->sql_error()) {
841 debug(array($this->lastQuery, $this->sql_error()));
842 }
843 if ($this->debug) {
844 $data = array(
845 'handlerType' => $hType,
846 'args' => array($from_table, $select_fields, $where_clause, $groupBy, $orderBy, $limit),
847 'ORIG_from_table' => $ORIG_tableName
848 );
849 if ($this->conf['debugOptions']['numberRows']) {
850 $data['numberRows'] = $this->sql_num_rows($sqlResult);
851 }
852 $this->debugHandler('exec_SELECTquery', GeneralUtility::milliseconds() - $pt, $data);
853 }
854 // Return result handler.
855 return $sqlResult;
856 }
857
858 /**
859 * Truncates a table.
860 *
861 * @param string $table Database tablename
862 * @return mixed Result from handler
863 */
864 public function exec_TRUNCATEquery($table) {
865 $pt = $this->debug ? GeneralUtility::milliseconds() : 0;
866 // Do table/field mapping:
867 $ORIG_tableName = $table;
868 if ($tableArray = $this->map_needMapping($table)) {
869 // Table name:
870 if ($this->mapping[$table]['mapTableName']) {
871 $table = $this->mapping[$table]['mapTableName'];
872 }
873 }
874 // Select API
875 $this->lastHandlerKey = $this->handler_getFromTableList($table);
876 $hType = (string) $this->handlerCfg[$this->lastHandlerKey]['type'];
877 $sqlResult = NULL;
878 switch ($hType) {
879 case 'native':
880 $this->lastQuery = $this->TRUNCATEquery($table);
881 $sqlResult = $this->query($this->lastQuery);
882 break;
883 case 'adodb':
884 $this->lastQuery = $this->TRUNCATEquery($table);
885 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_query($this->lastQuery, FALSE);
886 break;
887 case 'userdefined':
888 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->exec_TRUNCATEquery($table);
889 break;
890 }
891 if ($this->printErrors && $this->sql_error()) {
892 debug(array($this->lastQuery, $this->sql_error()));
893 }
894 if ($this->debug) {
895 $this->debugHandler('exec_TRUNCATEquery', GeneralUtility::milliseconds() - $pt, array(
896 'handlerType' => $hType,
897 'args' => array($table),
898 'ORIG_from_table' => $ORIG_tableName
899 ));
900 }
901 foreach ($this->postProcessHookObjects as $hookObject) {
902 $hookObject->exec_TRUNCATEquery_postProcessAction($table, $this);
903 }
904 // Return result:
905 return $sqlResult;
906 }
907
908 /**
909 * Executes a query.
910 * EXPERIMENTAL since TYPO3 4.4.
911 *
912 * @param array $queryParts SQL parsed by method parseSQL() of \TYPO3\CMS\Core\Database\SqlParser
913 * @return \mysqli_result|object MySQLi result object / DBAL object
914 * @see self::sql_query()
915 */
916 protected function exec_query(array $queryParts) {
917 switch ($queryParts['type']) {
918 case 'SELECT':
919 $selectFields = $this->SQLparser->compileFieldList($queryParts['SELECT']);
920 $fromTables = $this->SQLparser->compileFromTables($queryParts['FROM']);
921 $whereClause = isset($queryParts['WHERE']) ? $this->SQLparser->compileWhereClause($queryParts['WHERE']) : '1=1';
922 $groupBy = isset($queryParts['GROUPBY']) ? $this->SQLparser->compileFieldList($queryParts['GROUPBY']) : '';
923 $orderBy = isset($queryParts['ORDERBY']) ? $this->SQLparser->compileFieldList($queryParts['ORDERBY']) : '';
924 $limit = isset($queryParts['LIMIT']) ? $queryParts['LIMIT'] : '';
925 return $this->exec_SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy, $limit);
926 case 'UPDATE':
927 $table = $queryParts['TABLE'];
928 $fields = array();
929 foreach ($queryParts['FIELDS'] as $fN => $fV) {
930 $fields[$fN] = $fV[0];
931 }
932 $whereClause = isset($queryParts['WHERE']) ? $this->SQLparser->compileWhereClause($queryParts['WHERE']) : '1=1';
933 return $this->exec_UPDATEquery($table, $whereClause, $fields);
934 case 'INSERT':
935 $table = $queryParts['TABLE'];
936 $values = array();
937 if (isset($queryParts['VALUES_ONLY']) && is_array($queryParts['VALUES_ONLY'])) {
938 $fields = $GLOBALS['TYPO3_DB']->cache_fieldType[$table];
939 $fc = 0;
940 foreach ($fields as $fn => $fd) {
941 $values[$fn] = $queryParts['VALUES_ONLY'][$fc++][0];
942 }
943 } else {
944 foreach ($queryParts['FIELDS'] as $fN => $fV) {
945 $values[$fN] = $fV[0];
946 }
947 }
948 return $this->exec_INSERTquery($table, $values);
949 case 'DELETE':
950 $table = $queryParts['TABLE'];
951 $whereClause = isset($queryParts['WHERE']) ? $this->SQLparser->compileWhereClause($queryParts['WHERE']) : '1=1';
952 return $this->exec_DELETEquery($table, $whereClause);
953 case 'TRUNCATETABLE':
954 $table = $queryParts['TABLE'];
955 return $this->exec_TRUNCATEquery($table);
956 default:
957 return NULL;
958 }
959 }
960
961 /**
962 * Central query method. Also checks if there is a database connection.
963 * Use this to execute database queries instead of directly calling $this->link->query()
964 *
965 * @param string $query The query to send to the database
966 * @return bool|\mysqli_result
967 */
968 protected function query($query) {
969 if (!$this->isConnected()) {
970 $this->connectDB();
971 }
972 return $this->handlerInstance[$this->lastHandlerKey]['link']->query($query);
973 }
974
975 /**************************************
976 *
977 * Query building
978 *
979 **************************************/
980 /**
981 * Creates an INSERT SQL-statement for $table from the array with field/value pairs $fields_values.
982 *
983 * @param string $table See exec_INSERTquery()
984 * @param array $fields_values See exec_INSERTquery()
985 * @param bool $no_quote_fields See fullQuoteArray()
986 * @return string|NULL Full SQL query for INSERT, NULL if $rows is empty
987 */
988 public function INSERTquery($table, $fields_values, $no_quote_fields = FALSE) {
989 // Table and fieldnames should be "SQL-injection-safe" when supplied to this function (contrary to values in the arrays which may be insecure).
990 if (!is_array($fields_values) || count($fields_values) === 0) {
991 return '';
992 }
993 foreach ($this->preProcessHookObjects as $hookObject) {
994 $hookObject->INSERTquery_preProcessAction($table, $fields_values, $no_quote_fields, $this);
995 }
996 if (is_string($no_quote_fields)) {
997 $no_quote_fields = explode(',', $no_quote_fields);
998 } elseif (!is_array($no_quote_fields)) {
999 $no_quote_fields = array();
1000 }
1001 $blobFields = $clobFields = array();
1002 $nArr = array();
1003 $handlerKey = $this->handler_getFromTableList($table);
1004 $quoteClob = isset($this->handlerCfg[$handlerKey]['config']['quoteClob']) ? $this->handlerCfg[$handlerKey]['config']['quoteClob'] : FALSE;
1005 foreach ($fields_values as $k => $v) {
1006 if (!$this->runningNative() && $this->sql_field_metatype($table, $k) == 'B') {
1007 // we skip the field in the regular INSERT statement, it is only in blobfields
1008 $blobFields[$this->quoteFieldNames($k)] = $v;
1009 } elseif (!$this->runningNative() && $this->sql_field_metatype($table, $k) == 'XL') {
1010 // we skip the field in the regular INSERT statement, it is only in clobfields
1011 $clobFields[$this->quoteFieldNames($k)] = $quoteClob ? $this->quoteStr($v, $table) : $v;
1012 } else {
1013 // Add slashes old-school:
1014 // cast numerical values
1015 $mt = $this->sql_field_metatype($table, $k);
1016 if ($mt[0] == 'I') {
1017 $v = (int)$v;
1018 } elseif ($mt[0] == 'F') {
1019 $v = (double) $v;
1020 }
1021 $nArr[$this->quoteFieldNames($k)] = !in_array($k, $no_quote_fields) ? $this->fullQuoteStr($v, $table) : $v;
1022 }
1023 }
1024 if (count($blobFields) || count($clobFields)) {
1025 $query = array();
1026 if (count($nArr)) {
1027 $query[0] = 'INSERT INTO ' . $this->quoteFromTables($table) . '
1028 (
1029 ' . implode(',
1030 ', array_keys($nArr)) . '
1031 ) VALUES (
1032 ' . implode(',
1033 ', $nArr) . '
1034 )';
1035 }
1036 if (count($blobFields)) {
1037 $query[1] = $blobFields;
1038 }
1039 if (count($clobFields)) {
1040 $query[2] = $clobFields;
1041 }
1042 if (isset($query[0]) && ($this->debugOutput || $this->store_lastBuiltQuery)) {
1043 $this->debug_lastBuiltQuery = $query[0];
1044 }
1045 } else {
1046 $query = 'INSERT INTO ' . $this->quoteFromTables($table) . '
1047 (
1048 ' . implode(',
1049 ', array_keys($nArr)) . '
1050 ) VALUES (
1051 ' . implode(',
1052 ', $nArr) . '
1053 )';
1054 if ($this->debugOutput || $this->store_lastBuiltQuery) {
1055 $this->debug_lastBuiltQuery = $query;
1056 }
1057 }
1058 return $query;
1059 }
1060
1061 /**
1062 * Creates an INSERT SQL-statement for $table with multiple rows.
1063 *
1064 * @param string $table Table name
1065 * @param array $fields Field names
1066 * @param array $rows Table rows. Each row should be an array with field values mapping to $fields
1067 * @param bool $no_quote_fields See fullQuoteArray()
1068 * @return string|array Full SQL query for INSERT (unless $rows does not contain any elements in which case it will be FALSE)
1069 */
1070 public function INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = FALSE) {
1071 if ((string) $this->handlerCfg[$this->lastHandlerKey]['type'] === 'native') {
1072 return parent::INSERTmultipleRows($table, $fields, $rows, $no_quote_fields);
1073 }
1074 $result = array();
1075 foreach ($rows as $row) {
1076 $fields_values = array();
1077 foreach ($fields as $key => $value) {
1078 $fields_values[$value] = $row[$key];
1079 }
1080 $rowQuery = $this->INSERTquery($table, $fields_values, $no_quote_fields);
1081 if (is_array($rowQuery)) {
1082 $result[] = $rowQuery;
1083 } else {
1084 $result[][0] = $rowQuery;
1085 }
1086 }
1087 return $result;
1088 }
1089
1090 /**
1091 * Creates an UPDATE SQL-statement for $table where $where-clause (typ. 'uid=...') from the array with field/value pairs $fields_values.
1092 *
1093 *
1094 * @param string $table See exec_UPDATEquery()
1095 * @param string $where See exec_UPDATEquery()
1096 * @param array $fields_values See exec_UPDATEquery()
1097 * @param bool $no_quote_fields
1098 * @throws \InvalidArgumentException
1099 * @return string Full SQL query for UPDATE
1100 */
1101 public function UPDATEquery($table, $where, $fields_values, $no_quote_fields = FALSE) {
1102 // Table and fieldnames should be "SQL-injection-safe" when supplied to this function (contrary to values in the arrays which may be insecure).
1103 if (is_string($where)) {
1104 foreach ($this->preProcessHookObjects as $hookObject) {
1105 $hookObject->UPDATEquery_preProcessAction($table, $where, $fields_values, $no_quote_fields, $this);
1106 }
1107 $blobFields = $clobFields = array();
1108 $nArr = array();
1109 if (is_array($fields_values) && count($fields_values)) {
1110 if (is_string($no_quote_fields)) {
1111 $no_quote_fields = explode(',', $no_quote_fields);
1112 } elseif (!is_array($no_quote_fields)) {
1113 $no_quote_fields = array();
1114 }
1115 $handlerKey = $this->handler_getFromTableList($table);
1116 $quoteClob = isset($this->handlerCfg[$handlerKey]['config']['quoteClob']) ? $this->handlerCfg[$handlerKey]['config']['quoteClob'] : FALSE;
1117 foreach ($fields_values as $k => $v) {
1118 if (!$this->runningNative() && $this->sql_field_metatype($table, $k) == 'B') {
1119 // we skip the field in the regular UPDATE statement, it is only in blobfields
1120 $blobFields[$this->quoteFieldNames($k)] = $v;
1121 } elseif (!$this->runningNative() && $this->sql_field_metatype($table, $k) == 'XL') {
1122 // we skip the field in the regular UPDATE statement, it is only in clobfields
1123 $clobFields[$this->quoteFieldNames($k)] = $quoteClob ? $this->quoteStr($v, $table) : $v;
1124 } else {
1125 // Add slashes old-school:
1126 // cast numeric values
1127 $mt = $this->sql_field_metatype($table, $k);
1128 if ($mt[0] == 'I') {
1129 $v = (int)$v;
1130 } elseif ($mt[0] == 'F') {
1131 $v = (double) $v;
1132 }
1133 $nArr[] = $this->quoteFieldNames($k) . '=' . (!in_array($k, $no_quote_fields) ? $this->fullQuoteStr($v, $table) : $v);
1134 }
1135 }
1136 }
1137 if (count($blobFields) || count($clobFields)) {
1138 $query = array();
1139 if (count($nArr)) {
1140 $query[0] = 'UPDATE ' . $this->quoteFromTables($table) . '
1141 SET
1142 ' . implode(',
1143 ', $nArr) . (strlen($where) > 0 ? '
1144 WHERE
1145 ' . $this->quoteWhereClause($where) : '');
1146 }
1147 if (count($blobFields)) {
1148 $query[1] = $blobFields;
1149 }
1150 if (count($clobFields)) {
1151 $query[2] = $clobFields;
1152 }
1153 if (isset($query[0]) && ($this->debugOutput || $this->store_lastBuiltQuery)) {
1154 $this->debug_lastBuiltQuery = $query[0];
1155 }
1156 } else {
1157 $query = 'UPDATE ' . $this->quoteFromTables($table) . '
1158 SET
1159 ' . implode(',
1160 ', $nArr) . (strlen($where) > 0 ? '
1161 WHERE
1162 ' . $this->quoteWhereClause($where) : '');
1163 if ($this->debugOutput || $this->store_lastBuiltQuery) {
1164 $this->debug_lastBuiltQuery = $query;
1165 }
1166 }
1167 return $query;
1168 } else {
1169 throw new \InvalidArgumentException('TYPO3 Fatal Error: "Where" clause argument for UPDATE query was not a string in $this->UPDATEquery() !', 1270853887);
1170 }
1171 }
1172
1173 /**
1174 * Creates a DELETE SQL-statement for $table where $where-clause
1175 *
1176 * @param string $table See exec_DELETEquery()
1177 * @param string $where See exec_DELETEquery()
1178 * @return string Full SQL query for DELETE
1179 * @throws \InvalidArgumentException
1180 */
1181 public function DELETEquery($table, $where) {
1182 if (is_string($where)) {
1183 foreach ($this->preProcessHookObjects as $hookObject) {
1184 $hookObject->DELETEquery_preProcessAction($table, $where, $this);
1185 }
1186 $table = $this->quoteFromTables($table);
1187 $where = $this->quoteWhereClause($where);
1188 $query = 'DELETE FROM ' . $table . (strlen($where) > 0 ? ' WHERE ' . $where : '');
1189 if ($this->debugOutput || $this->store_lastBuiltQuery) {
1190 $this->debug_lastBuiltQuery = $query;
1191 }
1192 return $query;
1193 } else {
1194 throw new \InvalidArgumentException('TYPO3 Fatal Error: "Where" clause argument for DELETE query was not a string in $this->DELETEquery() !', 1310027383);
1195 }
1196 }
1197
1198 /**
1199 * Creates a SELECT SQL-statement
1200 *
1201 * @param string $select_fields See exec_SELECTquery()
1202 * @param string $from_table See exec_SELECTquery()
1203 * @param string $where_clause See exec_SELECTquery()
1204 * @param string $groupBy See exec_SELECTquery()
1205 * @param string $orderBy See exec_SELECTquery()
1206 * @param string $limit See exec_SELECTquery()
1207 * @return string Full SQL query for SELECT
1208 */
1209 public function SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '') {
1210 $this->lastHandlerKey = $this->handler_getFromTableList($from_table);
1211 $hType = (string) $this->handlerCfg[$this->lastHandlerKey]['type'];
1212 if ($hType === 'adodb' && $this->runningADOdbDriver('postgres')) {
1213 // Possibly rewrite the LIMIT to be PostgreSQL-compatible
1214 $splitLimit = GeneralUtility::intExplode(',', $limit);
1215 // Splitting the limit values:
1216 if ($splitLimit[1]) {
1217 // If there are two parameters, do mapping differently than otherwise:
1218 $numrows = $splitLimit[1];
1219 $offset = $splitLimit[0];
1220 $limit = $numrows . ' OFFSET ' . $offset;
1221 }
1222 }
1223 $select_fields = $this->quoteFieldNames($select_fields);
1224 $from_table = $this->quoteFromTables($from_table);
1225 $where_clause = $this->quoteWhereClause($where_clause);
1226 $groupBy = $this->quoteGroupBy($groupBy);
1227 $orderBy = $this->quoteOrderBy($orderBy);
1228 // Call parent method to build actual query
1229 $query = parent::SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
1230 if ($this->debugOutput || $this->store_lastBuiltQuery) {
1231 $this->debug_lastBuiltQuery = $query;
1232 }
1233 return $query;
1234 }
1235
1236 /**
1237 * Creates a SELECT SQL-statement to be used with an ADOdb backend.
1238 *
1239 * @param array $params parsed parameters: array($select_fields, $from_table, $where_clause, $groupBy, $orderBy)
1240 * @return string Full SQL query for SELECT
1241 */
1242 protected function SELECTqueryFromArray(array $params) {
1243 // $select_fields
1244 $params[0] = $this->_quoteFieldNames($params[0]);
1245 // $from_table
1246 $params[1] = $this->_quoteFromTables($params[1]);
1247 // $where_clause
1248 if (count($params[2]) > 0) {
1249 $params[2] = $this->_quoteWhereClause($params[2]);
1250 }
1251 // $group_by
1252 if (count($params[3]) > 0) {
1253 $params[3] = $this->_quoteGroupBy($params[3]);
1254 }
1255 // $order_by
1256 if (count($params[4]) > 0) {
1257 $params[4] = $this->_quoteOrderBy($params[4]);
1258 }
1259 // Compile the SELECT parameters
1260 list($select_fields, $from_table, $where_clause, $groupBy, $orderBy) = $this->compileSelectParameters($params);
1261 // Call parent method to build actual query
1262 $query = parent::SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy);
1263 if ($this->debugOutput || $this->store_lastBuiltQuery) {
1264 $this->debug_lastBuiltQuery = $query;
1265 }
1266 return $query;
1267 }
1268
1269 /**
1270 * Compiles and returns an array of SELECTquery parameters (without $limit) to
1271 * be used with SELECTquery() or exec_SELECTquery().
1272 *
1273 * @param array $params
1274 * @return array array($select_fields, $from_table, $where_clause, $groupBy, $orderBy)
1275 */
1276 protected function compileSelectParameters(array $params) {
1277 $select_fields = $this->SQLparser->compileFieldList($params[0]);
1278 $from_table = $this->SQLparser->compileFromTables($params[1]);
1279 $where_clause = count($params[2]) > 0 ? $this->SQLparser->compileWhereClause($params[2]) : '';
1280 $groupBy = count($params[3]) > 0 ? $this->SQLparser->compileFieldList($params[3]) : '';
1281 $orderBy = count($params[4]) > 0 ? $this->SQLparser->compileFieldList($params[4]) : '';
1282 return array($select_fields, $from_table, $where_clause, $groupBy, $orderBy);
1283 }
1284
1285 /**
1286 * Creates a TRUNCATE TABLE SQL-statement
1287 *
1288 * @param string $table See exec_TRUNCATEquery()
1289 * @return string Full SQL query for TRUNCATE TABLE
1290 */
1291 public function TRUNCATEquery($table) {
1292 foreach ($this->preProcessHookObjects as $hookObject) {
1293 $hookObject->TRUNCATEquery_preProcessAction($table, $this);
1294 }
1295 $table = $this->quoteFromTables($table);
1296 // Build actual query
1297 $query = 'TRUNCATE TABLE ' . $table;
1298 if ($this->debugOutput || $this->store_lastBuiltQuery) {
1299 $this->debug_lastBuiltQuery = $query;
1300 }
1301 return $query;
1302 }
1303
1304 /**************************************
1305 *
1306 * Prepared Query Support
1307 *
1308 **************************************/
1309 /**
1310 * Creates a SELECT prepared SQL statement.
1311 *
1312 * @param string $select_fields See exec_SELECTquery()
1313 * @param string $from_table See exec_SELECTquery()
1314 * @param string $where_clause See exec_SELECTquery()
1315 * @param string $groupBy See exec_SELECTquery()
1316 * @param string $orderBy See exec_SELECTquery()
1317 * @param string $limit See exec_SELECTquery()
1318 * @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 \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_AUTOTYPE.
1319 * @return \TYPO3\CMS\Core\Database\PreparedStatement Prepared statement
1320 */
1321 public function prepare_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', array $input_parameters = array()) {
1322 $pt = $this->debug ? GeneralUtility::milliseconds() : 0;
1323 $precompiledParts = array();
1324 if ($this->queryCache) {
1325 $cacheKey = 'prepare_SELECTquery-' . \TYPO3\CMS\Dbal\QueryCache::getCacheKey(array(
1326 'selectFields' => $select_fields,
1327 'fromTable' => $from_table,
1328 'whereClause' => $where_clause,
1329 'groupBy' => $groupBy,
1330 'orderBy' => $orderBy,
1331 'limit' => $limit
1332 ));
1333 if ($this->queryCache->has($cacheKey)) {
1334 $precompiledParts = $this->queryCache->get($cacheKey);
1335 if ($this->debug) {
1336 $data = array(
1337 'args' => array($from_table, $select_fields, $where_clause, $groupBy, $orderBy, $limit, $input_parameters),
1338 'precompiledParts' => $precompiledParts
1339 );
1340 $this->debugHandler('prepare_SELECTquery (cache hit)', GeneralUtility::milliseconds() - $pt, $data);
1341 }
1342 }
1343 }
1344 $ORIG_tableName = '';
1345 if (count($precompiledParts) == 0) {
1346 // Map table / field names if needed:
1347 $ORIG_tableName = $from_table;
1348 // Saving table names in $ORIG_from_table since $from_table is transformed beneath:
1349 $parsedFromTable = array();
1350 $queryComponents = array();
1351 if ($tableArray = $this->map_needMapping($ORIG_tableName, FALSE, $parsedFromTable)) {
1352 $from = $parsedFromTable ? $parsedFromTable : $from_table;
1353 $components = $this->map_remapSELECTQueryParts($select_fields, $from, $where_clause, $groupBy, $orderBy);
1354 $queryComponents['SELECT'] = $components[0];
1355 $queryComponents['FROM'] = $components[1];
1356 $queryComponents['WHERE'] = $components[2];
1357 $queryComponents['GROUPBY'] = $components[3];
1358 $queryComponents['ORDERBY'] = $components[4];
1359 $queryComponents['parameters'] = $components[5];
1360 } else {
1361 $queryComponents = $this->getQueryComponents($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
1362 }
1363 $queryComponents['ORIG_tableName'] = $ORIG_tableName;
1364 if (!$this->runningNative()) {
1365 // Quotes all fields
1366 $queryComponents['SELECT'] = $this->_quoteFieldNames($queryComponents['SELECT']);
1367 $queryComponents['FROM'] = $this->_quoteFromTables($queryComponents['FROM']);
1368 $queryComponents['WHERE'] = $this->_quoteWhereClause($queryComponents['WHERE']);
1369 $queryComponents['GROUPBY'] = $this->_quoteGroupBy($queryComponents['GROUPBY']);
1370 $queryComponents['ORDERBY'] = $this->_quoteOrderBy($queryComponents['ORDERBY']);
1371 }
1372 $precompiledParts = $this->precompileSELECTquery($queryComponents);
1373 if ($this->queryCache) {
1374 try {
1375 $this->queryCache->set($cacheKey, $precompiledParts);
1376 } catch (\TYPO3\CMS\Core\Cache\Exception $e) {
1377 if ($this->debug) {
1378 GeneralUtility::devLog($e->getMessage(), 'dbal', 1);
1379 }
1380 }
1381 }
1382 }
1383 $preparedStatement = GeneralUtility::makeInstance('TYPO3\\CMS\\Core\\Database\\PreparedStatement', '', $from_table, $precompiledParts);
1384 /* @var $preparedStatement \TYPO3\CMS\Core\Database\PreparedStatement */
1385 // Bind values to parameters
1386 foreach ($input_parameters as $key => $value) {
1387 $preparedStatement->bindValue($key, $value, \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_AUTOTYPE);
1388 }
1389 if ($this->debug) {
1390 $data = array(
1391 'args' => array($from_table, $select_fields, $where_clause, $groupBy, $orderBy, $limit, $input_parameters),
1392 'ORIG_from_table' => $ORIG_tableName
1393 );
1394 $this->debugHandler('prepare_SELECTquery', GeneralUtility::milliseconds() - $pt, $data);
1395 }
1396 // Return prepared statement
1397 return $preparedStatement;
1398 }
1399
1400 /**
1401 * Returns the parsed query components.
1402 *
1403 * @param string $select_fields
1404 * @param string $from_table
1405 * @param string $where_clause
1406 * @param string $groupBy
1407 * @param string $orderBy
1408 * @param string $limit
1409 * @throws \InvalidArgumentException
1410 * @return array
1411 */
1412 protected function getQueryComponents($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit) {
1413 $queryComponents = array(
1414 'SELECT' => '',
1415 'FROM' => '',
1416 'WHERE' => '',
1417 'GROUPBY' => '',
1418 'ORDERBY' => '',
1419 'LIMIT' => '',
1420 'parameters' => array()
1421 );
1422 $this->lastHandlerKey = $this->handler_getFromTableList($from_table);
1423 $hType = (string) $this->handlerCfg[$this->lastHandlerKey]['type'];
1424 if ($hType === 'adodb' && $this->runningADOdbDriver('postgres')) {
1425 // Possibly rewrite the LIMIT to be PostgreSQL-compatible
1426 $splitLimit = GeneralUtility::intExplode(',', $limit);
1427 // Splitting the limit values:
1428 if ($splitLimit[1]) {
1429 // If there are two parameters, do mapping differently than otherwise:
1430 $numrows = $splitLimit[1];
1431 $offset = $splitLimit[0];
1432 $limit = $numrows . ' OFFSET ' . $offset;
1433 }
1434 }
1435 $queryComponents['LIMIT'] = $limit;
1436 $queryComponents['SELECT'] = $this->SQLparser->parseFieldList($select_fields);
1437 if ($this->SQLparser->parse_error) {
1438 throw new \InvalidArgumentException($this->SQLparser->parse_error, 1310027408);
1439 }
1440 $queryComponents['FROM'] = $this->SQLparser->parseFromTables($from_table);
1441 $queryComponents['WHERE'] = $this->SQLparser->parseWhereClause($where_clause, '', $queryComponents['parameters']);
1442 if (!is_array($queryComponents['WHERE'])) {
1443 throw new \InvalidArgumentException('Could not parse where clause', 1310027427);
1444 }
1445 $queryComponents['GROUPBY'] = $this->SQLparser->parseFieldList($groupBy);
1446 $queryComponents['ORDERBY'] = $this->SQLparser->parseFieldList($orderBy);
1447 // Return the query components
1448 return $queryComponents;
1449 }
1450
1451 /**
1452 * Precompiles a SELECT prepared SQL statement.
1453 *
1454 * @param array $components
1455 * @return array Precompiled SQL statement
1456 */
1457 protected function precompileSELECTquery(array $components) {
1458 $parameterWrap = '__' . dechex(time()) . '__';
1459 foreach ($components['parameters'] as $key => $params) {
1460 if ($key === '?') {
1461 foreach ($params as $index => $param) {
1462 $components['parameters'][$key][$index][0] = $parameterWrap . $param[0] . $parameterWrap;
1463 }
1464 } else {
1465 $components['parameters'][$key][0] = $parameterWrap . $params[0] . $parameterWrap;
1466 }
1467 }
1468 $select_fields = $this->SQLparser->compileFieldList($components['SELECT']);
1469 $from_table = $this->SQLparser->compileFromTables($components['FROM']);
1470 $where_clause = $this->SQLparser->compileWhereClause($components['WHERE']);
1471 $groupBy = $this->SQLparser->compileFieldList($components['GROUPBY']);
1472 $orderBy = $this->SQLparser->compileFieldList($components['ORDERBY']);
1473 $limit = $components['LIMIT'];
1474 $precompiledParts = array();
1475 $this->lastHandlerKey = $this->handler_getFromTableList($components['ORIG_tableName']);
1476 $hType = (string) $this->handlerCfg[$this->lastHandlerKey]['type'];
1477 $precompiledParts['handler'] = $hType;
1478 $precompiledParts['ORIG_tableName'] = $components['ORIG_tableName'];
1479 switch ($hType) {
1480 case 'native':
1481 $query = parent::SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
1482 $precompiledParts['queryParts'] = explode($parameterWrap, $query);
1483 break;
1484 case 'adodb':
1485 $query = parent::SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy);
1486 $precompiledParts['queryParts'] = explode($parameterWrap, $query);
1487 $precompiledParts['LIMIT'] = $limit;
1488 break;
1489 case 'userdefined':
1490 $precompiledParts['queryParts'] = array(
1491 'SELECT' => $select_fields,
1492 'FROM' => $from_table,
1493 'WHERE' => $where_clause,
1494 'GROUPBY' => $groupBy,
1495 'ORDERBY' => $orderBy,
1496 'LIMIT' => $limit
1497 );
1498 break;
1499 }
1500 return $precompiledParts;
1501 }
1502
1503 /**
1504 * Executes a prepared query.
1505 * This method may only be called by \TYPO3\CMS\Core\Database\PreparedStatement
1506 *
1507 * @param string $query The query to execute
1508 * @param array $queryComponents The components of the query to execute
1509 * @return boolean|\mysqli_result|object MySQLi result object / DBAL object
1510 */
1511 public function exec_PREPAREDquery($query, array $queryComponents) {
1512 $pt = $this->debug ? GeneralUtility::milliseconds() : 0;
1513 // Get handler key and select API:
1514 $sqlResult = NULL;
1515 switch ($queryComponents['handler']) {
1516 case 'native':
1517 $this->lastQuery = $query;
1518 $sqlResult = $this->query($this->lastQuery);
1519 $this->resourceIdToTableNameMap[serialize($sqlResult)] = $queryComponents['ORIG_tableName'];
1520 break;
1521 case 'adodb':
1522 $limit = $queryComponents['LIMIT'];
1523 if ($this->runningADOdbDriver('postgres')) {
1524 // Possibly rewrite the LIMIT to be PostgreSQL-compatible
1525 $splitLimit = GeneralUtility::intExplode(',', $limit);
1526 // Splitting the limit values:
1527 if ($splitLimit[1]) {
1528 // If there are two parameters, do mapping differently than otherwise:
1529 $numrows = $splitLimit[1];
1530 $offset = $splitLimit[0];
1531 $limit = $numrows . ' OFFSET ' . $offset;
1532 }
1533 }
1534 if ($limit != '') {
1535 $splitLimit = GeneralUtility::intExplode(',', $limit);
1536 // Splitting the limit values:
1537 if ($splitLimit[1]) {
1538 // If there are two parameters, do mapping differently than otherwise:
1539 $numrows = $splitLimit[1];
1540 $offset = $splitLimit[0];
1541 } else {
1542 $numrows = $splitLimit[0];
1543 $offset = 0;
1544 }
1545 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->SelectLimit($query, $numrows, $offset);
1546 $this->lastQuery = $sqlResult->sql;
1547 } else {
1548 $this->lastQuery = $query;
1549 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_Execute($this->lastQuery);
1550 }
1551 $sqlResult->TYPO3_DBAL_handlerType = 'adodb';
1552 // Setting handler type in result object (for later recognition!)
1553 $sqlResult->TYPO3_DBAL_tableList = $queryComponents['ORIG_tableName'];
1554 break;
1555 case 'userdefined':
1556 $queryParts = $queryComponents['queryParts'];
1557 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->exec_SELECTquery($queryParts['SELECT'], $queryParts['FROM'], $queryParts['WHERE'], $queryParts['GROUPBY'], $queryParts['ORDERBY'], $queryParts['LIMIT']);
1558 if (is_object($sqlResult)) {
1559 $sqlResult->TYPO3_DBAL_handlerType = 'userdefined';
1560 // Setting handler type in result object (for later recognition!)
1561 $sqlResult->TYPO3_DBAL_tableList = $queryComponents['ORIG_tableName'];
1562 }
1563 break;
1564 }
1565 if ($this->printErrors && $this->sql_error()) {
1566 debug(array($this->lastQuery, $this->sql_error()));
1567 }
1568 if ($this->debug) {
1569 $data = array(
1570 'handlerType' => $queryComponents['handler'],
1571 'args' => $queryComponents,
1572 'ORIG_from_table' => $queryComponents['ORIG_tableName']
1573 );
1574 if ($this->conf['debugOptions']['numberRows']) {
1575 $data['numberRows'] = $this->sql_num_rows($sqlResult);
1576 }
1577 $this->debugHandler('exec_PREPAREDquery', GeneralUtility::milliseconds() - $pt, $data);
1578 }
1579 // Return result handler.
1580 return $sqlResult;
1581 }
1582
1583 /**************************************
1584 *
1585 * Functions for quoting table/field names
1586 *
1587 **************************************/
1588 /**
1589 * Quotes components of a SELECT subquery.
1590 *
1591 * @param array $components Array of SQL query components
1592 * @return array
1593 */
1594 protected function quoteSELECTsubquery(array $components) {
1595 $components['SELECT'] = $this->_quoteFieldNames($components['SELECT']);
1596 $components['FROM'] = $this->_quoteFromTables($components['FROM']);
1597 $components['WHERE'] = $this->_quoteWhereClause($components['WHERE']);
1598 return $components;
1599 }
1600
1601 /**
1602 * Quotes field (and table) names with the quote character suitable for the DB being used
1603 *
1604 * @param string $select_fields List of fields to be used in query to DB
1605 * @throws \InvalidArgumentException
1606 * @return string Quoted list of fields to be in query to DB
1607 */
1608 public function quoteFieldNames($select_fields) {
1609 if ($select_fields == '') {
1610 return '';
1611 }
1612 if ($this->runningNative()) {
1613 return $select_fields;
1614 }
1615 $select_fields = $this->SQLparser->parseFieldList($select_fields);
1616 if ($this->SQLparser->parse_error) {
1617 throw new \InvalidArgumentException($this->SQLparser->parse_error, 1310027490);
1618 }
1619 $select_fields = $this->_quoteFieldNames($select_fields);
1620 return $this->SQLparser->compileFieldList($select_fields);
1621 }
1622
1623 /**
1624 * Quotes field (and table) names in a SQL SELECT clause according to DB rules
1625 *
1626 * @param array $select_fields The parsed fields to quote
1627 * @return array
1628 * @see quoteFieldNames()
1629 */
1630 protected function _quoteFieldNames(array $select_fields) {
1631 foreach ($select_fields as $k => $v) {
1632 if ($select_fields[$k]['field'] != '' && $select_fields[$k]['field'] != '*' && !is_numeric($select_fields[$k]['field'])) {
1633 $select_fields[$k]['field'] = $this->quoteName($select_fields[$k]['field']);
1634 }
1635 if ($select_fields[$k]['table'] != '' && !is_numeric($select_fields[$k]['table'])) {
1636 $select_fields[$k]['table'] = $this->quoteName($select_fields[$k]['table']);
1637 }
1638 if ($select_fields[$k]['as'] != '') {
1639 $select_fields[$k]['as'] = $this->quoteName($select_fields[$k]['as']);
1640 }
1641 if (isset($select_fields[$k]['func_content.']) && $select_fields[$k]['func_content.'][0]['func_content'] != '*') {
1642 $select_fields[$k]['func_content.'][0]['func_content'] = $this->quoteFieldNames($select_fields[$k]['func_content.'][0]['func_content']);
1643 $select_fields[$k]['func_content'] = $this->quoteFieldNames($select_fields[$k]['func_content']);
1644 }
1645 if (isset($select_fields[$k]['flow-control'])) {
1646 // Quoting flow-control statements
1647 if ($select_fields[$k]['flow-control']['type'] === 'CASE') {
1648 if (isset($select_fields[$k]['flow-control']['case_field'])) {
1649 $select_fields[$k]['flow-control']['case_field'] = $this->quoteFieldNames($select_fields[$k]['flow-control']['case_field']);
1650 }
1651 foreach ($select_fields[$k]['flow-control']['when'] as $key => $when) {
1652 $select_fields[$k]['flow-control']['when'][$key]['when_value'] = $this->_quoteWhereClause($when['when_value']);
1653 }
1654 }
1655 }
1656 }
1657 return $select_fields;
1658 }
1659
1660 /**
1661 * Quotes table names with the quote character suitable for the DB being used
1662 *
1663 * @param string $from_table List of tables to be selected from DB
1664 * @return string Quoted list of tables to be selected from DB
1665 */
1666 public function quoteFromTables($from_table) {
1667 if ($from_table === '') {
1668 return '';
1669 }
1670 if ($this->runningNative()) {
1671 return $from_table;
1672 }
1673 $from_table = $this->SQLparser->parseFromTables($from_table);
1674 $from_table = $this->_quoteFromTables($from_table);
1675 return $this->SQLparser->compileFromTables($from_table);
1676 }
1677
1678 /**
1679 * Quotes table names in a SQL FROM clause according to DB rules
1680 *
1681 * @param array $from_table The parsed FROM clause to quote
1682 * @return array
1683 * @see quoteFromTables()
1684 */
1685 protected function _quoteFromTables(array $from_table) {
1686 foreach ($from_table as $k => $v) {
1687 $from_table[$k]['table'] = $this->quoteName($from_table[$k]['table']);
1688 if ($from_table[$k]['as'] != '') {
1689 $from_table[$k]['as'] = $this->quoteName($from_table[$k]['as']);
1690 }
1691 if (is_array($v['JOIN'])) {
1692 foreach ($v['JOIN'] as $joinCnt => $join) {
1693 $from_table[$k]['JOIN'][$joinCnt]['withTable'] = $this->quoteName($join['withTable']);
1694 $from_table[$k]['JOIN'][$joinCnt]['as'] = $join['as'] ? $this->quoteName($join['as']) : '';
1695 foreach ($from_table[$k]['JOIN'][$joinCnt]['ON'] as &$condition) {
1696 $condition['left']['table'] = $condition['left']['table'] ? $this->quoteName($condition['left']['table']) : '';
1697 $condition['left']['field'] = $this->quoteName($condition['left']['field']);
1698 $condition['right']['table'] = $condition['right']['table'] ? $this->quoteName($condition['right']['table']) : '';
1699 $condition['right']['field'] = $this->quoteName($condition['right']['field']);
1700 }
1701 }
1702 }
1703 }
1704 return $from_table;
1705 }
1706
1707 /**
1708 * Quotes the field (and table) names within a where clause with the quote character suitable for the DB being used
1709 *
1710 * @param string $where_clause A where clause that can be parsed by parseWhereClause
1711 * @throws \InvalidArgumentException
1712 * @return string Usable where clause with quoted field/table names
1713 */
1714 public function quoteWhereClause($where_clause) {
1715 if ($where_clause === '' || $this->runningNative()) {
1716 return $where_clause;
1717 }
1718 $where_clause = $this->SQLparser->parseWhereClause($where_clause);
1719 if (is_array($where_clause)) {
1720 $where_clause = $this->_quoteWhereClause($where_clause);
1721 $where_clause = $this->SQLparser->compileWhereClause($where_clause);
1722 } else {
1723 throw new \InvalidArgumentException('Could not parse where clause', 1310027511);
1724 }
1725 return $where_clause;
1726 }
1727
1728 /**
1729 * Quotes field names in a SQL WHERE clause according to DB rules
1730 *
1731 * @param array $where_clause The parsed WHERE clause to quote
1732 * @return array
1733 * @see quoteWhereClause()
1734 */
1735 protected function _quoteWhereClause(array $where_clause) {
1736 foreach ($where_clause as $k => $v) {
1737 // Look for sublevel:
1738 if (is_array($where_clause[$k]['sub'])) {
1739 $where_clause[$k]['sub'] = $this->_quoteWhereClause($where_clause[$k]['sub']);
1740 } elseif (isset($v['func'])) {
1741 switch ($where_clause[$k]['func']['type']) {
1742 case 'EXISTS':
1743 $where_clause[$k]['func']['subquery'] = $this->quoteSELECTsubquery($v['func']['subquery']);
1744 break;
1745 case 'FIND_IN_SET':
1746 // quoteStr that will be used for Oracle
1747 $pattern = str_replace($where_clause[$k]['func']['str'][1], '\\' . $where_clause[$k]['func']['str'][1], $where_clause[$k]['func']['str'][0]);
1748 // table is not really needed and may in fact be empty in real statements
1749 // but it's not overridden from \TYPO3\CMS\Core\Database\DatabaseConnection at the moment...
1750 $patternForLike = $this->escapeStrForLike($pattern, $where_clause[$k]['func']['table']);
1751 $where_clause[$k]['func']['str_like'] = $patternForLike;
1752 // Intentional fallthrough
1753 case 'IFNULL':
1754 // Intentional fallthrough
1755 case 'LOCATE':
1756 if ($where_clause[$k]['func']['table'] != '') {
1757 $where_clause[$k]['func']['table'] = $this->quoteName($v['func']['table']);
1758 }
1759 if ($where_clause[$k]['func']['field'] != '') {
1760 $where_clause[$k]['func']['field'] = $this->quoteName($v['func']['field']);
1761 }
1762 break;
1763 }
1764 } else {
1765 if ($where_clause[$k]['table'] != '') {
1766 $where_clause[$k]['table'] = $this->quoteName($where_clause[$k]['table']);
1767 }
1768 if (!is_numeric($where_clause[$k]['field'])) {
1769 $where_clause[$k]['field'] = $this->quoteName($where_clause[$k]['field']);
1770 }
1771 if (isset($where_clause[$k]['calc_table'])) {
1772 if ($where_clause[$k]['calc_table'] != '') {
1773 $where_clause[$k]['calc_table'] = $this->quoteName($where_clause[$k]['calc_table']);
1774 }
1775 if ($where_clause[$k]['calc_field'] != '') {
1776 $where_clause[$k]['calc_field'] = $this->quoteName($where_clause[$k]['calc_field']);
1777 }
1778 }
1779 }
1780 if ($where_clause[$k]['comparator']) {
1781 if (isset($v['value']['operator'])) {
1782 foreach ($where_clause[$k]['value']['args'] as $argK => $fieldDef) {
1783 $where_clause[$k]['value']['args'][$argK]['table'] = $this->quoteName($fieldDef['table']);
1784 $where_clause[$k]['value']['args'][$argK]['field'] = $this->quoteName($fieldDef['field']);
1785 }
1786 } else {
1787 // Detecting value type; list or plain:
1788 if (GeneralUtility::inList('NOTIN,IN', strtoupper(str_replace(array(' ', '
1789 ', '
1790 ', ' '), '', $where_clause[$k]['comparator'])))) {
1791 if (isset($v['subquery'])) {
1792 $where_clause[$k]['subquery'] = $this->quoteSELECTsubquery($v['subquery']);
1793 }
1794 } else {
1795 if (
1796 (!isset($where_clause[$k]['value'][1]) || $where_clause[$k]['value'][1] == '')
1797 && is_string($where_clause[$k]['value'][0]) && strstr($where_clause[$k]['value'][0], '.')
1798 ) {
1799 $where_clause[$k]['value'][0] = $this->quoteFieldNames($where_clause[$k]['value'][0]);
1800 }
1801 }
1802 }
1803 }
1804 }
1805 return $where_clause;
1806 }
1807
1808 /**
1809 * Quotes the field (and table) names within a group by clause with the quote
1810 * character suitable for the DB being used
1811 *
1812 * @param string $groupBy A group by clause that can by parsed by parseFieldList
1813 * @return string Usable group by clause with quoted field/table names
1814 */
1815 protected function quoteGroupBy($groupBy) {
1816 if ($groupBy === '') {
1817 return '';
1818 }
1819 if ($this->runningNative()) {
1820 return $groupBy;
1821 }
1822 $groupBy = $this->SQLparser->parseFieldList($groupBy);
1823 $groupBy = $this->_quoteGroupBy($groupBy);
1824 return $this->SQLparser->compileFieldList($groupBy);
1825 }
1826
1827 /**
1828 * Quotes field names in a SQL GROUP BY clause according to DB rules
1829 *
1830 * @param array $groupBy The parsed GROUP BY clause to quote
1831 * @return array
1832 * @see quoteGroupBy()
1833 */
1834 protected function _quoteGroupBy(array $groupBy) {
1835 foreach ($groupBy as $k => $v) {
1836 $groupBy[$k]['field'] = $this->quoteName($groupBy[$k]['field']);
1837 if ($groupBy[$k]['table'] != '') {
1838 $groupBy[$k]['table'] = $this->quoteName($groupBy[$k]['table']);
1839 }
1840 }
1841 return $groupBy;
1842 }
1843
1844 /**
1845 * Quotes the field (and table) names within an order by clause with the quote
1846 * character suitable for the DB being used
1847 *
1848 * @param string $orderBy An order by clause that can by parsed by parseFieldList
1849 * @return string Usable order by clause with quoted field/table names
1850 */
1851 protected function quoteOrderBy($orderBy) {
1852 if ($orderBy === '') {
1853 return '';
1854 }
1855 if ($this->runningNative()) {
1856 return $orderBy;
1857 }
1858 $orderBy = $this->SQLparser->parseFieldList($orderBy);
1859 $orderBy = $this->_quoteOrderBy($orderBy);
1860 return $this->SQLparser->compileFieldList($orderBy);
1861 }
1862
1863 /**
1864 * Quotes field names in a SQL ORDER BY clause according to DB rules
1865 *
1866 * @param array $orderBy The parsed ORDER BY clause to quote
1867 * @return array
1868 * @see quoteOrderBy()
1869 */
1870 protected function _quoteOrderBy(array $orderBy) {
1871 foreach ($orderBy as $k => $v) {
1872 if ($orderBy[$k]['table'] === '' && $v['field'] !== '' && ctype_digit($v['field'])) {
1873 continue;
1874 }
1875 $orderBy[$k]['field'] = $this->quoteName($orderBy[$k]['field']);
1876 if ($orderBy[$k]['table'] !== '') {
1877 $orderBy[$k]['table'] = $this->quoteName($orderBy[$k]['table']);
1878 }
1879 }
1880 return $orderBy;
1881 }
1882
1883 /**************************************
1884 *
1885 * Various helper functions
1886 *
1887 **************************************/
1888 /**
1889 * Escaping and quoting values for SQL statements.
1890 *
1891 * @param string $str Input string
1892 * @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!).
1893 * @param bool $allowNull Whether to allow NULL values
1894 * @return string Output string; Wrapped in single quotes and quotes in the string (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
1895 * @see quoteStr()
1896 */
1897 public function fullQuoteStr($str, $table, $allowNull = FALSE) {
1898 if ($allowNull && $str === NULL) {
1899 return 'NULL';
1900 }
1901 return '\'' . $this->quoteStr($str, $table) . '\'';
1902 }
1903
1904 /**
1905 * Substitution for PHP function "addslashes()"
1906 * Use this function instead of the PHP addslashes() function when you build queries - this will prepare your code for DBAL.
1907 * 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()!
1908 *
1909 * @param string $str Input string
1910 * @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!).
1911 * @throws \RuntimeException
1912 * @return string Output string; Quotes (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
1913 * @see quoteStr()
1914 */
1915 public function quoteStr($str, $table) {
1916 $this->lastHandlerKey = $this->handler_getFromTableList($table);
1917 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
1918 case 'native':
1919 if ($this->handlerInstance[$this->lastHandlerKey]['link']) {
1920 if (!$this->isConnected()) {
1921 $this->connectDB();
1922 }
1923 $str = $this->handlerInstance[$this->lastHandlerKey]['link']->real_escape_string($str);
1924 } else {
1925 // link may be null when unit testing DBAL
1926 $str = str_replace('\'', '\\\'', $str);
1927 }
1928 break;
1929 case 'adodb':
1930 if (!$this->isConnected()) {
1931 $this->connectDB();
1932 }
1933 $str = substr($this->handlerInstance[$this->lastHandlerKey]->qstr($str), 1, -1);
1934 break;
1935 case 'userdefined':
1936 $str = $this->handlerInstance[$this->lastHandlerKey]->quoteStr($str);
1937 break;
1938 default:
1939 throw new \RuntimeException('No handler found!!!', 1310027655);
1940 }
1941 return $str;
1942 }
1943
1944 /**
1945 * Quotes an object name (table name, field, ...)
1946 *
1947 * @param string $name Object's name
1948 * @param string $handlerKey Handler key
1949 * @param bool $useBackticks If method NameQuote() is not used, whether to use backticks instead of driver-specific quotes
1950 * @return string Properly-quoted object's name
1951 */
1952 public function quoteName($name, $handlerKey = NULL, $useBackticks = FALSE) {
1953 $handlerKey = $handlerKey ? $handlerKey : $this->lastHandlerKey;
1954 $useNameQuote = isset($this->handlerCfg[$handlerKey]['config']['useNameQuote']) ? $this->handlerCfg[$handlerKey]['config']['useNameQuote'] : FALSE;
1955 if ($useNameQuote) {
1956 // Sometimes DataDictionary is not properly instantiated
1957 if (!is_object($this->handlerInstance[$handlerKey]->DataDictionary)) {
1958 $this->handlerInstance[$handlerKey]->DataDictionary = NewDataDictionary($this->handlerInstance[$handlerKey]);
1959 }
1960 return $this->handlerInstance[$handlerKey]->DataDictionary->NameQuote($name);
1961 } else {
1962 $quote = $useBackticks ? '`' : $this->handlerInstance[$handlerKey]->nameQuote;
1963 return $quote . $name . $quote;
1964 }
1965 }
1966
1967 /**
1968 * Return MetaType for native field type (ADOdb only!)
1969 *
1970 * @param string $type Native type as reported by admin_get_fields()
1971 * @param string $table Table name for which query type string. Important for detection of DBMS handler of the query!
1972 * @param int $maxLength
1973 * @throws \RuntimeException
1974 * @return string Meta type (currently ADOdb syntax only, http://phplens.com/lens/adodb/docs-adodb.htm#metatype)
1975 */
1976 public function MetaType($type, $table, $maxLength = -1) {
1977 $this->lastHandlerKey = $this->handler_getFromTableList($table);
1978 $str = '';
1979 switch ((string) $this->handlerCfg[$this->lastHandlerKey]['type']) {
1980 case 'native':
1981 $str = $type;
1982 break;
1983 case 'adodb':
1984 if (in_array($table, $this->cache_fieldType)) {
1985 $rs = $this->handlerInstance[$this->lastHandlerKey]->SelectLimit('SELECT * FROM ' . $this->quoteFromTables($table), 1);
1986 $str = $rs->MetaType($type, $maxLength);
1987 }
1988 break;
1989 case 'userdefined':
1990 $str = $this->handlerInstance[$this->lastHandlerKey]->MetaType($str, $table, $maxLength);
1991 break;
1992 default:
1993 throw new \RuntimeException('No handler found!!!', 1310027685);
1994 }
1995 return $str;
1996 }
1997
1998 /**
1999 * Return MetaType for native MySQL field type
2000 *
2001 * @param string $t native type as reported as in mysqldump files
2002 * @return string Meta type (currently ADOdb syntax only, http://phplens.com/lens/adodb/docs-adodb.htm#metatype)
2003 */
2004 public function MySQLMetaType($t) {
2005 switch (strtoupper($t)) {
2006 case 'STRING':
2007
2008 case 'CHAR':
2009
2010 case 'VARCHAR':
2011
2012 case 'TINYBLOB':
2013
2014 case 'TINYTEXT':
2015
2016 case 'ENUM':
2017
2018 case 'SET':
2019 return 'C';
2020 case 'TEXT':
2021
2022 case 'LONGTEXT':
2023
2024 case 'MEDIUMTEXT':
2025 return 'XL';
2026 case 'IMAGE':
2027
2028 case 'LONGBLOB':
2029
2030 case 'BLOB':
2031
2032 case 'MEDIUMBLOB':
2033 return 'B';
2034 case 'YEAR':
2035
2036 case 'DATE':
2037 return 'D';
2038 case 'TIME':
2039
2040 case 'DATETIME':
2041
2042 case 'TIMESTAMP':
2043 return 'T';
2044 case 'FLOAT':
2045
2046 case 'DOUBLE':
2047 return 'F';
2048 case 'INT':
2049
2050 case 'INTEGER':
2051
2052 case 'TINYINT':
2053
2054 case 'SMALLINT':
2055
2056 case 'MEDIUMINT':
2057
2058 case 'BIGINT':
2059 return 'I8';
2060 default:
2061 return 'N';
2062 }
2063 }
2064
2065 /**
2066 * Return actual MySQL type for meta field type
2067 *
2068 * @param string $meta Meta type (currenly ADOdb syntax only, http://phplens.com/lens/adodb/docs-adodb.htm#metatype)
2069 * @return string Native type as reported as in mysqldump files, uppercase
2070 */
2071 public function MySQLActualType($meta) {
2072 switch (strtoupper($meta)) {
2073 case 'C':
2074 return 'VARCHAR';
2075 case 'XL':
2076
2077 case 'X':
2078 return 'LONGTEXT';
2079 case 'C2':
2080 return 'VARCHAR';
2081 case 'X2':
2082 return 'LONGTEXT';
2083 case 'B':
2084 return 'LONGBLOB';
2085 case 'D':
2086 return 'DATE';
2087 case 'T':
2088 return 'DATETIME';
2089 case 'L':
2090 return 'TINYINT';
2091 case 'I':
2092
2093 case 'I1':
2094
2095 case 'I2':
2096
2097 case 'I4':
2098
2099 case 'I8':
2100 return 'BIGINT';
2101 case 'F':
2102 return 'DOUBLE';
2103 case 'N':
2104 return 'NUMERIC';
2105 default:
2106 return $meta;
2107 }
2108 }
2109
2110 /**************************************
2111 *
2112 * SQL wrapper functions (Overriding parent methods)
2113 * (For use in your applications)
2114 *
2115 **************************************/
2116 /**
2117 * Returns the error status on the last query() execution
2118 *
2119 * @return string MySQLi error string.
2120 */
2121 public function sql_error() {
2122 $output = '';
2123 switch ($this->handlerCfg[$this->lastHandlerKey]['type']) {
2124 case 'native':
2125 $output = $this->handlerInstance[$this->lastHandlerKey]['link']->error;
2126 break;
2127 case 'adodb':
2128 $output = $this->handlerInstance[$this->lastHandlerKey]->ErrorMsg();
2129 break;
2130 case 'userdefined':
2131 $output = $this->handlerInstance[$this->lastHandlerKey]->sql_error();
2132 break;
2133 }
2134 return $output;
2135 }
2136
2137 /**
2138 * Returns the error number on the last query() execution
2139 *
2140 * @return integer MySQLi error number
2141 */
2142 public function sql_errno() {
2143 $output = 0;
2144 switch ($this->handlerCfg[$this->lastHandlerKey]['type']) {
2145 case 'native':
2146 $output = $this->handlerInstance[$this->lastHandlerKey]['link']->errno;
2147 break;
2148 case 'adodb':
2149 $output = $this->handlerInstance[$this->lastHandlerKey]->ErrorNo();
2150 break;
2151 case 'userdefined':
2152 $output = $this->handlerInstance[$this->lastHandlerKey]->sql_errno();
2153 break;
2154 }
2155 return $output;
2156 }
2157
2158 /**
2159 * Returns the number of selected rows.
2160 *
2161 * @param boolean|\mysqli_result|object $res MySQLi result object / DBAL object
2162 * @return integer Number of resulting rows
2163 */
2164 public function sql_num_rows($res) {
2165 if ($res === FALSE) {
2166 return FALSE;
2167 }
2168 $handlerType = $this->determineHandlerType($res);
2169 $output = 0;
2170 switch ($handlerType) {
2171 case 'native':
2172 $output = $res->num_rows;
2173 break;
2174 case 'adodb':
2175 $output = method_exists($res, 'RecordCount') ? $res->RecordCount() : 0;
2176 break;
2177 case 'userdefined':
2178 $output = $res->sql_num_rows();
2179 break;
2180 }
2181 return $output;
2182 }
2183
2184 /**
2185 * Returns an associative array that corresponds to the fetched row, or FALSE if there are no more rows.
2186 * MySQLi fetch_assoc() wrapper function
2187 *
2188 * @param boolean|\mysqli_result|object $res MySQLi result object / DBAL object
2189 * @return array|boolean Associative array of result row.
2190 */
2191 public function sql_fetch_assoc($res) {
2192 $tableList = '';
2193 $output = FALSE;
2194 switch ($this->determineHandlerType($res)) {
2195 case 'native':
2196 $output = $res->fetch_assoc();
2197 $key = serialize($res);
2198 $tableList = $this->resourceIdToTableNameMap[$key];
2199 unset($this->resourceIdToTableNameMap[$key]);
2200 // Reading list of tables from SELECT query:
2201 break;
2202 case 'adodb':
2203 // Check if method exists for the current $res object.
2204 // If a table exists in TCA but not in the db, a error
2205 // occurred because $res is not a valid object.
2206 if (method_exists($res, 'FetchRow')) {
2207 $output = $res->FetchRow();
2208 $tableList = $res->TYPO3_DBAL_tableList;
2209 // Reading list of tables from SELECT query:
2210 // Removing all numeric/integer keys.
2211 // A workaround because in ADOdb we would need to know what we want before executing the query...
2212 // MSSQL does not support ADODB_FETCH_BOTH and always returns an assoc. array instead. So
2213 // we don't need to remove anything.
2214 if (is_array($output)) {
2215 if ($this->runningADOdbDriver('mssql')) {
2216 // MSSQL does not know such thing as an empty string. So it returns one space instead, which we must fix.
2217 foreach ($output as $key => $value) {
2218 if ($value === ' ') {
2219 $output[$key] = '';
2220 }
2221 }
2222 } else {
2223 foreach ($output as $key => $value) {
2224 if (is_integer($key)) {
2225 unset($output[$key]);
2226 }
2227 }
2228 }
2229 }
2230 }
2231 break;
2232 case 'userdefined':
2233 $output = $res->sql_fetch_assoc();
2234 $tableList = $res->TYPO3_DBAL_tableList;
2235 // Reading list of tables from SELECT query:
2236 break;
2237 }
2238 // Table/Fieldname mapping:
2239 if (is_array($output)) {
2240 if ($tables = $this->map_needMapping($tableList, TRUE)) {
2241 $output = $this->map_assocArray($output, $tables, 1);
2242 }
2243 }
2244 // Return result:
2245 return $output;
2246 }
2247
2248 /**
2249 * Returns an array that corresponds to the fetched row, or FALSE if there are no more rows.
2250 * The array contains the values in numerical indices.
2251 * MySQLi fetch_row() wrapper function
2252 *
2253 * @param boolean|\mysqli_result|object $res MySQLi result object / DBAL object
2254 * @return array|boolean Array with result rows.
2255 */
2256 public function sql_fetch_row($res) {
2257 $output = FALSE;
2258 switch ($this->determineHandlerType($res)) {
2259 case 'native':
2260 $output = $res->fetch_row();
2261 if ($output === NULL) {
2262 // Needed for compatibility
2263 $output = FALSE;
2264 }
2265 break;
2266 case 'adodb':
2267 // Check if method exists for the current $res object.
2268 // If a table exists in TCA but not in the db, a error
2269 // occurred because $res is not a valid object.
2270 if (method_exists($res, 'FetchRow')) {
2271 $output = $res->FetchRow();
2272 // Removing all assoc. keys.
2273 // A workaround because in ADOdb we would need to know what we want before executing the query...
2274 // MSSQL does not support ADODB_FETCH_BOTH and always returns an assoc. array instead. So
2275 // we need to convert resultset.
2276 if (is_array($output)) {
2277 $keyIndex = 0;
2278 foreach ($output as $key => $value) {
2279 unset($output[$key]);
2280 if (is_integer($key) || $this->runningADOdbDriver('mssql')) {
2281 $output[$keyIndex] = $value;
2282 if ($value === ' ') {
2283 // MSSQL does not know such thing as an empty string. So it returns one space instead, which we must fix.
2284 $output[$keyIndex] = '';
2285 }
2286 $keyIndex++;
2287 }
2288 }
2289 }
2290 }
2291 break;
2292 case 'userdefined':
2293 $output = $res->sql_fetch_row();
2294 break;
2295 }
2296 return $output;
2297 }
2298
2299 /**
2300 * Free result memory
2301 * free_result() wrapper function
2302 *
2303 * @param boolean|\mysqli_result|object $res MySQLi result object / DBAL object
2304 * @return bool Returns TRUE on success or FALSE on failure.
2305 */
2306 public function sql_free_result($res) {
2307 if ($res === FALSE) {
2308 return FALSE;
2309 }
2310 $output = TRUE;
2311 switch ($this->determineHandlerType($res)) {
2312 case 'native':
2313 $res->free();
2314 break;
2315 case 'adodb':
2316 if (method_exists($res, 'Close')) {
2317 $res->Close();
2318 unset($res);
2319 $output = TRUE;
2320 } else {
2321 $output = FALSE;
2322 }
2323 break;
2324 case 'userdefined':
2325 unset($res);
2326 break;
2327 }
2328 return $output;
2329 }
2330
2331 /**
2332 * Determine handler type by result set
2333 *
2334 * @param bool|\mysqli_result|object $res MySQLi result set / DBAL Object
2335 * @return bool|string
2336 */
2337 protected function determineHandlerType($res) {
2338 if (is_object($res) && !$res instanceof \mysqli_result) {
2339 $handlerType = $res->TYPO3_DBAL_handlerType;
2340 } elseif ($res instanceof \mysqli_result) {
2341 $handlerType = 'native';
2342 } else {
2343 $handlerType = FALSE;
2344 }
2345 return $handlerType;
2346 }
2347
2348 /**
2349 * Get the ID generated from the previous INSERT operation
2350 *
2351 * @return integer The uid of the last inserted record.
2352 */
2353 public function sql_insert_id() {
2354 $output = 0;
2355 switch ($this->handlerCfg[$this->lastHandlerKey]['type']) {
2356 case 'native':
2357 $output = $this->handlerInstance[$this->lastHandlerKey]['link']->insert_id;
2358 break;
2359 case 'adodb':
2360 $output = $this->handlerInstance[$this->lastHandlerKey]->last_insert_id;
2361 break;
2362 case 'userdefined':
2363 $output = $this->handlerInstance[$this->lastHandlerKey]->sql_insert_id();
2364 break;
2365 }
2366 return $output;
2367 }
2368
2369 /**
2370 * Returns the number of rows affected by the last INSERT, UPDATE or DELETE query
2371 *
2372 * @return integer Number of rows affected by last query
2373 */
2374 public function sql_affected_rows() {
2375 $output = 0;
2376 switch ($this->handlerCfg[$this->lastHandlerKey]['type']) {
2377 case 'native':
2378 $output = $this->handlerInstance[$this->lastHandlerKey]['link']->affected_rows;
2379 break;
2380 case 'adodb':
2381 $output = $this->handlerInstance[$this->lastHandlerKey]->Affected_Rows();
2382 break;
2383 case 'userdefined':
2384 $output = $this->handlerInstance[$this->lastHandlerKey]->sql_affected_rows();
2385 break;
2386 }
2387 return $output;
2388 }
2389
2390 /**
2391 * Move internal result pointer
2392 *
2393 * @param boolean|\mysqli_result|object $res MySQLi result object / DBAL object
2394 * @param integer $seek Seek result number.
2395 * @return bool Returns TRUE on success or FALSE on failure.
2396 */
2397 public function sql_data_seek($res, $seek) {
2398 $output = TRUE;
2399 switch ($this->determineHandlerType($res)) {
2400 case 'native':
2401 $output = $res->data_seek($seek);
2402 break;
2403 case 'adodb':
2404 $output = $res->Move($seek);
2405 break;
2406 case 'userdefined':
2407 $output = $res->sql_data_seek($seek);
2408 break;
2409 }
2410 return $output;
2411 }
2412
2413 /**
2414 * Get the type of the specified field in a result
2415 *
2416 * If the first parameter is a string, it is used as table name for the lookup.
2417 *
2418 * @param string $table MySQL result pointer (of SELECT query) / DBAL object / table name
2419 * @param integer $field Field index. In case of ADOdb a string (field name!)
2420 * @return string Returns the type of the specified field index
2421 */
2422 public function sql_field_metatype($table, $field) {
2423 // If $table and/or $field are mapped, use the original names instead
2424 foreach ($this->mapping as $tableName => $tableMapInfo) {
2425 if (isset($tableMapInfo['mapTableName']) && $tableMapInfo['mapTableName'] === $table) {
2426 // Table name is mapped => use original name
2427 $table = $tableName;
2428 }
2429 if (isset($tableMapInfo['mapFieldNames'])) {
2430 foreach ($tableMapInfo['mapFieldNames'] as $fieldName => $fieldMapInfo) {
2431 if ($fieldMapInfo === $field) {
2432 // Field name is mapped => use original name
2433 $field = $fieldName;
2434 }
2435 }
2436 }
2437 }
2438 return $this->cache_fieldType[$table][$field]['metaType'];
2439 }
2440
2441 /**
2442 * Get the type of the specified field in a result
2443 * mysql_field_type() wrapper function
2444 *
2445 * @param boolean|\mysqli_result|object $res MySQLi result object / DBAL object
2446 * @param integer $pointer Field index.
2447 * @return string Returns the name of the specified field index, or FALSE on error
2448 */
2449 public function sql_field_type($res, $pointer) {
2450 if ($res === NULL) {
2451 debug(array('no res in sql_field_type!'));
2452 return 'text';
2453 } elseif (is_string($res)) {
2454 if ($res === 'tx_dbal_debuglog') {
2455 return 'text';
2456 }
2457 $handlerType = 'adodb';
2458 } else {
2459 $handlerType = $this->determineHandlerType($res);
2460 }
2461 $output = '';
2462 switch ($handlerType) {
2463 case 'native':
2464 $metaInfo = $res->fetch_field_direct($pointer);
2465 if ($metaInfo) {
2466 $output = $this->mysqlDataTypeMapping[$metaInfo->type];
2467 } else {
2468 $output = '';
2469 }
2470 break;
2471 case 'adodb':
2472 if (is_string($pointer)) {
2473 $output = $this->cache_fieldType[$res][$pointer]['type'];
2474 }
2475 break;
2476 case 'userdefined':
2477 $output = $res->sql_field_type($pointer);
2478 break;
2479 }
2480 return $output;
2481 }
2482
2483 /**********
2484 *
2485 * Legacy functions, bound to _DEFAULT handler. (Overriding parent methods)
2486 * Deprecated or still experimental.
2487 *
2488 **********/
2489 /**
2490 * Executes query
2491 *
2492 * EXPERIMENTAL - This method will make its best to handle the query correctly
2493 * but if it cannot, it will simply pass the query to DEFAULT handler.
2494 *
2495 * You should use exec_* function from this class instead!
2496 * If you don't, anything that does not use the _DEFAULT handler will probably break!
2497 *
2498 * MySQLi query() wrapper function
2499 * Beware: Use of this method should be avoided as it is experimentally supported by DBAL. You should consider
2500 * using exec_SELECTquery() and similar methods instead.
2501 *
2502 * @param string $query Query to execute
2503 * @return boolean|\mysqli_result|object MySQLi result object / DBAL object
2504 */
2505 public function sql_query($query) {
2506 $globalConfig = unserialize($GLOBALS['TYPO3_CONF_VARS']['EXT']['extConf']['dbal']);
2507 if ($globalConfig['sql_query.']['passthrough']) {
2508 return parent::sql_query($query);
2509 }
2510 // This method is heavily used by Extbase, try to handle it with DBAL-native methods
2511 $queryParts = $this->SQLparser->parseSQL($query);
2512 if (is_array($queryParts) && GeneralUtility::inList('SELECT,UPDATE,INSERT,DELETE', $queryParts['type'])) {
2513 return $this->exec_query($queryParts);
2514 }
2515 $sqlResult = NULL;
2516 switch ($this->handlerCfg['_DEFAULT']['type']) {
2517 case 'native':
2518 if (!$this->isConnected()) {
2519 $this->connectDB();
2520 }
2521 $sqlResult = $this->handlerInstance['_DEFAULT']['link']->query($query);
2522 break;
2523 case 'adodb':
2524 $sqlResult = $this->handlerInstance['_DEFAULT']->Execute($query);
2525 $sqlResult->TYPO3_DBAL_handlerType = 'adodb';
2526 break;
2527 case 'userdefined':
2528 $sqlResult = $this->handlerInstance['_DEFAULT']->sql_query($query);
2529 $sqlResult->TYPO3_DBAL_handlerType = 'userdefined';
2530 break;
2531 }
2532 $this->lastHandlerKey = '_DEFAULT';
2533 if ($this->printErrors && $this->sql_error()) {
2534 debug(array($this->lastQuery, $this->sql_error()));
2535 }
2536 return $sqlResult;
2537 }
2538
2539 /**
2540 * Open a (persistent) connection to a MySQL server
2541 *
2542 * @param string $host Deprecated since 6.1, will be removed in two versions. Database host IP/domain[:port]
2543 * @param string $username Deprecated since 6.1, will be removed in two versions. Username to connect with.
2544 * @param string $password Deprecated since 6.1, will be removed in two versions. Password to connect with.
2545 * @return boolean|void
2546 * @throws \RuntimeException
2547 */
2548 public function sql_pconnect($host = NULL, $username = NULL, $password = NULL) {
2549 if ($host || $username || $password) {
2550 $this->handleDeprecatedConnectArguments($host, $username, $password);
2551 }
2552
2553 // Overriding the _DEFAULT handler configuration of username, password, localhost and database name:
2554 $this->handlerCfg['_DEFAULT']['config']['username'] = $this->databaseUsername;
2555 $this->handlerCfg['_DEFAULT']['config']['password'] = $this->databaseUserPassword;
2556 $this->handlerCfg['_DEFAULT']['config']['host'] = $this->databaseHost;
2557 $this->handlerCfg['_DEFAULT']['config']['port'] = (int)$this->databasePort;
2558 $this->handlerCfg['_DEFAULT']['config']['database'] = $this->databaseName;
2559 // Initializing and output value:
2560 $sqlResult = $this->handler_init('_DEFAULT');
2561 return $sqlResult;
2562 }
2563
2564 /**
2565 * Select a SQL database
2566 *
2567 * @param string $TYPO3_db Deprecated since 6.1, will be removed in two versions. Database to connect to.
2568 * @return bool Returns TRUE on success or FALSE on failure.
2569 */
2570 public function sql_select_db($TYPO3_db = NULL) {
2571 if (!$TYPO3_db) {
2572 $TYPO3_db = $this->handlerCfg[$this->lastHandlerKey]['config']['database'];
2573 }
2574 $ret = TRUE;
2575 if ((string)$this->handlerCfg[$this->lastHandlerKey]['type'] === 'native') {
2576 $ret = $this->handlerInstance[$this->lastHandlerKey]['link']->select_db($TYPO3_db);
2577 }
2578 if (!$ret) {
2579 GeneralUtility::sysLog(
2580 'Could not select MySQL database ' . $TYPO3_db . ': ' . $this->sql_error(),
2581 'Core',
2582 GeneralUtility::SYSLOG_SEVERITY_FATAL
2583 );
2584 }
2585 return $ret;
2586 }
2587
2588 /**************************************
2589 *
2590 * SQL admin functions
2591 * (For use in the Install Tool and Extension Manager)
2592 *
2593 **************************************/
2594 /**
2595 * Listing databases from current MySQL connection. NOTICE: It WILL try to select those databases and thus break selection of current database.
2596 * This is only used as a service function in the (1-2-3 process) of the Install Tool.
2597 * In any case a lookup should be done in the _DEFAULT handler DBMS then.
2598 * Use in Install Tool only!
2599 *
2600 * @return array Each entry represents a database name
2601 * @throws \RuntimeException
2602 */
2603 public function admin_get_dbs() {
2604 $dbArr = array();
2605 $this->lastHandlerKey = '_DEFAULT';
2606 switch ($this->handlerCfg['_DEFAULT']['type']) {
2607 case 'native':
2608 /** @var \mysqli_result $db_list */
2609 $db_list = $this->query("SELECT SCHEMA_NAME FROM information_schema.SCHEMATA");
2610 while ($row = $db_list->fetch_object()) {
2611 if ($this->sql_select_db($row->SCHEMA_NAME)) {
2612 $dbArr[] = $row->SCHEMA_NAME;
2613 }
2614 }
2615 $db_list->free();
2616 break;
2617 case 'adodb':
2618 // check needed for install tool - otherwise it will just die because the call to
2619 // MetaDatabases is done on a stdClass instance
2620 if (method_exists($this->handlerInstance['_DEFAULT'], 'MetaDatabases')) {
2621 $sqlDBs = $this->handlerInstance['_DEFAULT']->MetaDatabases();
2622 if (is_array($sqlDBs)) {
2623 foreach ($sqlDBs as $k => $theDB) {
2624 $dbArr[] = $theDB;
2625 }
2626 }
2627 }
2628 break;
2629 case 'userdefined':
2630 $dbArr = $this->handlerInstance['_DEFAULT']->admin_get_tables();
2631 break;
2632 }
2633 return $dbArr;
2634 }
2635
2636 /**
2637 * Returns the list of tables from the default database, TYPO3_db (quering the DBMS)
2638 * In a DBAL this method should 1) look up all tables from the DBMS of
2639 * the _DEFAULT handler and then 2) add all tables *configured* to be managed by other handlers
2640 *
2641 * @return array Array with tablenames as key and arrays with status information as value
2642 */
2643 public function admin_get_tables() {
2644 $whichTables = array();
2645 // Getting real list of tables:
2646 switch ($this->handlerCfg['_DEFAULT']['type']) {
2647 case 'native':
2648 $tables_result = $this->query('SHOW TABLE STATUS FROM `' . TYPO3_db . '`');
2649 if (!$this->sql_error()) {
2650 while ($theTable = $this->sql_fetch_assoc($tables_result)) {
2651 $whichTables[$theTable['Name']] = $theTable;
2652 }
2653 }
2654 $tables_result->free();
2655 break;
2656 case 'adodb':
2657 // check needed for install tool - otherwise it will just die because the call to
2658 // MetaTables is done on a stdClass instance
2659 if (method_exists($this->handlerInstance['_DEFAULT'], 'MetaTables')) {
2660 $sqlTables = $this->handlerInstance['_DEFAULT']->MetaTables('TABLES');
2661 foreach ($sqlTables as $k => $theTable) {
2662 if (preg_match('/BIN\\$/', $theTable)) {
2663 // Skip tables from the Oracle 10 Recycle Bin
2664 continue;
2665 }
2666 $whichTables[$theTable] = $theTable;
2667 }
2668 }
2669 break;
2670 case 'userdefined':
2671 $whichTables = $this->handlerInstance['_DEFAULT']->admin_get_tables();
2672 break;
2673 }
2674 // Check mapping:
2675 if (is_array($this->mapping) && count($this->mapping)) {
2676 // Mapping table names in reverse, first getting list of real table names:
2677 $tMap = array();
2678 foreach ($this->mapping as $tN => $tMapInfo) {
2679 if (isset($tMapInfo['mapTableName'])) {
2680 $tMap[$tMapInfo['mapTableName']] = $tN;
2681 }
2682 }
2683 // Do mapping:
2684 $newList = array();
2685 foreach ($whichTables as $tN => $tDefinition) {
2686 if (isset($tMap[$tN])) {
2687 $tN = $tMap[$tN];
2688 }
2689 $newList[$tN] = $tDefinition;
2690 }
2691 $whichTables = $newList;
2692 }
2693 // Adding tables configured to reside in other DBMS (handler by other handlers than the default):
2694 if (is_array($this->table2handlerKeys)) {
2695 foreach ($this->table2handlerKeys as $key => $handlerKey) {
2696 $whichTables[$key] = $key;
2697 }
2698 }
2699 return $whichTables;
2700 }
2701
2702 /**
2703 * Returns information about each field in the $table (quering the DBMS)
2704 * In a DBAL this should look up the right handler for the table and return compatible information
2705 * This function is important not only for the Install Tool but probably for
2706 * DBALs as well since they might need to look up table specific information
2707 * in order to construct correct queries. In such cases this information should
2708 * probably be cached for quick delivery.
2709 *
2710 * @param string $tableName Table name
2711 * @return array Field information in an associative array with fieldname => field row
2712 */
2713 public function admin_get_fields($tableName) {
2714 $output = array();
2715 // Do field mapping if needed:
2716 $ORIG_tableName = $tableName;
2717 if ($tableArray = $this->map_needMapping($tableName)) {
2718 // Table name:
2719 if ($this->mapping[$tableName]['mapTableName']) {
2720 $tableName = $this->mapping[$tableName]['mapTableName'];
2721 }
2722 }
2723 // Find columns
2724 $this->lastHandlerKey = $this->handler_getFromTableList($tableName);
2725 switch ((string) $this->handlerCfg[$this->lastHandlerKey]['type']) {
2726 case 'native':
2727 /** @var \mysqli_result $columns_res */
2728 $columns_res = $this->query('SHOW columns FROM ' . $tableName);
2729 while ($fieldRow = $columns_res->fetch_assoc()) {
2730 $output[$fieldRow['Field']] = $fieldRow;
2731 }
2732 $columns_res->free();
2733 break;
2734 case 'adodb':
2735 $fieldRows = $this->handlerInstance[$this->lastHandlerKey]->MetaColumns($tableName, FALSE);
2736 if (is_array($fieldRows)) {
2737 foreach ($fieldRows as $k => $fieldRow) {
2738 settype($fieldRow, 'array');
2739 $fieldRow['Field'] = $fieldRow['name'];
2740 $ntype = $this->MySQLActualType($this->MetaType($fieldRow['type'], $tableName));
2741 $ntype .= $fieldRow['max_length'] != -1 ? ($ntype == 'INT' ? '(11)' : '(' . $fieldRow['max_length'] . ')') : '';
2742 $fieldRow['Type'] = strtolower($ntype);
2743 $fieldRow['Null'