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