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