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