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