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