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