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