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