[TASK] Ensure consistent handling of BLOBs 83/50883/8
authorMorton Jonuschat <m.jonuschat@mojocode.de>
Sun, 4 Dec 2016 19:40:00 +0000 (11:40 -0800)
committerChristian Kuhn <lolli@schwarzbu.ch>
Fri, 23 Dec 2016 17:40:05 +0000 (18:40 +0100)
Set PDO attributes to ensure that BLOB fields are returned as strings to PHP,
the way MySQL does. Update INSERT/UPDATE statements that deal with BLOB field
to declare the proper parameter type so that the driver can do the required
encoding/escaping when sending the data to the DBMS.

Releases: master
Resolves: #78884
Change-Id: I1618da9130549bd95ce0410420ecdee40cc3632d
Reviewed-on: https://review.typo3.org/50883
Tested-by: TYPO3com <no-reply@typo3.com>
Reviewed-by: Manuel Selbach <manuel_selbach@yahoo.de>
Tested-by: Manuel Selbach <manuel_selbach@yahoo.de>
Reviewed-by: Christian Kuhn <lolli@schwarzbu.ch>
Tested-by: Christian Kuhn <lolli@schwarzbu.ch>
16 files changed:
typo3/sysext/core/Classes/Authentication/AbstractUserAuthentication.php
typo3/sysext/core/Classes/Cache/Backend/Typo3DatabaseBackend.php
typo3/sysext/core/Classes/DataHandling/DataHandler.php
typo3/sysext/core/Classes/Database/Connection.php
typo3/sysext/core/Classes/Database/ConnectionPool.php
typo3/sysext/core/Classes/Database/QueryView.php
typo3/sysext/core/Classes/Registry.php
typo3/sysext/core/Classes/Resource/Index/MetaDataRepository.php
typo3/sysext/core/Tests/Unit/Database/ConnectionTest.php
typo3/sysext/frontend/Classes/Authentication/FrontendUserAuthentication.php
typo3/sysext/impexp/Classes/Domain/Repository/PresetRepository.php
typo3/sysext/indexed_search/Classes/Controller/SearchController.php
typo3/sysext/indexed_search/Classes/Indexer.php
typo3/sysext/install/Classes/Service/SilentConfigurationUpgradeService.php
typo3/sysext/scheduler/Classes/Scheduler.php
typo3/sysext/scheduler/Classes/Task/AbstractTask.php

index bff19a2..4e661eb 100644 (file)
@@ -862,7 +862,11 @@ abstract class AbstractUserAuthentication
 
         // Re-create session entry
         $insertFields = $this->getNewSessionRecord($tempuser);
-        $inserted = (bool)$connection->insert($this->session_table, $insertFields);
+        $inserted = (bool)$connection->insert(
+            $this->session_table,
+            $insertFields,
+            ['ses_data' => Connection::PARAM_LOB]
+        );
         if (!$inserted) {
             $message = 'Session data could not be written to DB. Error: ' . $connection->errorInfo();
             GeneralUtility::sysLog($message, 'core', GeneralUtility::SYSLOG_SEVERITY_WARNING);
@@ -1280,7 +1284,8 @@ abstract class AbstractUserAuthentication
             GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($this->session_table)->update(
                 $this->user_table,
                 ['uc' => serialize($variable)],
-                [$this->userid_column => (int)$this->user[$this->userid_column]]
+                [$this->userid_column => (int)$this->user[$this->userid_column]],
+                ['uc' => Connection::PARAM_LOB]
             );
         }
     }
@@ -1368,7 +1373,8 @@ abstract class AbstractUserAuthentication
         GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($this->session_table)->update(
             $this->session_table,
             ['ses_data' => $this->user['ses_data']],
-            ['ses_id' => $this->user['ses_id']]
+            ['ses_id' => $this->user['ses_id']],
+            ['ses_data' => Connection::PARAM_LOB]
         );
     }
 
index 817461e..89fe79d 100644 (file)
@@ -111,6 +111,9 @@ class Typo3DatabaseBackend extends AbstractBackend implements TaggableBackendInt
                     'identifier' => $entryIdentifier,
                     'expires' => $expires,
                     'content' => $data,
+                ],
+                [
+                    'content' => Connection::PARAM_LOB,
                 ]
             );
         if (!empty($tags)) {
index a330443..fab8071 100644 (file)
@@ -6861,11 +6861,21 @@ class DataHandler
                     $fieldArray['uid'] = $suggestedUid;
                 }
                 $fieldArray = $this->insertUpdateDB_preprocessBasedOnFieldType($table, $fieldArray);
