[TASK] Fix CGL errors
[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 = array();
40
41 /**
42 * Configuration array, copied from TYPO3_CONF_VARS in constructor.
43 * @var array
44 */
45 public $mapping = array();
46
47 /**
48 * See manual
49 * @var array
50 */
51 protected $table2handlerKeys = array();
52
53 /**
54 * See manual
55 * @var array
56 */
57 public $handlerCfg = array(
58 '_DEFAULT' => array(
59 'type' => 'native',
60 'config' => array(
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 = array();
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 = array();
108
109 /**
110 * @var array
111 */
112 protected $resourceIdToTableNameMap = array();
113
114 /**
115 * @var array
116 */
117 protected $cache_handlerKeyFromTableList = array();
118
119 /**
120 * @var array
121 */
122 protected $cache_mappingFromTableList = array();
123
124 /**
125 * parsed SQL from standard DB dump file
126 * @var array
127 */
128 public $cache_autoIncFields = array();
129
130 /**
131 * @var array
132 */
133 public $cache_fieldType = array();
134
135 /**
136 * @var array
137 */
138 public $cache_primaryKeys = array();
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 = array(
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), array('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 array('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 = array();
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(array($this->lastQuery, $this->sql_error()));
594 }
595 if ($this->debug) {
596 $this->debugHandler('exec_INSERTquery', GeneralUtility::milliseconds() - $pt, array(
597 'handlerType' => $hType,
598 'args' => array($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 = array();
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(array($this->lastQuery, $this->sql_error()));
711 }
712 if ($this->debug) {
713 $this->debugHandler('exec_UPDATEquery', GeneralUtility::milliseconds() - $pt, array(
714 'handlerType' => $hType,
715 'args' => array($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(array($this->lastQuery, $this->sql_error()));
767 }
768 if ($this->debug) {
769 $this->debugHandler('exec_DELETEquery', GeneralUtility::milliseconds() - $pt, array(
770 'handlerType' => $hType,
771 'args' => array($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 = array();
802 $remappedParameters = array();
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(array($this->lastQuery, $this->sql_error()));
873 }
874 if ($this->debug) {
875 $data = array(
876 'handlerType' => $hType,
877 'args' => array($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(array($this->lastQuery, $this->sql_error()));
925 }
926 if ($this->debug) {
927 $this->debugHandler('exec_TRUNCATEquery', GeneralUtility::milliseconds() - $pt, array(
928 'handlerType' => $hType,
929 'args' => array($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 = array();
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 = array();
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 = array();
1035 }
1036 $blobFields = $clobFields = array();
1037 $nArr = array();
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 = array();
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 = array();
1111 foreach ($rows as $row) {
1112 $fields_values = array();
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 = array();
1145 $nArr = array();
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 = array();
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 = array();
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 array($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 = array())
1366 {
1367 $pt = $this->debug ? GeneralUtility::milliseconds() : 0;
1368 $precompiledParts = array();
1369 if ($this->queryCache) {
1370 $cacheKey = 'prepare_SELECTquery-' . \TYPO3\CMS\Dbal\QueryCache::getCacheKey(array(
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 = array(
1382 'args' => array($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 = array();
1395 $queryComponents = array();
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 = array(
1436 'args' => array($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 = array(
1460 'SELECT' => '',
1461 'FROM' => '',
1462 'WHERE' => '',
1463 'GROUPBY' => '',
1464 'ORDERBY' => '',
1465 'LIMIT' => '',
1466 'parameters' => array()
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 = array();
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'] = array(
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(array($this->lastQuery, $this->sql_error()));
1593 }
1594 if ($this->debug) {
1595 $data = array(
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 'IFNULL':
1802 // Intentional fallthrough
1803 case 'LOCATE':
1804 if ($where_clause[$k]['func']['table'] != '') {
1805 $where_clause[$k]['func']['table'] = $this->quoteName($v['func']['table']);
1806 }
1807 if ($where_clause[$k]['func']['field'] != '') {
1808 $where_clause[$k]['func']['field'] = $this->quoteName($v['func']['field']);
1809 }
1810 break;
1811 }
1812 } else {
1813 if ($where_clause[$k]['table'] != '') {
1814 $where_clause[$k]['table'] = $this->quoteName($where_clause[$k]['table']);
1815 }
1816 if (!is_numeric($where_clause[$k]['field'])) {
1817 $where_clause[$k]['field'] = $this->quoteName($where_clause[$k]['field']);
1818 }
1819 if (isset($where_clause[$k]['calc_table'])) {
1820 if ($where_clause[$k]['calc_table'] != '') {
1821 $where_clause[$k]['calc_table'] = $this->quoteName($where_clause[$k]['calc_table']);
1822 }
1823 if ($where_clause[$k]['calc_field'] != '') {
1824 $where_clause[$k]['calc_field'] = $this->quoteName($where_clause[$k]['calc_field']);
1825 }
1826 }
1827 }
1828 if ($where_clause[$k]['comparator']) {
1829 if (isset($v['value']['operator'])) {
1830 foreach ($where_clause[$k]['value']['args'] as $argK => $fieldDef) {
1831 $where_clause[$k]['value']['args'][$argK]['table'] = $this->quoteName($fieldDef['table']);
1832 $where_clause[$k]['value']['args'][$argK]['field'] = $this->quoteName($fieldDef['field']);
1833 }
1834 } else {
1835 // Detecting value type; list or plain:
1836 if (GeneralUtility::inList('NOTIN,IN', strtoupper(str_replace(array(' ', LF, CR, TAB), '', $where_clause[$k]['comparator'])))) {
1837 if (isset($v['subquery'])) {
1838 $where_clause[$k]['subquery'] = $this->quoteSELECTsubquery($v['subquery']);
1839 }
1840 } else {
1841 if (
1842 (!isset($where_clause[$k]['value'][1]) || $where_clause[$k]['value'][1] == '')
1843 && is_string($where_clause[$k]['value'][0]) && strstr($where_clause[$k]['value'][0], '.')
1844 ) {
1845 $where_clause[$k]['value'][0] = $this->quoteFieldNames($where_clause[$k]['value'][0]);
1846 } elseif ($this->runningADOdbDriver('mssql')) {
1847 $where_clause[$k]['value'][0] = substr($this->handlerInstance[$this->lastHandlerKey]->qstr($where_clause[$k]['value'][0]), 1, -1);
1848 }
1849 }
1850 }
1851 }
1852 }
1853 return $where_clause;
1854 }
1855
1856 /**
1857 * Quotes the field (and table) names within a group by clause with the quote
1858 * character suitable for the DB being used
1859 *
1860 * @param string $groupBy A group by clause that can by parsed by parseFieldList
1861 * @return string Usable group by clause with quoted field/table names
1862 */
1863 protected function quoteGroupBy($groupBy)
1864 {
1865 if ($groupBy === '') {
1866 return '';
1867 }
1868 if ($this->runningNative()) {
1869 return $groupBy;
1870 }
1871 $groupBy = $this->SQLparser->parseFieldList($groupBy);
1872 $groupBy = $this->_quoteGroupBy($groupBy);
1873 return $this->SQLparser->compileFieldList($groupBy);
1874 }
1875
1876 /**
1877 * Quotes field names in a SQL GROUP BY clause according to DB rules
1878 *
1879 * @param array $groupBy The parsed GROUP BY clause to quote
1880 * @return array
1881 * @see quoteGroupBy()
1882 */
1883 protected function _quoteGroupBy(array $groupBy)
1884 {
1885 foreach ($groupBy as $k => $v) {
1886 $groupBy[$k]['field'] = $this->quoteName($groupBy[$k]['field']);
1887 if ($groupBy[$k]['table'] != '') {
1888 $groupBy[$k]['table'] = $this->quoteName($groupBy[$k]['table']);
1889 }
1890 }
1891 return $groupBy;
1892 }
1893
1894 /**
1895 * Quotes the field (and table) names within an order by clause with the quote
1896 * character suitable for the DB being used
1897 *
1898 * @param string $orderBy An order by clause that can by parsed by parseFieldList
1899 * @return string Usable order by clause with quoted field/table names
1900 */
1901 protected function quoteOrderBy($orderBy)
1902 {
1903 if ($orderBy === '') {
1904 return '';
1905 }
1906 if ($this->runningNative()) {
1907 return $orderBy;
1908 }
1909 $orderBy = $this->SQLparser->parseFieldList($orderBy);
1910 $orderBy = $this->_quoteOrderBy($orderBy);
1911 return $this->SQLparser->compileFieldList($orderBy);
1912 }
1913
1914 /**
1915 * Quotes field names in a SQL ORDER BY clause according to DB rules
1916 *
1917 * @param array $orderBy The parsed ORDER BY clause to quote
1918 * @return array
1919 * @see quoteOrderBy()
1920 */
1921 protected function _quoteOrderBy(array $orderBy)
1922 {
1923 foreach ($orderBy as $k => $v) {
1924 if ($orderBy[$k]['table'] === '' && $v['field'] !== '' && ctype_digit($v['field'])) {
1925 continue;
1926 }
1927 $orderBy[$k]['field'] = $this->quoteName($orderBy[$k]['field']);
1928 if ($orderBy[$k]['table'] !== '') {
1929 $orderBy[$k]['table'] = $this->quoteName($orderBy[$k]['table']);
1930 }
1931 }
1932 return $orderBy;
1933 }
1934
1935 /**************************************
1936 *
1937 * Various helper functions
1938 *
1939 **************************************/
1940 /**
1941 * Escaping and quoting values for SQL statements.
1942 *
1943 * @param string $str Input string
1944 * @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!).
1945 * @param bool $allowNull Whether to allow NULL values
1946 * @return string Output string; Wrapped in single quotes and quotes in the string (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
1947 * @see quoteStr()
1948 */
1949 public function fullQuoteStr($str, $table, $allowNull = false)
1950 {
1951 if ($allowNull && $str === null) {
1952 return 'NULL';
1953 }
1954 return '\'' . $this->quoteStr($str, $table) . '\'';
1955 }
1956
1957 /**
1958 * Substitution for PHP function "addslashes()"
1959 * Use this function instead of the PHP addslashes() function when you build queries - this will prepare your code for DBAL.
1960 * 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()!
1961 *
1962 * @param string $str Input string
1963 * @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!).
1964 * @throws \RuntimeException
1965 * @return string Output string; Quotes (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
1966 * @see quoteStr()
1967 */
1968 public function quoteStr($str, $table)
1969 {
1970 $this->lastHandlerKey = $this->handler_getFromTableList($table);
1971 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
1972 case 'native':
1973 if ($this->handlerInstance[$this->lastHandlerKey]['link']) {
1974 if (!$this->isConnected()) {
1975 $this->connectDB();
1976 }
1977 $str = $this->handlerInstance[$this->lastHandlerKey]['link']->real_escape_string($str);
1978 } else {
1979 // link may be null when unit testing DBAL
1980 $str = str_replace('\'', '\\\'', $str);
1981 }
1982 break;
1983 case 'adodb':
1984 if (!$this->isConnected()) {
1985 $this->connectDB();
1986 }
1987 $str = substr($this->handlerInstance[$this->lastHandlerKey]->qstr($str), 1, -1);
1988 break;
1989 case 'userdefined':
1990 $str = $this->handlerInstance[$this->lastHandlerKey]->quoteStr($str);
1991 break;
1992 default:
1993 throw new \RuntimeException('No handler found!!!', 1310027655);
1994 }
1995 return $str;
1996 }
1997
1998 /**
1999 * Quotes an object name (table name, field, ...)
2000 *
2001 * @param string $name Object's name
2002 * @param string $handlerKey Handler key
2003 * @param bool $useBackticks If method NameQuote() is not used, whether to use backticks instead of driver-specific quotes
2004 * @return string Properly-quoted object's name
2005 */
2006 public function quoteName($name, $handlerKey = null, $useBackticks = false)
2007 {
2008 $handlerKey = $handlerKey ? $handlerKey : $this->lastHandlerKey;
2009 $useNameQuote = isset($this->handlerCfg[$handlerKey]['config']['useNameQuote']) ? $this->handlerCfg[$handlerKey]['config']['useNameQuote'] : false;
2010 if ($useNameQuote) {
2011 // Sometimes DataDictionary is not properly instantiated
2012 if (!is_object($this->handlerInstance[$handlerKey]->DataDictionary)) {
2013 $this->handlerInstance[$handlerKey]->DataDictionary = NewDataDictionary($this->handlerInstance[$handlerKey]);
2014 }
2015 return $this->handlerInstance[$handlerKey]->DataDictionary->NameQuote($name);
2016 } else {
2017 $quote = $useBackticks ? '`' : $this->handlerInstance[$handlerKey]->nameQuote;
2018 return $quote . $name . $quote;
2019 }
2020 }
2021
2022 /**
2023 * Return MetaType for native field type (ADOdb only!)
2024 *
2025 * @param string $type Native type as reported by admin_get_fields()
2026 * @param string $table Table name for which query type string. Important for detection of DBMS handler of the query!
2027 * @param int $maxLength
2028 * @throws \RuntimeException
2029 * @return string Meta type (currently ADOdb syntax only, http://phplens.com/lens/adodb/docs-adodb.htm#metatype)
2030 * @deprecated since TYPO3 CMS 7, will be removed in TYPO3 CMS 8, use getMetadata() instead
2031 */
2032 public function MetaType($type, $table, $maxLength = -1)
2033 {
2034 GeneralUtility::logDeprecatedFunction();
2035 return $this->getMetadata($type, $table, 'dummyFieldToBypassCache', $maxLength);
2036 }
2037
2038 /**
2039 * Return Metadata for native field type (ADOdb only!)
2040 *
2041 * @param string $type Native type as reported by admin_get_fields()
2042 * @param string $table Table name for which the type is queried. Important for detection of DBMS handler of the query!
2043 * @param string $field Field name for which the type is queried. Important for accessing the field information cache.
2044 * @param int $maxLength
2045 * @throws \RuntimeException
2046 * @return string Meta type (currently ADOdb syntax only, http://phplens.com/lens/adodb/docs-adodb.htm#metatype)
2047 */
2048 public function getMetadata($type, $table, $field, $maxLength = -1)
2049 {
2050 $this->lastHandlerKey = $this->handler_getFromTableList($table);
2051 $str = '';
2052 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
2053 case 'native':
2054 $str = $type;
2055 break;
2056 case 'adodb':
2057 if (!empty($this->cache_fieldType[$table][$field])) {
2058 $str = $this->cache_fieldType[$table][$field]['metaType'];
2059 } else {
2060 $rs = $this->handlerInstance[$this->lastHandlerKey]->SelectLimit('SELECT * FROM ' . $this->quoteFromTables($table), 1);
2061 $str = $rs->MetaType($type, $maxLength);
2062 }
2063 break;
2064 case 'userdefined':
2065 $str = $this->handlerInstance[$this->lastHandlerKey]->MetaType($str, $table, $maxLength);
2066 break;
2067 default:
2068 throw new \RuntimeException('No handler found!!!', 1310027685);
2069 }
2070 return $str;
2071 }
2072
2073 /**
2074 * Return MetaType for native MySQL field type
2075 *
2076 * @param string $t native type as reported as in mysqldump files
2077 * @return string Meta type (currently ADOdb syntax only, http://phplens.com/lens/adodb/docs-adodb.htm#metatype)
2078 * @deprecated since TYPO3 CMS 7, will be removed in TYPO3 CMS 8
2079 */
2080 public function MySQLMetaType($t)
2081 {
2082 GeneralUtility::logDeprecatedFunction();
2083 return $this->dbmsSpecifics->getMetaFieldType($t);
2084 }
2085
2086 /**
2087 * Return actual MySQL type for meta field type
2088 *
2089 * @param string $meta Meta type (currenly ADOdb syntax only, http://phplens.com/lens/adodb/docs-adodb.htm#metatype)
2090 * @return string Native type as reported as in mysqldump files, uppercase
2091 * @deprecated since TYPO3 CMS 7, will be removed in TYPO3 CMS 8
2092 */
2093 public function MySQLActualType($meta)
2094 {
2095 GeneralUtility::logDeprecatedFunction();
2096 return $this->dbmsSpecifics->getNativeFieldType($meta);
2097 }
2098
2099 /**
2100 * Update the id information for the last inserted record
2101 *
2102 * @param string $table
2103 * @param array $fieldValues
2104 * @return null|int
2105 */
2106 protected function updateLastInsertId($table, array $fieldValues)
2107 {
2108 if ($table === 'tx_dbal_debuglog') {
2109 return null;
2110 }
2111 $newId = null;
2112 if (isset($fieldValues[$this->cache_autoIncFields[$table]])) {
2113 $newId = $fieldValues[$this->cache_autoIncFields[$table]];
2114 } elseif ($this->handlerInstance[$this->lastHandlerKey]->hasInsertID && !empty($this->cache_autoIncFields[$table])) {
2115 // The table is able to retrieve the ID of the last insert
2116 $newId = $this->handlerInstance[$this->lastHandlerKey]->Insert_ID($table, $this->cache_autoIncFields[$table]);
2117 }
2118 if ($newId !== null) {
2119 $this->handlerInstance[$this->lastHandlerKey]->last_insert_id = $newId;
2120 }
2121 return $newId;
2122 }
2123
2124 /*********************************************
2125 *
2126 * SqlSchemaMigrationService helper functions
2127 *
2128 *********************************************/
2129 /**
2130 * Remove the index prefix length information from columns in an index definition.
2131 * Partial indexes based on a prefix are not supported by all databases.
2132 *
2133 * @param string $indexSQL
2134 * @return string
2135 */
2136 public function getEquivalentIndexDefinition($indexSQL)
2137 {
2138 if ($this->dbmsSpecifics->specificExists(Specifics\AbstractSpecifics::PARTIAL_STRING_INDEX) && (bool)$this->dbmsSpecifics->getSpecific(Specifics\AbstractSpecifics::PARTIAL_STRING_INDEX)) {
2139 return $indexSQL;
2140 }
2141
2142 $strippedIndexSQL = preg_replace_callback(
2143 '/\A([^(]+)\((.*)\)\Z/',
2144 function ($matches) {
2145 return $matches[1] . '(' . preg_replace('/\((\d+)\)/', '', $matches[2]) . ')';
2146 },
2147 $indexSQL
2148 );
2149
2150 return $strippedIndexSQL === null ? $indexSQL : $strippedIndexSQL;
2151 }
2152
2153 /**
2154 * Convert the native MySQL Field type to the closest matching equivalent field type supported by the DBMS.
2155 * INTEGER and TINYTEXT colums need to be further processed due to MySQL limitations / non-standard features.
2156 *
2157 * @param string $fieldSQL
2158 * @return string
2159 */
2160 public function getEquivalentFieldDefinition($fieldSQL)
2161 {
2162 if (!preg_match('/^([a-z0-9]+)(\(([^\)]+)\))?(.*)/', $fieldSQL, $components)) {
2163 return $fieldSQL;
2164 }
2165
2166 $metaType = $this->dbmsSpecifics->getMetaFieldType($components[1]);
2167 $replacementType = $this->dbmsSpecifics->getNativeFieldType($metaType);
2168 $replacementLength = $components[2];
2169 $replacementExtra = '';
2170
2171 // MySQL INT types support a display length that has no effect on the
2172 // actual range of values that can be stored, normalize to the default
2173 // display length returned by DBAL.
2174 if (substr($metaType, 0, 1) === 'I') {
2175 $replacementLength = $this->dbmsSpecifics->getNativeFieldLength($replacementType, $components[3]);
2176 }
2177
2178 // MySQL TINYTEXT is equivalent to VARCHAR(255) DEFAULT NULL. MySQL TEXT
2179 // columns can not have a default value in contrast to VARCHAR, so the
2180 // `default NULL` gets appended to avoid false-positive schema changes.
2181 if ($components[1] === 'tinytext') {
2182 $replacementLength = '(255)';
2183 if (false !== stripos($components[0], ' NOT NULL')) {
2184 $replacementExtra = ' default \'\'';
2185 } else {
2186 $replacementExtra = ' default NULL';
2187 }
2188 }
2189
2190 return str_replace($components[1] . $components[2], strtolower($replacementType) . $replacementLength, $components[0]) . $replacementExtra;
2191 }
2192
2193 /**************************************
2194 *
2195 * SQL wrapper functions (Overriding parent methods)
2196 * (For use in your applications)
2197 *
2198 **************************************/
2199 /**
2200 * Returns the error status on the last query() execution
2201 *
2202 * @return string MySQLi error string.
2203 */
2204 public function sql_error()
2205 {
2206 $output = '';
2207 switch ($this->handlerCfg[$this->lastHandlerKey]['type']) {
2208 case 'native':
2209 $output = $this->handlerInstance[$this->lastHandlerKey]['link']->error;
2210 break;
2211 case 'adodb':
2212 $output = $this->handlerInstance[$this->lastHandlerKey]->ErrorMsg();
2213 break;
2214 case 'userdefined':
2215 $output = $this->handlerInstance[$this->lastHandlerKey]->sql_error();
2216 break;
2217 }
2218 return $output;
2219 }
2220
2221 /**
2222 * Returns the error number on the last query() execution
2223 *
2224 * @return int MySQLi error number
2225 */
2226 public function sql_errno()
2227 {
2228 $output = 0;
2229 switch ($this->handlerCfg[$this->lastHandlerKey]['type']) {
2230 case 'native':
2231 $output = $this->handlerInstance[$this->lastHandlerKey]['link']->errno;
2232 break;
2233 case 'adodb':
2234 $output = $this->handlerInstance[$this->lastHandlerKey]->ErrorNo();
2235 break;
2236 case 'userdefined':
2237 $output = $this->handlerInstance[$this->lastHandlerKey]->sql_errno();
2238 break;
2239 }
2240 return $output;
2241 }
2242
2243 /**
2244 * Returns the number of selected rows.
2245 *
2246 * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
2247 * @return int Number of resulting rows
2248 */
2249 public function sql_num_rows($res)
2250 {
2251 if ($res === false) {
2252 return false;
2253 }
2254 $handlerType = $this->determineHandlerType($res);
2255 $output = 0;
2256 switch ($handlerType) {
2257 case 'native':
2258 $output = $res->num_rows;
2259 break;
2260 case 'adodb':
2261 $output = method_exists($res, 'RecordCount') ? $res->RecordCount() : 0;
2262 break;
2263 case 'userdefined':
2264 $output = $res->sql_num_rows();
2265 break;
2266 }
2267 return $output;
2268 }
2269
2270 /**
2271 * Returns an associative array that corresponds to the fetched row, or FALSE if there are no more rows.
2272 * MySQLi fetch_assoc() wrapper function
2273 *
2274 * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
2275 * @return array|boolean Associative array of result row.
2276 */
2277 public function sql_fetch_assoc($res)
2278 {
2279 $tableList = '';
2280 $output = false;
2281 switch ($this->determineHandlerType($res)) {
2282 case 'native':
2283 $output = $res->fetch_assoc();
2284 $key = serialize($res);
2285 $tableList = $this->resourceIdToTableNameMap[$key];
2286 unset($this->resourceIdToTableNameMap[$key]);
2287 // Reading list of tables from SELECT query:
2288 break;
2289 case 'adodb':
2290 // Check if method exists for the current $res object.
2291 // If a table exists in TCA but not in the db, an error
2292 // occurred because $res is not a valid object.
2293 if (method_exists($res, 'FetchRow')) {
2294 $output = $res->FetchRow();
2295 $tableList = $res->TYPO3_DBAL_tableList;
2296 // Reading list of tables from SELECT query:
2297 // Removing all numeric/integer keys.
2298 // A workaround because in ADOdb we would need to know what we want before executing the query...
2299 // MSSQL does not support ADODB_FETCH_BOTH and always returns an assoc. array instead. So
2300 // we don't need to remove anything.
2301 if (is_array($output)) {
2302 if ($this->runningADOdbDriver('mssql')) {
2303 // MSSQL does not know such thing as an empty string. So it returns one space instead, which we must fix.
2304 foreach ($output as $key => $value) {
2305 if ($value === ' ') {
2306 $output[$key] = '';
2307 }
2308 }
2309 } else {
2310 foreach ($output as $key => $value) {
2311 if (is_integer($key)) {
2312 unset($output[$key]);
2313 }
2314 }
2315 }
2316 }
2317 }
2318 break;
2319 case 'userdefined':
2320 $output = $res->sql_fetch_assoc();
2321 $tableList = $res->TYPO3_DBAL_tableList;
2322 // Reading list of tables from SELECT query:
2323 break;
2324 }
2325 // Table/Fieldname mapping:
2326 if (is_array($output)) {
2327 if ($tables = $this->map_needMapping($tableList, true)) {
2328 $output = $this->map_assocArray($output, $tables, 1);
2329 }
2330 }
2331 if ($output === null) {
2332 // Needed for compatibility
2333 $output = false;
2334 }
2335 // Return result:
2336 return $output;
2337 }
2338
2339 /**
2340 * Returns an array that corresponds to the fetched row, or FALSE if there are no more rows.
2341 * The array contains the values in numerical indices.
2342 * MySQLi fetch_row() wrapper function
2343 *
2344 * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
2345 * @return array|boolean Array with result rows.
2346 */
2347 public function sql_fetch_row($res)
2348 {
2349 $output = false;
2350 switch ($this->determineHandlerType($res)) {
2351 case 'native':
2352 $output = $res->fetch_row();
2353 if ($output === null) {
2354 // Needed for compatibility
2355 $output = false;
2356 }
2357 break;
2358 case 'adodb':
2359 // Check if method exists for the current $res object.
2360 // If a table exists in TCA but not in the db, an error
2361 // occurred because $res is not a valid object.
2362 if (method_exists($res, 'FetchRow')) {
2363 $output = $res->FetchRow();
2364 // Removing all assoc. keys.
2365 // A workaround because in ADOdb we would need to know what we want before executing the query...
2366 // MSSQL does not support ADODB_FETCH_BOTH and always returns an assoc. array instead. So
2367 // we need to convert resultset.
2368 if (is_array($output)) {
2369 $keyIndex = 0;
2370 foreach ($output as $key => $value) {
2371 unset($output[$key]);
2372 if (is_integer($key) || $this->runningADOdbDriver('mssql')) {
2373 $output[$keyIndex] = $value;
2374 if ($value === ' ') {
2375 // MSSQL does not know such thing as an empty string. So it returns one space instead, which we must fix.
2376 $output[$keyIndex] = '';
2377 }
2378 $keyIndex++;
2379 }
2380 }
2381 }
2382 }
2383 break;
2384 case 'userdefined':
2385 $output = $res->sql_fetch_row();
2386 break;
2387 }
2388 if ($output === null) {
2389 // Needed for compatibility
2390 $output = false;
2391 }
2392 return $output;
2393 }
2394
2395 /**
2396 * Free result memory
2397 * free_result() wrapper function
2398 *
2399 * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
2400 * @return bool Returns TRUE on success or FALSE on failure.
2401 */
2402 public function sql_free_result($res)
2403 {
2404 if ($res === false) {
2405 return false;
2406 }
2407 $output = true;
2408 switch ($this->determineHandlerType($res)) {
2409 case 'native':
2410 $res->free();
2411 break;
2412 case 'adodb':
2413 if (method_exists($res, 'Close')) {
2414 $res->Close();
2415 unset($res);
2416 $output = true;
2417 } else {
2418 $output = false;
2419 }
2420 break;
2421 case 'userdefined':
2422 unset($res);
2423 break;
2424 }
2425 return $output;
2426 }
2427
2428 /**
2429 * Determine handler type by result set
2430 *
2431 * @param bool|\mysqli_result|object $res MySQLi result set / DBAL Object
2432 * @return bool|string
2433 */
2434 protected function determineHandlerType($res)
2435 {
2436 if (is_object($res) && !$res instanceof \mysqli_result) {
2437 $handlerType = $res->TYPO3_DBAL_handlerType;
2438 } elseif ($res instanceof \mysqli_result) {
2439 $handlerType = 'native';
2440 } else {
2441 $handlerType = false;
2442 }
2443 return $handlerType;
2444 }
2445
2446 /**
2447 * Get the ID generated from the previous INSERT operation
2448 *
2449 * @return int The uid of the last inserted record.
2450 */
2451 public function sql_insert_id()
2452 {
2453 $output = 0;
2454 switch ($this->handlerCfg[$this->lastHandlerKey]['type']) {
2455 case 'native':
2456 $output = $this->handlerInstance[$this->lastHandlerKey]['link']->insert_id;
2457 break;
2458 case 'adodb':
2459 $output = $this->handlerInstance[$this->lastHandlerKey]->last_insert_id;
2460 break;
2461 case 'userdefined':
2462 $output = $this->handlerInstance[$this->lastHandlerKey]->sql_insert_id();
2463 break;
2464 }
2465 return $output;
2466 }
2467
2468 /**
2469 * Returns the number of rows affected by the last INSERT, UPDATE or DELETE query
2470 *
2471 * @return int Number of rows affected by last query
2472 */
2473 public function sql_affected_rows()
2474 {
2475 $output = 0;
2476 switch ($this->handlerCfg[$this->lastHandlerKey]['type']) {
2477 case 'native':
2478 $output = $this->handlerInstance[$this->lastHandlerKey]['link']->affected_rows;
2479 break;
2480 case 'adodb':
2481 $output = $this->handlerInstance[$this->lastHandlerKey]->Affected_Rows();
2482 break;
2483 case 'userdefined':
2484 $output = $this->handlerInstance[$this->lastHandlerKey]->sql_affected_rows();
2485 break;
2486 }
2487 return $output;
2488 }
2489
2490 /**
2491 * Move internal result pointer
2492 *
2493 * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
2494 * @param int $seek Seek result number.
2495 * @return bool Returns TRUE on success or FALSE on failure.
2496 */
2497 public function sql_data_seek($res, $seek)
2498 {
2499 $output = true;
2500 switch ($this->determineHandlerType($res)) {
2501 case 'native':
2502 $output = $res->data_seek($seek);
2503 break;
2504 case 'adodb':
2505 $output = $res->Move($seek);
2506 break;
2507 case 'userdefined':
2508 $output = $res->sql_data_seek($seek);
2509 break;
2510 }
2511 return $output;
2512 }
2513
2514 /**
2515 * Get the type of the specified field in a result
2516 *
2517 * If the first parameter is a string, it is used as table name for the lookup.
2518 *
2519 * @param string $table MySQL result pointer (of SELECT query) / DBAL object / table name
2520 * @param int $field Field index. In case of ADOdb a string (field name!)
2521 * @return string Returns the type of the specified field index
2522 */
2523 public function sql_field_metatype($table, $field)
2524 {
2525 // If $table and/or $field are mapped, use the original names instead
2526 foreach ($this->mapping as $tableName => $tableMapInfo) {
2527 if (isset($tableMapInfo['mapFieldNames'])) {
2528 foreach ($tableMapInfo['mapFieldNames'] as $fieldName => $fieldMapInfo) {
2529 if ($fieldMapInfo === $field) {
2530 // Field name is mapped => use original name
2531 $field = $fieldName;
2532 }
2533 }
2534 }
2535 }
2536 return $this->cache_fieldType[$table][$field]['metaType'];
2537 }
2538
2539 /**
2540 * Get the type of the specified field in a result
2541 * mysql_field_type() wrapper function
2542 *
2543 * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
2544 * @param int $pointer Field index.
2545 * @return string Returns the name of the specified field index, or FALSE on error
2546 */
2547 public function sql_field_type($res, $pointer)
2548 {
2549 if ($res === null) {
2550 debug(array('no res in sql_field_type!'));
2551 return 'text';
2552 } elseif (is_string($res)) {
2553 if ($res === 'tx_dbal_debuglog') {
2554 return 'text';
2555 }
2556 $handlerType = 'adodb';
2557 } else {
2558 $handlerType = $this->determineHandlerType($res);
2559 }
2560 $output = '';
2561 switch ($handlerType) {
2562 case 'native':
2563 $metaInfo = $res->fetch_field_direct($pointer);
2564 if ($metaInfo) {
2565 $output = $this->mysqlDataTypeMapping[$metaInfo->type];
2566 } else {
2567 $output = '';
2568 }
2569 break;
2570 case 'adodb':
2571 if (is_string($pointer)) {
2572 $output = $this->cache_fieldType[$res][$pointer]['type'];
2573 }
2574 break;
2575 case 'userdefined':
2576 $output = $res->sql_field_type($pointer);
2577 break;
2578 }
2579 return $output;
2580 }
2581
2582 /**********
2583 *
2584 * Legacy functions, bound to _DEFAULT handler. (Overriding parent methods)
2585 * Deprecated or still experimental.
2586 *
2587 **********/
2588 /**
2589 * Executes query
2590 *
2591 * EXPERIMENTAL - This method will make its best to handle the query correctly
2592 * but if it cannot, it will simply pass the query to DEFAULT handler.
2593 *
2594 * You should use exec_* function from this class instead!
2595 * If you don't, anything that does not use the _DEFAULT handler will probably break!
2596 *
2597 * MySQLi query() wrapper function
2598 * Beware: Use of this method should be avoided as it is experimentally supported by DBAL. You should consider
2599 * using exec_SELECTquery() and similar methods instead.
2600 *
2601 * @param string $query Query to execute
2602 * @return bool|\mysqli_result|object MySQLi result object / DBAL object
2603 */
2604 public function sql_query($query)
2605 {
2606 $globalConfig = unserialize($GLOBALS['TYPO3_CONF_VARS']['EXT']['extConf']['dbal']);
2607 if ($globalConfig['sql_query.']['passthrough']) {
2608 return parent::sql_query($query);
2609 }
2610 // This method is heavily used by Extbase, try to handle it with DBAL-native methods
2611 $queryParts = $this->SQLparser->parseSQL($query);
2612 if (is_array($queryParts) && GeneralUtility::inList('SELECT,UPDATE,INSERT,DELETE', $queryParts['type'])) {
2613 return $this->exec_query($queryParts);
2614 }
2615 $sqlResult = null;
2616 switch ($this->handlerCfg['_DEFAULT']['type']) {
2617 case 'native':
2618 if (!$this->isConnected()) {
2619 $this->connectDB();
2620 }
2621 $sqlResult = $this->handlerInstance['_DEFAULT']['link']->query($query);
2622 break;
2623 case 'adodb':
2624 $sqlResult = $this->handlerInstance['_DEFAULT']->Execute($query);
2625 $sqlResult->TYPO3_DBAL_handlerType = 'adodb';
2626 break;
2627 case 'userdefined':
2628 $sqlResult = $this->handlerInstance['_DEFAULT']->sql_query($query);
2629 $sqlResult->TYPO3_DBAL_handlerType = 'userdefined';
2630 break;
2631 }
2632 $this->lastHandlerKey = '_DEFAULT';
2633 if ($this->printErrors && $this->sql_error()) {
2634 debug(array($this->lastQuery, $this->sql_error()));
2635 }
2636 return $sqlResult;
2637 }
2638
2639 /**
2640 * Open a (persistent) connection to a MySQL server
2641 *
2642 * @return bool|void
2643 */
2644 public function sql_pconnect()
2645 {
2646 return $this->handler_init('_DEFAULT');
2647 }
2648
2649 /**
2650 * Select a SQL database
2651 *
2652 * @return bool Returns TRUE on success or FALSE on failure.
2653 */
2654 public function sql_select_db()
2655 {
2656 $databaseName = $this->handlerCfg[$this->lastHandlerKey]['config']['database'];
2657 $ret = true;
2658 if ((string)$this->handlerCfg[$this->lastHandlerKey]['type'] === 'native') {
2659 $ret = $this->handlerInstance[$this->lastHandlerKey]['link']->select_db($databaseName);
2660 }
2661 if (!$ret) {
2662 GeneralUtility::sysLog(
2663 'Could not select MySQL database ' . $databaseName . ': ' . $this->sql_error(),
2664 'core',
2665 GeneralUtility::SYSLOG_SEVERITY_FATAL
2666 );
2667 }
2668 return $ret;
2669 }
2670
2671 /**************************************
2672 *
2673 * SQL admin functions
2674 * (For use in the Install Tool and Extension Manager)
2675 *
2676 **************************************/
2677 /**
2678 * Listing databases from current MySQL connection. NOTICE: It WILL try to select those databases and thus break selection of current database.
2679 * This is only used as a service function in the (1-2-3 process) of the Install Tool.
2680 * In any case a lookup should be done in the _DEFAULT handler DBMS then.
2681 * Use in Install Tool only!
2682 *
2683 * @return array Each entry represents a database name
2684 * @throws \RuntimeException
2685 */
2686 public function admin_get_dbs()
2687 {
2688 $dbArr = array();
2689 $this->lastHandlerKey = '_DEFAULT';
2690 switch ($this->handlerCfg['_DEFAULT']['type']) {
2691 case 'native':
2692 /** @var \mysqli_result $db_list */
2693 $db_list = $this->query("SELECT SCHEMA_NAME FROM information_schema.SCHEMATA");
2694 $oldDb = $this->handlerCfg[$this->lastHandlerKey]['config']['database'];
2695 while ($row = $db_list->fetch_object()) {
2696 $this->handlerCfg[$this->lastHandlerKey]['config']['database'] = $row->SCHEMA_NAME;
2697 if ($this->sql_select_db()) {
2698 $dbArr[] = $row->SCHEMA_NAME;
2699 }
2700 }
2701 $this->handlerCfg[$this->lastHandlerKey]['config']['database'] = $oldDb;
2702 $db_list->free();
2703 break;
2704 case 'adodb':
2705 // check needed for install tool - otherwise it will just die because the call to
2706 // MetaDatabases is done on a stdClass instance
2707 if (method_exists($this->handlerInstance['_DEFAULT'], 'MetaDatabases')) {
2708 $sqlDBs = $this->handlerInstance['_DEFAULT']->MetaDatabases();
2709 if (is_array($sqlDBs)) {
2710 foreach ($sqlDBs as $k => $theDB) {
2711 $dbArr[] = $theDB;
2712 }
2713 }
2714 }
2715 break;
2716 case 'userdefined':
2717 $dbArr = $this->handlerInstance['_DEFAULT']->admin_get_tables();
2718 break;
2719 }
2720 return $dbArr;
2721 }
2722
2723 /**
2724 * Returns the list of tables from the default database, TYPO3_db (quering the DBMS)
2725 * In a DBAL this method should 1) look up all tables from the DBMS of
2726 * the _DEFAULT handler and then 2) add all tables *configured* to be managed by other handlers
2727 *
2728 * @return array Array with tablenames as key and arrays with status information as value
2729 */
2730 public function admin_get_tables()
2731 {
2732 $whichTables = array();
2733 // Getting real list of tables:
2734 switch ($this->handlerCfg['_DEFAULT']['type']) {
2735 case 'native':
2736 $tables_result = $this->query('SHOW TABLE STATUS FROM `' . TYPO3_db . '`');
2737 if (!$this->sql_error()) {
2738 while ($theTable = $this->sql_fetch_assoc($tables_result)) {
2739 $whichTables[$theTable['Name']] = $theTable;
2740 }
2741 }
2742 $tables_result->free();
2743 break;
2744 case 'adodb':
2745 // check needed for install tool - otherwise it will just die because the call to
2746 // MetaTables is done on a stdClass instance
2747 if (method_exists($this->handlerInstance['_DEFAULT'], 'MetaTables')) {
2748 $sqlTables = $this->handlerInstance['_DEFAULT']->MetaTables('TABLES');
2749 foreach ($sqlTables as $k => $theTable) {
2750 if (preg_match('/BIN\\$/', $theTable)) {
2751 // Skip tables from the Oracle 10 Recycle Bin
2752 continue;
2753 }
2754 $whichTables[$theTable] = array('Name' => $theTable);
2755 }
2756 }
2757 break;
2758 case 'userdefined':
2759 $whichTables = $this->handlerInstance['_DEFAULT']->admin_get_tables();
2760 break;
2761 }
2762 // Check mapping:
2763 if (is_array($this->mapping) && !empty($this->mapping)) {
2764 // Mapping table names in reverse, first getting list of real table names:
2765 $tMap = array();
2766 foreach ($this->mapping as $tN => $tMapInfo) {
2767 if (isset($tMapInfo['mapTableName'])) {
2768 $tMap[$tMapInfo['mapTableName']] = $tN;
2769 }
2770 }
2771 // Do mapping:
2772 $newList = array();
2773 foreach ($whichTables as $tN => $tDefinition) {
2774 if (isset($tMap[$tN])) {
2775 $tN = $tMap[$tN];
2776 $tDefinition = array('Name' => $tN);
2777 }
2778 $newList[$tN] = $tDefinition;
2779 }
2780 $whichTables = $newList;
2781 }
2782 // Adding tables configured to reside in other DBMS (handler by other handlers than the default):
2783 if (is_array($this->table2handlerKeys)) {
2784 foreach ($this->table2handlerKeys as $key => $handlerKey) {
2785 $whichTables[$key] = array('Name' => $key);
2786 }
2787 }
2788 return $whichTables;
2789 }
2790
2791 /**
2792 * Returns information about each field in the $table (quering the DBMS)
2793 * In a DBAL this should look up the right handler for the table and return compatible information
2794 * This function is important not only for the Install Tool but probably for
2795 * DBALs as well since they might need to look up table specific information
2796 * in order to construct correct queries. In such cases this information should
2797 * probably be cached for quick delivery.
2798 *
2799 * @param string $tableName Table name
2800 * @return array Field information in an associative array with fieldname => field row
2801 */
2802 public function admin_get_fields($tableName)
2803 {
2804 $output = array();
2805 // Do field mapping if needed:
2806 $ORIG_tableName = $tableName;
2807 if ($tableArray = $this->map_needMapping($tableName)) {
2808 // Table name:
2809 if ($this->mapping[$tableName]['mapTableName']) {
2810 $tableName = $this->mapping[$tableName]['mapTableName'];
2811 }
2812 }
2813 // Find columns
2814 $this->lastHandlerKey = $this->handler_getFromTableList($tableName);
2815 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
2816 case 'native':
2817 /** @var \mysqli_result $columns_res */
2818 $columns_res = $this->query('SHOW columns FROM ' . $tableName);
2819 while ($fieldRow = $columns_res->fetch_assoc()) {
2820 $output[$fieldRow['Field']] = $fieldRow;
2821 }
2822 $columns_res->free();
2823 break;
2824 case 'adodb':
2825 $fieldRows = $this->handlerInstance[$this->lastHandlerKey]->MetaColumns($tableName, false);
2826 if (is_array($fieldRows)) {
2827 foreach ($fieldRows as $k => $fieldRow) {