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