+                $typeArray = [];
+                if (!empty($GLOBALS['TCA'][$table]['ctrl']['transOrigDiffSourceField'])
+                    && array_key_exists($GLOBALS['TCA'][$table]['ctrl']['transOrigDiffSourceField'], $fieldArray)
+                ) {
+                    $typeArray[$GLOBALS['TCA'][$table]['ctrl']['transOrigDiffSourceField']] = Connection::PARAM_LOB;
+                }
                 $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($table);
                 $insertErrorMessage = '';
                 try {
                     // Execute the INSERT query:
-                    $connection->insert($table, $fieldArray);
+                    $connection->insert(
+                        $table,
+                        $fieldArray,
+                        $typeArray
+                    );
                 } catch (DBALException $e) {
                     $insertErrorMessage = $e->getPrevious()->getMessage();
                 }
index aa5e00f..2a2ed6f 100644 (file)
@@ -132,6 +132,23 @@ class Connection extends \Doctrine\DBAL\Connection
     }
 
     /**
+     * Detect if the column types are specified by column name or using
+     * positional information. In the first case quote the field names
+     * accordingly.
+     *
+     * @param array $input
+     * @return array
+     */
+    protected function quoteColumnTypes(array $input): array
+    {
+        if (!is_string(key($input))) {
+            return $input;
+        }
+
+        return $this->quoteColumnValuePairs($input);
+    }
+
+    /**
      * Inserts a table row with specified data.
      *
      * All SQL identifiers are expected to be unquoted and will be quoted when building the query.
@@ -148,7 +165,7 @@ class Connection extends \Doctrine\DBAL\Connection
         return parent::insert(
             $this->quoteIdentifier($tableName),
             $this->quoteColumnValuePairs($data),
-            $types
+            $this->quoteColumnTypes($types)
         );
     }
 
@@ -243,7 +260,7 @@ class Connection extends \Doctrine\DBAL\Connection
             $this->quoteIdentifier($tableName),
             $this->quoteColumnValuePairs($data),
             $this->quoteColumnValuePairs($identifier),
-            $types
+            $this->quoteColumnTypes($types)
         );
     }
 
@@ -264,7 +281,7 @@ class Connection extends \Doctrine\DBAL\Connection
         return parent::delete(
             $this->quoteIdentifier($tableName),
             $this->quoteColumnValuePairs($identifier),
-            $types
+            $this->quoteColumnTypes($types)
         );
     }
 
index 16a8131..85700ac 100644 (file)
@@ -146,6 +146,12 @@ class ConnectionPool
             $connectionParams['charset'] = 'utf-8';
         }
 
+        // Force consistent handling of binary objects across datbase platforms
+        // MySQL returns strings by default, PostgreSQL streams.
+        if (strpos($connectionParams['driver'], 'pdo_') === 0) {
+            $connectionParams['driverOptions'][\PDO::ATTR_STRINGIFY_FETCHES] = true;
+        }
+
         /** @var Connection $conn */
         $conn = DriverManager::getConnection($connectionParams);
         $conn->setFetchMode(\PDO::FETCH_ASSOC);
index 0238b05..af4c577 100644 (file)
@@ -235,8 +235,10 @@ class QueryView
                 $queryGenerator->enablePrefix = 1;
                 $queryString = $queryGenerator->getQuery($queryGenerator->queryConfig);
 
-                $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($queryGenerator->table);
-                $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
+                $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
+                    ->getQueryBuilderForTable($queryGenerator->table);
+                $queryBuilder->getRestrictions()->removeAll()
+                    ->add(GeneralUtility::makeInstance(DeletedRestriction::class));
                 $rowCount = $queryBuilder->count('*')
                     ->from($queryGenerator->table)
                     ->where(QueryHelper::stripLogicalOperatorPrefix($queryString))
@@ -248,14 +250,13 @@ class QueryView
                     'qSelect' => $queryGenerator->getSelectQuery($queryString),
                     'qString' => $queryString
                 ];
-                $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('sys_action');
-                $queryBuilder->update('sys_action')
-                    ->where($queryBuilder->expr()->eq(
-                        'uid',
-                        $queryBuilder->createNamedParameter($uid, \PDO::PARAM_INT)
-                    ))
-                    ->set('t2_data', serialize($t2DataValue))
-                    ->execute();
+                GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('sys_action')
+                    ->update(
+                        'sys_action',
+                        ['t2_data' => serialize($t2DataValue)],
+                        ['uid' => (int)$uid],
+                        ['t2_data' => Connection::PARAM_LOB]
+                    );
             }
             return 1;
         }
