[BUGFIX] Make quoting of SQL fragments in TCA possible 89/54889/2
authorStephan Großberndt <stephan@grossberndt.de>
Fri, 30 Jun 2017 06:35:50 +0000 (08:35 +0200)
committerAndreas Wolf <andreas.wolf@typo3.org>
Thu, 30 Nov 2017 14:40:09 +0000 (15:40 +0100)
The table and column name quoting method {#tableName}.{#columnName}
introduced for TypoScript in issue #80506 is now used to quote
identifiers in TCA column definitions known to possibly contain SQL
fragments. Missing quotes for identifiers containing e.g. uppercase
letters lead to exceptions when performing a query in PostgreSQL.

In order to achieve this a new Class TcaPreparation is added as this is
not a typical TcaMigration.

Releases: master, 8.7
Resolves: #81751
Related: #80506
Change-Id: I230be317c7088e5de5343634fac507b4ab994b28
(cherry picked from commit 91aa14884b6526dc664b6472baa27e9d802b09fc)
Reviewed-on: https://review.typo3.org/54889
Reviewed-by: Andreas Wolf <andreas.wolf@typo3.org>
Tested-by: Andreas Wolf <andreas.wolf@typo3.org>
typo3/sysext/backend/Classes/Form/FormDataProvider/TcaFlexPrepare.php
typo3/sysext/core/Classes/Preparations/TcaPreparation.php [new file with mode: 0644]
typo3/sysext/core/Classes/Utility/ExtensionManagementUtility.php
typo3/sysext/core/Documentation/Changelog/master/Important-81751-DbalCompatibleQuotingInTca.rst [new file with mode: 0644]
typo3/sysext/core/Tests/Unit/Preparations/TcaPreparationTest.php [new file with mode: 0644]
typo3/sysext/frontend/Configuration/TCA/tt_content.php

index 7357a90..6d557e1 100644 (file)
@@ -22,6 +22,7 @@ use TYPO3\CMS\Core\Configuration\FlexForm\Exception\InvalidParentRowRootExceptio
 use TYPO3\CMS\Core\Configuration\FlexForm\Exception\InvalidPointerFieldValueException;
 use TYPO3\CMS\Core\Configuration\FlexForm\FlexFormTools;
 use TYPO3\CMS\Core\Migrations\TcaMigration;
+use TYPO3\CMS\Core\Preparations\TcaPreparation;
 use TYPO3\CMS\Core\Utility\GeneralUtility;
 
 /**
@@ -219,6 +220,7 @@ class TcaFlexPrepare implements FormDataProviderInterface
             if ($key === 'el' && is_array($value)) {
                 $newSubStructure = [];
                 $tcaMigration = GeneralUtility::makeInstance(TcaMigration::class);
+                $tcaPreparation = GeneralUtility::makeInstance(TcaPreparation::class);
                 foreach ($value as $subKey => $subValue) {
                     // On-the-fly migration for flex form "TCA"
                     // @deprecated since TYPO3 CMS 7. Not removed in TYPO3 CMS 8 though. This call will stay for now to allow further TCA migrations in 8.
@@ -237,7 +239,8 @@ class TcaFlexPrepare implements FormDataProviderInterface
                         array_unshift($messages, $context);
                         GeneralUtility::deprecationLog(implode(LF, $messages));
                     }
-                    $newSubStructure[$subKey] = $migratedTca['dummyTable']['columns']['dummyField'];
+                    $preparedTca = $tcaPreparation->prepare($migratedTca);
+                    $newSubStructure[$subKey] = $preparedTca['dummyTable']['columns']['dummyField'];
                 }
                 $value = $newSubStructure;
             }
diff --git a/typo3/sysext/core/Classes/Preparations/TcaPreparation.php b/typo3/sysext/core/Classes/Preparations/TcaPreparation.php
new file mode 100644 (file)
index 0000000..9f1aa3b
--- /dev/null
@@ -0,0 +1,94 @@
+<?php
+declare(strict_types=1);
+namespace TYPO3\CMS\Core\Preparations;
+
+/*
+ * This file is part of the TYPO3 CMS project.
+ *
+ * It is free software; you can redistribute it and/or modify it under
+ * the terms of the GNU General Public License, either version 2
+ * of the License, or any later version.
+ *
+ * For the full copyright and license information, please read the
+ * LICENSE.txt file that was distributed with this source code.
+ *
+ * The TYPO3 project - inspiring people to share!
+ */
+
+use TYPO3\CMS\Core\Database\ConnectionPool;
+use TYPO3\CMS\Core\Database\Query\QueryHelper;
+use TYPO3\CMS\Core\Utility\GeneralUtility;
+
+/**
+ * Prepare TCA. Used in bootstrap and Flex Form Data Structures.
+ *
+ * @internal Class and API may change any time.
+ */
+class TcaPreparation
+{
+
+    /**
+     * Prepare TCA
+     *
+     * This class is typically called within bootstrap with empty caches after all TCA
+     * files from extensions have been loaded. The preparation is then applied and
+     * the prepared result is cached.
+     * For flex form TCA, this class is called dynamically if opening a record in the backend.
+     *
+     * See unit tests for details.
+     *
+     * @param array $tca
+     * @return array
+     */
+    public function prepare(array $tca): array
+    {
+        $tca = $this->prepareQuotingOfTableNamesAndColumnNames($tca);
+        return $tca;
+    }
+
+    /**
+     * Quote all table and field names in definitions known to possibly have quoted identifiers like '{#tablename}.{#columnname}='
+     *
+     * @param array $tca Incoming TCA
+     * @return array Prepared TCA
+     */
+    protected function prepareQuotingOfTableNamesAndColumnNames(array $tca): array
+    {
+        $connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
+
+        $newTca = $tca;
+        $configToPrepareQuoting = [
+            'foreign_table_where',
+            'MM_table_where',
+            'search' => 'andWhere'
+        ];
+        foreach ($tca as $table => $tableDefinition) {
+            if (!isset($tableDefinition['columns']) || !is_array($tableDefinition['columns'])) {
+                continue;
+            }
+
+            foreach ($tableDefinition['columns'] as $columnName => $columnConfig) {
+                foreach ($configToPrepareQuoting as $level => $value) {
+                    if (is_string($level)) {
+                        $sqlQueryPartToPrepareQuotingIn = $columnConfig['config'][$level][$value] ?? '';
+                    } else {
+                        $sqlQueryPartToPrepareQuotingIn = $columnConfig['config'][$value] ?? '';
+                    }
+                    if (mb_strpos($sqlQueryPartToPrepareQuotingIn, '{#') !== false) {
+                        $quoted = QueryHelper::quoteDatabaseIdentifiers(
+                            $connectionPool->getConnectionForTable($table),
+                            $sqlQueryPartToPrepareQuotingIn
+                        );
+                        if (is_string($level)) {
+                            $newTca[$table]['columns'][$columnName]['config'][$level][$value] = $quoted;
+                        } else {
+                            $newTca[$table]['columns'][$columnName]['config'][$value] = $quoted;
+                        }
+                    }
+                }
+            }
+        }
+
+        return $newTca;
+    }
+}
index dd9f6b1..9de57c5 100644 (file)
@@ -18,6 +18,7 @@ use TYPO3\CMS\Core\Category\CategoryRegistry;
 use TYPO3\CMS\Core\Imaging\IconRegistry;
 use TYPO3\CMS\Core\Migrations\TcaMigration;
 use TYPO3\CMS\Core\Package\PackageManager;
+use TYPO3\CMS\Core\Preparations\TcaPreparation;
 
 /**
  * Extension Management functions
@@ -1783,6 +1784,10 @@ tt_content.' . $key . $suffix . ' {
             GeneralUtility::deprecationLog(implode(LF, $messages));
         }
 
+        // TCA preparation
+        $tcaPreparation = GeneralUtility::makeInstance(TcaPreparation::class);
+        $GLOBALS['TCA'] = $tcaPreparation->prepare($GLOBALS['TCA']);
+
         static::emitTcaIsBeingBuiltSignal($GLOBALS['TCA']);
     }
 
diff --git a/typo3/sysext/core/Documentation/Changelog/master/Important-81751-DbalCompatibleQuotingInTca.rst b/typo3/sysext/core/Documentation/Changelog/master/Important-81751-DbalCompatibleQuotingInTca.rst
new file mode 100644 (file)
index 0000000..0de0d80
--- /dev/null
@@ -0,0 +1,45 @@
+.. include:: ../../Includes.txt
+
+==================================================
+Important: #81751 - DBAL compatible quoting in TCA
+==================================================
+
+See :issue:`81751`
+
+Description
+===========
+
+Names of tables and columns used in SQL fragments of :php:`TCA` definitions need proper quoting to be compatible with different database drivers. The database
+framework of the core now applies proper quoting to table and column names if they are wrapped as :php:`{#tableName}.{#columnName}`
+
+It is advised to adapt extensions accordingly to run successfully on databases like PostgreSQL.
+
+Example for a :php:`TCA` definition snippet:
+
+.. code-block:: php
+
+    'columns' => [
+        'aField' => [
+            'config' => [
+                'foreign_table' => 'tt_content',
+                'foreign_table_where' => 'AND {#tt_content}.{#CType} IN (\'text\',\'textpic\',\'textmedia\') ORDER BY {#tt_content}.{#CType} ASC',
+                ...
+            ],
+        ],
+        ...
+    ],
+
+    'columns' => [
+        'aField' => [
+            'config' => [
+                'type' => 'text',
+                'search' => [
+                    'andWhere' => '{#CType}=\'text\' OR {#CType}=\'textpic\' OR {#CType}=\'textmedia\''
+                ],
+                ...
+            ],
+        ],
+        ...
+    ],
+
+.. index:: Database, Backend, TCA
diff --git a/typo3/sysext/core/Tests/Unit/Preparations/TcaPreparationTest.php b/typo3/sysext/core/Tests/Unit/Preparations/TcaPreparationTest.php
new file mode 100644 (file)
index 0000000..103e4a3
--- /dev/null
@@ -0,0 +1,86 @@
+<?php
+
+namespace TYPO3\CMS\Core\Tests\Unit\Preparations;
+
+/*
+ * This file is part of the TYPO3 CMS project.
+ *
+ * It is free software; you can redistribute it and/or modify it under
+ * the terms of the GNU General Public License, either version 2
+ * of the License, or any later version.
+ *
+ * For the full copyright and license information, please read the
+ * LICENSE.txt file that was distributed with this source code.
+ *
+ * The TYPO3 project - inspiring people to share!
+ */
+
+use Prophecy\Argument;
+use TYPO3\CMS\Core\Database\Connection;
+use TYPO3\CMS\Core\Database\ConnectionPool;
+use TYPO3\CMS\Core\Preparations\TcaPreparation;
+use TYPO3\CMS\Core\Utility\GeneralUtility;
+
+/**
+ * Test case
+ */
+class TcaPreparationTest extends \TYPO3\TestingFramework\Core\Unit\UnitTestCase
+{
+    public function prepareQuotingOfTableNamesAndColumnNamesDataProvider()
+    {
+        return [
+            [
+                [
+                    'aTable' => [
+                        'columns' => [
+                            'foo' => [
+                                'config' => [
+                                    'type' => 'inline',
+                                    'foreign_table_where' => 'AND {#tt_content}.{#CType} IN (\'text\',\'textpic\',\'textmedia\') ORDER BY {#tt_content}.{#CType} ASC',
+                                    'MM_table_where' => 'AND {#uid_local} = ###REC_FIELD_category###',
+                                    'search' => [
+                                        'andWhere' => '{#CType}=\'text\' OR {#CType}=\'textpic\' OR {#CType}=\'textmedia\' AND {#title}=\'foo\'',
+                                    ],
+                                ],
+                            ],
+                        ],
+                    ],
+                ],
+                [
+                    'aTable' => [
+                        'columns' => [
+                            'foo' => [
+                                'config' => [
+                                    'type' => 'inline',
+                                    'foreign_table_where' => 'AND `tt_content`.`CType` IN (\'text\',\'textpic\',\'textmedia\') ORDER BY `tt_content`.`CType` ASC',
+                                    'MM_table_where' => 'AND `uid_local` = ###REC_FIELD_category###',
+                                    'search' => [
+                                        'andWhere' => '`CType`=\'text\' OR `CType`=\'textpic\' OR `CType`=\'textmedia\' AND `title`=\'foo\'',
+                                    ],
+                                ],
+                            ],
+                        ],
+                    ],
+                ],
+            ],
+        ];
+    }
+
+    /**
+     * @test
+     * @dataProvider prepareQuotingOfTableNamesAndColumnNamesDataProvider
+     */
+    public function prepareQuotingOfTableNamesAndColumnNames(array $input, array $expected)
+    {
+        $connection = $this->prophesize(Connection::class);
+        $connection->quoteIdentifier('tt_content')->willReturn('`tt_content`');
+        $connection->quoteIdentifier('CType')->willReturn('`CType`');
+        $connection->quoteIdentifier('uid_local')->willReturn('`uid_local`');
+        $connection->quoteIdentifier('title')->willReturn('`title`');
+        $connectionPool = $this->prophesize(ConnectionPool::class);
+        $connectionPool->getConnectionForTable(Argument::any())->willReturn($connection->reveal());
+        GeneralUtility::addInstance(ConnectionPool::class, $connectionPool->reveal());
+        $subject = new TcaPreparation();
+        $this->assertEquals($expected, $subject->prepare($input));
+    }
+}
index 369642f..5817a1a 100644 (file)
@@ -580,7 +580,7 @@ return [
                 'rows' => '15',
                 'softref' => 'typolink_tag,images,email[subst],url',
                 'search' => [
-                    'andWhere' => 'CType=\'text\' OR CType=\'textpic\' OR CType=\'textmedia\''
+                    'andWhere' => '{#CType}=\'text\' OR {#CType}=\'textpic\' OR {#CType}=\'textmedia\''
                 ]
             ]
         ],
@@ -1078,7 +1078,7 @@ return [
                     '
                 ],
                 'search' => [
-                    'andWhere' => 'CType=\'list\''
+                    'andWhere' => '{#CType}=\'list\''
                 ]
             ]
         ],