Fixed bug #11108: DBAL wildly quotes fields and table names
authorXavier Perseguers <typo3@perseguers.ch>
Fri, 4 Sep 2009 14:59:24 +0000 (14:59 +0000)
committerXavier Perseguers <typo3@perseguers.ch>
Fri, 4 Sep 2009 14:59:24 +0000 (14:59 +0000)
git-svn-id: https://svn.typo3.org/TYPO3v4/Extensions/dbal/trunk@24017 735d13b6-9817-0410-8766-e36946ffe9aa

typo3/sysext/dbal/ChangeLog
typo3/sysext/dbal/class.ux_t3lib_db.php
typo3/sysext/dbal/class.ux_t3lib_sqlengine.php
typo3/sysext/dbal/class.ux_t3lib_sqlparser.php

index 614a182..3682554 100644 (file)
@@ -1,6 +1,7 @@
 2009-09-03  Xavier Perseguers  <typo3@perseguers.ch>
 
        * Fixed bug #11436: Unknown tables break Install-Tool (Thanks to David Bruchmann)
+       * Fixed bug #11108: DBAL wildly quotes fields and table names
 
 2008-02-04  Michael Stucki  <michael@typo3.org>
 
index b38dc9b..270ad5f 100644 (file)
@@ -32,6 +32,7 @@
  *
  * @author     Kasper Skaarhoj <kasper@typo3.com>
  * @author     Karsten Dambekalns <k.dambekalns@fishfarm.de>