index d9265d6..9032058 100644 (file)
@@ -14,6 +14,7 @@ namespace TYPO3\CMS\Core;
  * The TYPO3 project - inspiring people to share!
  */
 
+use TYPO3\CMS\Core\Database\Connection;
 use TYPO3\CMS\Core\Database\ConnectionPool;
 use TYPO3\CMS\Core\Utility\GeneralUtility;
 
@@ -88,13 +89,15 @@ class Registry implements SingletonInterface
         if ((int)$rowCount < 1) {
             $connection->insert(
                 'sys_registry',
-                ['entry_namespace' => $namespace, 'entry_key' => $key, 'entry_value' => $serializedValue]
+                ['entry_namespace' => $namespace, 'entry_key' => $key, 'entry_value' => $serializedValue],
+                ['entry_value' => Connection::PARAM_LOB]
             );
         } else {
             $connection->update(
                 'sys_registry',
                 ['entry_value' => $serializedValue],
-                ['entry_namespace' => $namespace, 'entry_key' => $key]
+                ['entry_namespace' => $namespace, 'entry_key' => $key],
+                ['entry_value' => Connection::PARAM_LOB]
             );
         }
         $this->entries[$namespace][$key] = $value;
index 3ac9176..db58940 100644 (file)
@@ -139,7 +139,8 @@ class MetaDataRepository implements SingletonInterface
         $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($this->tableName);
         $connection->insert(
             $this->tableName,
-            $emptyRecord
+            $emptyRecord,
+            ['l10n_diffsource' => Connection::PARAM_LOB]
         );
 
         $record = $emptyRecord;
index 28cbe62..f5f84ca 100644 (file)
@@ -192,6 +192,16 @@ class ConnectionTest extends \TYPO3\CMS\Components\TestingFramework\Core\UnitTes
                 ['aValue', 'bValue'],
                 [Connection::PARAM_STR, Connection::PARAM_STR],
             ],
+            'with types for field' => [
+                [
+                    'aTestTable',
+                    ['aField' => 123, 'bField' => 'bValue'],
+                    ['aField' => Connection::PARAM_INT, 'bField' => Connection::PARAM_LOB]
+                ],
+                'INSERT INTO "aTestTable" ("aField", "bField") VALUES (?, ?)',
+                [123, 'bValue'],
+                [Connection::PARAM_INT, Connection::PARAM_LOB],
+            ],
         ];
     }
 
@@ -250,6 +260,12 @@ class ConnectionTest extends \TYPO3\CMS\Components\TestingFramework\Core\UnitTes
                 ['aValue', 1],
                 [Connection::PARAM_STR],
             ],
+            'with types for field' => [
+                ['aTestTable', ['aField' => 'aValue'], ['uid' => 1], ['aField' => Connection::PARAM_LOB]],
+                'UPDATE "aTestTable" SET "aField" = ? WHERE "uid" = ?',
+                ['aValue', 1],
+                [0 => Connection::PARAM_LOB, 1 => Connection::PARAM_STR],
+            ],
         ];
     }
 
@@ -295,6 +311,12 @@ class ConnectionTest extends \TYPO3\CMS\Components\TestingFramework\Core\UnitTes
                 ['aValue'],
                 [Connection::PARAM_STR],
             ],
+            'with types for field' => [
+                ['aTestTable', ['aField' => 'aValue'], ['aField' => Connection::PARAM_STR]],
+                'DELETE FROM "aTestTable" WHERE "aField" = ?',
+                ['aValue'],
+                [Connection::PARAM_STR],
+            ],
         ];
     }
 
index f73a784..4e874f6 100644 (file)
@@ -15,6 +15,7 @@ namespace TYPO3\CMS\Frontend\Authentication;
  */
 
 use TYPO3\CMS\Core\Authentication\AbstractUserAuthentication;
+use TYPO3\CMS\Core\Database\Connection;
 use TYPO3\CMS\Core\Database\ConnectionPool;
 use TYPO3\CMS\Core\TypoScript\Parser\TypoScriptParser;
 use TYPO3\CMS\Core\Utility\GeneralUtility;
