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