+ * @author     Xavier Perseguers <typo3@perseguers.ch>
  */
 /**
  * [CLASS/FUNCTION INDEX of SCRIPT]
@@ -129,7 +130,8 @@ class ux_t3lib_DB extends t3lib_DB {
                                    'host' => '',       // Set by default (overridden)
                                    'database' => '',   // Set by default (overridden)
                                    'driver' => '',     // ONLY "adodb" type; eg. "mysql"
-                                   'sequenceStart' => 1        // ONLY "adodb", first number in sequences/serials/...
+                                   'sequenceStart' => 1,       // ONLY "adodb", first number in sequences/serials/...
+                                   'useNameQuote' => 0 // ONLY "adodb", whether to use NameQuote() method from ADOdb to quote names
                                )
            ),
        );
@@ -933,13 +935,13 @@ class ux_t3lib_DB extends t3lib_DB {
                $select_fields = $this->SQLparser->parseFieldList($select_fields);
                foreach($select_fields as $k => $v)     {
                        if($select_fields[$k]['field'] != '' && $select_fields[$k]['field'] != '*') {
-                               $select_fields[$k]['field'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$select_fields[$k]['field'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
+                               $select_fields[$k]['field'] = $this->quoteName($select_fields[$k]['field']);
                        }
                        if($select_fields[$k]['table'] != '') {
-                               $select_fields[$k]['table'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$select_fields[$k]['table'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
+                               $select_fields[$k]['table'] = $this->quoteName($select_fields[$k]['table']);
                        }
                        if($select_fields[$k]['as'] != '') {
-                               $select_fields[$k]['as'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$select_fields[$k]['as'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
+                               $select_fields[$k]['as'] = $this->quoteName($select_fields[$k]['as']);
                        }
                        if(isset($select_fields[$k]['func_content.']) && $select_fields[$k]['func_content.'][0]['func_content'] != '*'){
                                $select_fields[$k]['func_content.'][0]['func_content'] = $this->quoteFieldNames($select_fields[$k]['func_content.'][0]['func_content']);
@@ -962,16 +964,16 @@ class ux_t3lib_DB extends t3lib_DB {
 
                $from_table = $this->SQLparser->parseFromTables($from_table);
                foreach($from_table as $k => $v)        {
-                       $from_table[$k]['table'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$from_table[$k]['table'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
+                       $from_table[$k]['table'] = $this->quoteName($from_table[$k]['table']);
                        if($from_table[$k]['as'] != '') {
-                               $from_table[$k]['as'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$from_table[$k]['as'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
+                               $from_table[$k]['as'] = $this->quoteName($from_table[$k]['as']);
                        }
                        if (is_array($v['JOIN']))       {
-                               $from_table[$k]['JOIN']['withTable'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$from_table[$k]['JOIN']['withTable'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
-                               $from_table[$k]['JOIN']['ON'][0]['table'] = ($from_table[$k]['JOIN']['ON'][0]['table']) ? $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$from_table[$k]['JOIN']['ON'][0]['table'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote : '';
-                               $from_table[$k]['JOIN']['ON'][0]['field'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$from_table[$k]['JOIN']['ON'][0]['field'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
-                               $from_table[$k]['JOIN']['ON'][1]['table'] = ($from_table[$k]['JOIN']['ON'][1]['table']) ? $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$from_table[$k]['JOIN']['ON'][1]['table'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote : '';
-                               $from_table[$k]['JOIN']['ON'][1]['field'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$from_table[$k]['JOIN']['ON'][1]['field'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
+                               $from_table[$k]['JOIN']['withTable'] = $this->quoteName($from_table[$k]['JOIN']['withTable']);
+                               $from_table[$k]['JOIN']['ON'][0]['table'] = ($from_table[$k]['JOIN']['ON'][0]['table']) ? $this->quoteName($from_table[$k]['JOIN']['ON'][0]['table']) : '';
+                               $from_table[$k]['JOIN']['ON'][0]['field'] = $this->quoteName($from_table[$k]['JOIN']['ON'][0]['field']);
+                               $from_table[$k]['JOIN']['ON'][1]['table'] = ($from_table[$k]['JOIN']['ON'][1]['table']) ? $this->quoteName($from_table[$k]['JOIN']['ON'][1]['table']) : '';
+                               $from_table[$k]['JOIN']['ON'][1]['field'] = $this->quoteName($from_table[$k]['JOIN']['ON'][1]['field']);
                        }
                }
                return $this->SQLparser->compileFromTables($from_table);
@@ -1011,10 +1013,10 @@ class ux_t3lib_DB extends t3lib_DB {
                                $where_clause[$k]['sub'] = $this->_quoteWhereClause($where_clause[$k]['sub']);
                        } else {
                                if($where_clause[$k]['table'] != '') {
-                                       $where_clause[$k]['table'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$where_clause[$k]['table'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
+                                       $where_clause[$k]['table'] = $this->quoteName($where_clause[$k]['table']);
                                }
                                if(!is_numeric($where_clause[$k]['field'])) {
-                                       $where_clause[$k]['field'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$where_clause[$k]['field'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
+                                       $where_clause[$k]['field'] = $this->quoteName($where_clause[$k]['field']);
                                }
                        }
                        if ($where_clause[$k]['comparator'])    {
@@ -1040,9 +1042,9 @@ class ux_t3lib_DB extends t3lib_DB {
 
                $groupBy = $this->SQLparser->parseFieldList($groupBy);
                foreach($groupBy as $k => $v)   {
-                       $groupBy[$k]['field'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$groupBy[$k]['field'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
+                       $groupBy[$k]['field'] = $this->quoteName($groupBy[$k]['field']);
                        if($groupBy[$k]['table'] != '') {
-                               $groupBy[$k]['table'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$groupBy[$k]['table'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
+                               $groupBy[$k]['table'] = $this->quoteName($groupBy[$k]['table']);
                        }
                }
                return $this->SQLparser->compileFieldList($groupBy);
@@ -1060,9 +1062,9 @@ class ux_t3lib_DB extends t3lib_DB {
 
                $orderBy = $this->SQLparser->parseFieldList($orderBy);
                foreach($orderBy as $k => $v)   {
-                       $orderBy[$k]['field'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$orderBy[$k]['field'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
+                       $orderBy[$k]['field'] = $this->quoteName($orderBy[$k]['field']);
                        if($orderBy[$k]['table'] != '') {
-                               $orderBy[$k]['table'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$orderBy[$k]['table'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
+                               $orderBy[$k]['table'] = $this->quoteName($orderBy[$k]['table']);
                        }
                }
                return $this->SQLparser->compileFieldList($orderBy);
@@ -1117,6 +1119,24 @@ class ux_t3lib_DB extends t3lib_DB {
                return $str;
        }
 
+       /**
+        * Quotes an object name (table name, field, ...)
+        *
+        * @param       string          Object's name
+        * @param       string          Handler key
+        * @param       boolean         If method NameQuote() is not used, whether to use backticks instead of driver-specific quotes
+        * @return      string          Properly-quoted object's name
+        */
+       function quoteName($name, $handlerKey = NULL, $useBackticks = FALSE) {
+               $handlerKey = $handlerKey ? $handlerKey : $this->lastHandlerKey;
+               $useNameQuote = isset($this->handlerCfg[$handlerKey]['config']['useNameQuote']) ? $this->handlerCfg[$handlerKey]['config']['useNameQuote'] : FALSE;
+               if ($useNameQuote) {
+                       return $this->handlerInstance[$handlerKey]->DataDictionary->NameQuote($name);
+               } else {
+                       $quote = $useBackticks ? '`' : $this->handlerInstance[$handlerKey]->nameQuote;
+                       return $quote . $name . $quote;
+               }
+       }
 
        /**
         * Return MetaType for native field type (ADOdb only!)
index 24fc4fb..5cded2e 100644 (file)
@@ -121,7 +121,9 @@ class ux_t3lib_sqlengine extends t3lib_sqlengine {
                                $query = 'DROP TABLE'.($components['ifExists']?' IF EXISTS':'').' '.$components['TABLE'];
                                break;
                        case 'adodb':
-                               $query = $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE'])]->DataDictionary->DropTableSQL('`'.$components['TABLE'].'`');
+                               $handlerKey = $GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE']);
+                               $tableName = $GLOBALS['TYPO3_DB']->quoteName($components['TABLE'], $handlerKey, TRUE);
+                               $query = $GLOBALS['TYPO3_DB']->handlerInstance[$handlerKey]->DataDictionary->DropTableSQL($tableName);
                                break;
                }
 
@@ -147,8 +149,8 @@ class ux_t3lib_sqlengine extends t3lib_sqlengine {
                                $indexKeys = array();
 
                                foreach($components['FIELDS'] as $fN => $fCfg)  {
-                                               // the backticks get converted to the correct quote char automatically
-                                       $fieldsKeys[$fN] = '`'.$fN.'` '.$this->compileFieldCfg($fCfg['definition']);
+                                       $handlerKey = $GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE']);
+                                       $fieldsKeys[$fN] = $GLOBALS['TYPO3_DB']->quoteName($fN, $handlerKey, TRUE) . ' ' . $this->compileFieldCfg($fCfg['definition']);
                                }
 
                                if(isset($components['KEYS']) && is_array($components['KEYS'])) {
@@ -171,7 +173,8 @@ class ux_t3lib_sqlengine extends t3lib_sqlengine {
                                $tableOptions = array('postgres' => 'WITHOUT OIDS');
 
                                        // Fetch table/index generation query:
-                               $query = array_merge($GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->CreateTableSQL('`'.$components['TABLE'].'`',implode(','.chr(10), $fieldsKeys), $tableOptions), $indexKeys);
+                               $tableName = $GLOBALS['TYPO3_DB']->quoteName($components['TABLE'], NULL, TRUE);
+                               $query = array_merge($GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->CreateTableSQL($tableName, implode(',' . chr(10), $fieldsKeys), $tableOptions), $indexKeys);
                                break;
                }
 
@@ -185,12 +188,14 @@ class ux_t3lib_sqlengine extends t3lib_sqlengine {
                                $query[] = parent::compileALTERTABLE($components);
                                break;
                        case 'adodb':
+                               $tableName = $GLOBALS['TYPO3_DB']->quoteName($components['TABLE'], NULL, TRUE);
+                               $fieldName = $GLOBALS['TYPO3_DB']->quoteName($components['FIELD'], NULL, TRUE);
                                switch(strtoupper(str_replace(array(" ","\n","\r","\t"),'',$components['action'])))     {
                                        case 'ADD':
-                                               $query = $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->AddColumnSQL('`'.$components['TABLE'].'`','`'.$components['FIELD'].'` '.$this->compileFieldCfg($components['definition']));
+                                               $query = $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->AddColumnSQL($tableName, $fieldName . ' ' . $this->compileFieldCfg($components['definition']));
                                                break;
                                        case 'CHANGE':
-                                               $query = $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->AlterColumnSQL('`'.$components['TABLE'].'`','`'.$components['FIELD'].'` '.$this->compileFieldCfg($components['definition']));
+                                               $query = $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->AlterColumnSQL($tableName, $fieldName . ' ' . $this->compileFieldCfg($components['definition']));
                                                break;
                                        case 'DROP':
                                        case 'DROPKEY':
@@ -382,4 +387,4 @@ if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['ext/dbal/cl
        include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['ext/dbal/class.ux_t3lib_sqlengine.php']);
 }
 
-?>
\ No newline at end of file
+?>
index 226d693..368e6db 100644 (file)
@@ -121,7 +121,9 @@ class ux_t3lib_sqlparser extends t3lib_sqlparser {
                                $query = 'DROP TABLE'.($components['ifExists']?' IF EXISTS':'').' '.$components['TABLE'];
                                break;
                        case 'adodb':
-                               $query = $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE'])]->DataDictionary->DropTableSQL('`'.$components['TABLE'].'`');
+                               $handlerKey = $GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE']);
+                               $tableName = $GLOBALS['TYPO3_DB']->quoteName($components['TABLE'], $handlerKey, TRUE);
+                               $query = $GLOBALS['TYPO3_DB']->handlerInstance[$handlerKey]->DataDictionary->DropTableSQL($tableName);
                                break;
                }
 
@@ -147,8 +149,8 @@ class ux_t3lib_sqlparser extends t3lib_sqlparser {
                                $indexKeys = array();
 
                                foreach($components['FIELDS'] as $fN => $fCfg)  {
-                                               // the backticks get converted to the correct quote char automatically
-                                       $fieldsKeys[$fN] = $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE'])]->DataDictionary->nameQuote('`'.$fN.'`').' '.$this->compileFieldCfg($fCfg['definition']);
+                                       $handlerKey = $GLOBALS['TYPO3_DB']->handler_getFromTableList($components['TABLE']);
+                                       $fieldsKeys[$fN] = $GLOBALS['TYPO3_DB']->quoteName($fN, $handlerKey, TRUE) . ' ' . $this->compileFieldCfg($fCfg['definition']);
                                }
 
                                if(isset($components['KEYS']) && is_array($components['KEYS'])) {
@@ -171,7 +173,8 @@ class ux_t3lib_sqlparser extends t3lib_sqlparser {
                                $tableOptions = array('postgres' => 'WITHOUT OIDS');
 
                                        // Fetch table/index generation query:
-                               $query = array_merge($GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->CreateTableSQL('`'.$components['TABLE'].'`',implode(','.chr(10), $fieldsKeys), $tableOptions), $indexKeys);
+                               $tableName = $GLOBALS['TYPO3_DB']->quoteName($components['TABLE'], NULL, TRUE);
+                               $query = array_merge($GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->CreateTableSQL($tableName, implode(',' . chr(10), $fieldsKeys), $tableOptions), $indexKeys);
                                break;
                }
 
@@ -185,12 +188,14 @@ class ux_t3lib_sqlparser extends t3lib_sqlparser {
                                $query[] = parent::compileALTERTABLE($components);
                                break;
                        case 'adodb':
+                               $tableName = $GLOBALS['TYPO3_DB']->quoteName($components['TABLE'], NULL, TRUE);
+                               $fieldName = $GLOBALS['TYPO3_DB']->quoteName($components['FIELD'], NULL, TRUE);
                                switch(strtoupper(str_replace(array(" ","\n","\r","\t"),'',$components['action'])))     {
                                        case 'ADD':
-                                               $query = $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->AddColumnSQL('`'.$components['TABLE'].'`','`'.$components['FIELD'].'` '.$this->compileFieldCfg($components['definition']));
+                                               $query = $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->AddColumnSQL($tableName, $fieldName . ' ' . $this->compileFieldCfg($components['definition']));
                                                break;
                                        case 'CHANGE':
-                                               $query = $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->AlterColumnSQL('`'.$components['TABLE'].'`','`'.$components['FIELD'].'` '.$this->compileFieldCfg($components['definition']));
+                                               $query = $GLOBALS['TYPO3_DB']->handlerInstance[$GLOBALS['TYPO3_DB']->lastHandlerKey]->DataDictionary->AlterColumnSQL($tableName, $fieldName . ' ' . $this->compileFieldCfg($components['definition']));
                                                break;
                                        case 'DROP':
                                        case 'DROPKEY':
@@ -392,4 +397,4 @@ if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['ext/dbal/cl
        include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['ext/dbal/class.ux_t3lib_sqlparser.php']);
 }
 
-?>
\ No newline at end of file
+?>