@@ -453,7 +454,11 @@ class FrontendUserAuthentication extends AbstractUserAuthentication
                     'tstamp' => $GLOBALS['EXEC_TIME']
                 ];
                 $this->sessionDataTimestamp = $GLOBALS['EXEC_TIME'];
-                $databaseConnection->insert('fe_session_data', $insertFields);
+                $databaseConnection->insert(
+                    'fe_session_data',
+                    $insertFields,
+                    ['content' => Connection::PARAM_LOB]
+                );
                 // Now set the cookie (= fix the session)
                 $this->setSessionCookie();
             } else {
@@ -476,7 +481,8 @@ class FrontendUserAuthentication extends AbstractUserAuthentication
     public function removeSessionData()
     {
         $this->sessionDataTimestamp = null;
-        GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('fe_session_data')->delete(
+        GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('fe_session_data')
+            ->delete(
                 'fe_session_data',
                 ['hash' => $this->id]
             );
index 5ba5d02..a5ff945 100644 (file)
@@ -14,6 +14,7 @@ namespace TYPO3\CMS\Impexp\Domain\Repository;
  * The TYPO3 project - inspiring people to share!
  */
 
+use TYPO3\CMS\Core\Database\Connection;
 use TYPO3\CMS\Core\Database\ConnectionPool;
 use TYPO3\CMS\Core\Messaging\FlashMessage;
 use TYPO3\CMS\Core\Messaging\FlashMessageQueue;
@@ -126,7 +127,8 @@ class PresetRepository
                             'item_uid' => $inData['pagetree']['id'],
                             'preset_data' => serialize($inData)
                         ],
-                        ['uid' => (int)$preset['uid']]
+                        ['uid' => (int)$preset['uid']],
+                        ['preset_data' => Connection::PARAM_LOB]
                     );
 
                     $msg = 'Preset #' . $preset['uid'] . ' saved!';
@@ -144,7 +146,9 @@ class PresetRepository
                         'title' => $inData['preset']['title'],
                         'item_uid' => (int)$inData['pagetree']['id'],
                         'preset_data' => serialize($inData)
-                    ]
+                    ],
+                    ['preset_data' => Connection::PARAM_LOB]
+
                 );
 
                 $msg = 'New preset "' . htmlspecialchars($inData['preset']['title']) . '" is created';
index 4c44e59..bde541f 100644 (file)
@@ -817,7 +817,11 @@ class SearchController extends \TYPO3\CMS\Extbase\Mvc\Controller\ActionControlle
             'tstamp' => $GLOBALS['EXEC_TIME']
         ];
         $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('index_search_stat');
-        $connection->insert('index_stat_search', $insertFields);
+        $connection->insert(
+            'index_stat_search',
+            $insertFields,
+            ['searchoptions' => Connection::PARAM_LOB]
+        );
         $newId = $connection->lastInsertId('index_stat_search');
         if ($newId) {
             $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('index_stat_word');
index d3cb7ed..1ccd36a 100644 (file)
@@ -1480,7 +1480,11 @@ class Indexer
         if (IndexedSearchUtility::isTableUsed('index_phash')) {
             $connection = GeneralUtility::makeInstance(ConnectionPool::class)
                 ->getConnectionForTable('index_phash');
-            $connection->insert('index_phash', $fields);
+            $connection->insert(
+                'index_phash',
+                $fields,
+                ['cHashParams' => Connection::PARAM_LOB]
+            );
         }
         // PROCESSING index_section
         $this->submit_section($this->hash['phash'], $this->hash['phash']);
@@ -1648,7 +1652,11 @@ class Indexer
         if (IndexedSearchUtility::isTableUsed('index_phash')) {
             $connection = GeneralUtility::makeInstance(ConnectionPool::class)
                 ->getConnectionForTable('index_phash');
-            $connection->insert('index_phash', $fields);
+            $connection->insert(
+                'index_phash',
+                $fields,
+                ['cHashParams' => Connection::PARAM_LOB]
+            );
         }
         // PROCESSING index_fulltext
         $fields = [
index a909189..8f364f9 100644 (file)
@@ -111,6 +111,7 @@ class SilentConfigurationUpgradeService
         $this->migrateLockSslSetting();
         $this->migrateDatabaseConnectionSettings();
         $this->migrateDatabaseConnectionCharset();
+        $this->migrateDatabaseDriverOptions();
     }
 
     /**
@@ -652,7 +653,9 @@ class SilentConfigurationUpgradeService
             $value = $confManager->getLocalConfigurationValueByPath('DB/Connections/Default/driverOptions');
             $confManager->setLocalConfigurationValueByPath(
                 'DB/Connections/Default/driverOptions',
-                (bool)$value ? MYSQLI_CLIENT_COMPRESS : 0
+                [
+                    'flags' => (bool)$value ? MYSQLI_CLIENT_COMPRESS : 0,
+                ]
             );
         }
 
@@ -705,4 +708,25 @@ class SilentConfigurationUpgradeService
             // no incompatible charset configuration found, so nothing needs to be modified
         }
     }
+
+    /**
+     * Migrate the configuration setting DB/Connections/Default/driverOptions to array type.
+     *
+     * @return void
+     */
+    protected function migrateDatabaseDriverOptions()
+    {
+        $confManager = $this->configurationManager;
+        try {
+            $options = $confManager->getLocalConfigurationValueByPath('DB/Connections/Default/driverOptions');
+            if (!is_array($options)) {
+                $confManager->setLocalConfigurationValueByPath(
+                    'DB/Connections/Default/driverOptions',
+                    ['flags' => (int)$options]
+                );
+            }
+        } catch (\RuntimeException $e) {
+            // no driver options found, nothing needs to be modified
+        }
+    }
 }
