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