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