index 2eb19c4..abd68b2 100644 (file)
@@ -14,6 +14,7 @@ namespace TYPO3\CMS\Scheduler;
  * The TYPO3 project - inspiring people to share!
  */
 
+use TYPO3\CMS\Core\Database\Connection;
 use TYPO3\CMS\Core\Database\ConnectionPool;
 use TYPO3\CMS\Core\Database\Query\QueryHelper;
 use TYPO3\CMS\Core\Registry;
@@ -70,7 +71,11 @@ class Scheduler implements \TYPO3\CMS\Core\SingletonInterface
             ];
             $connection = GeneralUtility::makeInstance(ConnectionPool::class)
                 ->getConnectionForTable('tx_scheduler_task');
-            $result = $connection->insert('tx_scheduler_task', $fields);
+            $result = $connection->insert(
+                'tx_scheduler_task',
+                $fields,
+                ['serialized_task_object' => Connection::PARAM_LOB]
+            );
 
             if ($result) {
                 $task->setTaskUid($connection->lastInsertId('tx_scheduler_task'));
@@ -133,7 +138,8 @@ class Scheduler implements \TYPO3\CMS\Core\SingletonInterface
                 $connectionPool->getConnectionForTable('tx_scheduler_task')->update(
                     'tx_scheduler_task',
                     ['serialized_executions' => $value],
-                    ['uid' => (int)$row['uid']]
+                    ['uid' => (int)$row['uid']],
+                    ['serialized_executions' => Connection::PARAM_LOB]
                 );
             }
         }
@@ -264,7 +270,12 @@ class Scheduler implements \TYPO3\CMS\Core\SingletonInterface
             ];
             $result = GeneralUtility::makeInstance(ConnectionPool::class)
                 ->getConnectionForTable('tx_scheduler_task')
-                ->update('tx_scheduler_task', $fields, ['uid' => $taskUid]);
+                ->update(
+                    'tx_scheduler_task',
+                    $fields,
+                    ['uid' => $taskUid],
+                    ['serialized_task_object' => Connection::PARAM_LOB]
+                );
         } else {
             $result = false;
         }
index 31dd39d..a05c452 100644 (file)
@@ -14,6 +14,7 @@ namespace TYPO3\CMS\Scheduler\Task;
  * The TYPO3 project - inspiring people to share!
  */
 
+use TYPO3\CMS\Core\Database\Connection;
 use TYPO3\CMS\Core\Database\ConnectionPool;
 use TYPO3\CMS\Core\Utility\GeneralUtility;
 
@@ -430,6 +431,9 @@ abstract class AbstractTask
                 ],
                 [
                     'uid' => $this->taskUid
+                ],
+                [
+                    'serialized_executions' => Connection::PARAM_LOB,
                 ]
             );
         return $numExecutions;
@@ -493,6 +497,9 @@ abstract class AbstractTask
                     ],
                     [
                         'uid' => $this->taskUid
+                    ],
+                    [
+                        'serialized_executions' => Connection::PARAM_LOB,
                     ]
                 );
         }
@@ -515,6 +522,9 @@ abstract class AbstractTask
                 ],
                 [
                     'uid' => $this->taskUid
+                ],
+                [
+                    'serialized_executions' => Connection::PARAM_LOB,
                 ]
             );
         return (bool)$result;