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