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