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