Bug #81388

SQL-keyword "recursive" not enclosed in ticks - inserts into tt_content fail with MariaDB >=10.2

Added by Robert Görke almost 3 years ago. Updated 10 months ago.

Status:
Rejected
Priority:
Could have
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2017-05-31
Due date:
% Done:

0%

TYPO3 Version:
7
PHP Version:
Tags:
Complexity:
Is Regression:
Sprint Focus:

Description

Situation: I am using typo3 together with mariaDB 10.2. on centos. Now mariaDB supports recursive queries since 10.2 und thus has "recursive" as a keyword - despite the fact that it is not listed in the official list of keywords: You cannot use "recursive" as an identifier without escaping it with ticks.
Problem: typo3 uses the keyword "recursive" without enclosing it in ticks.
Observation: When trying to insert a text element, the corresponding sql-insert tries to insert into table tt_content. Thereby it also lists all columns, among them a column called "recursive", in the query - without ticks. This fails.
Result: It is not possible to use typo3 with mariaDB from 10.2 on.
Solution: A solution could simply be to enclose all identifiers in automatically built sql-queries by (correct) ticks.


Related issues

Duplicates TYPO3 Core - Bug #81341: recursive is a reserved keyword in MariaDB 10.2. Causes errors in queries for tt_content... Closed 2017-05-25

History

#1 Updated by Michael Stucki almost 3 years ago

  • Project changed from forge.typo3.org to TYPO3 Core
  • TYPO3 Version set to 8

Wrong project.

#2 Updated by Morton Jonuschat almost 3 years ago

  • Duplicates Bug #81341: recursive is a reserved keyword in MariaDB 10.2. Causes errors in queries for tt_content... added

#3 Updated by Morton Jonuschat almost 3 years ago

  • Category set to Database API (Doctrine DBAL)
  • Status changed from New to Rejected
  • Assignee set to Morton Jonuschat
  • Priority changed from Must have to Could have
  • TYPO3 Version changed from 8 to 7

This should be fixed on TYPO3 v8 as we replaced the whole database abstraction layer which gave us such things as quoting field names in all queries.

TYPO3 7LTS is now in priority bugfix mode and while we strive to support alternative MySQL implementations like MariaDB or Percona the fact remains that MariaDB 10.2 is feature-wise closer to MySQL 8.0 than to 5.7 (MySQL 8.0 like MariaDB 10.2 gained recursive common table expressions where the new protected keyword comes from).

Given that MySQL 8.0 is not a supported database platform either and that fixing this on TYPO3 7LTS is almost impossible with the existing database architecture classifying this as a must-fix breakage is not warranted.

Feel free to reopen this issue or create a new one if you see the same problems with MariaDB 10.2 or MySQL 8.0 on TYPO 8LTS - we have a much higher chance of fixing it there due to the modernized database layer.

#4 Updated by Klaus Hinum 10 months ago

As a work around, you can edit e.g. the typo3/sysext/core/Classes/Database/DatabaseConnection.php INSERTquery function (line 511) and add backticks to the keys:

public function INSERTquery($table, $fields_values, $no_quote_fields = false)
    {
        // Table and fieldnames should be "SQL-injection-safe" when supplied to this
        // function (contrary to values in the arrays which may be insecure).
        if (!is_array($fields_values) || empty($fields_values)) {
            return null;
        }
        foreach ($this->preProcessHookObjects as $hookObject) {
            $hookObject->INSERTquery_preProcessAction($table, $fields_values, $no_quote_fields, $this);
        }
        // Quote and escape values
        $fields_values = $this->fullQuoteArray($fields_values, $table, $no_quote_fields, true);
        // Build query
        $query_keys = '';
        foreach ($fields_values as $t_key => $t_val) {
            if ($query_keys) $query_keys .= ',';
            $query_keys .= '`'.$t_key.'`';
        }
        $query = 'INSERT INTO ' . $table . ' (' . $query_keys . ') VALUES ' . '(' . implode(',', $fields_values) . ')';
        //$query = 'INSERT INTO ' . $table . ' (' . implode(',', array_keys($fields_values)) . ') VALUES ' . '(' . implode(',', $fields_values) . ')';
        // Return query
        if ($this->debugOutput || $this->store_lastBuiltQuery) {
            $this->debug_lastBuiltQuery = $query;
        }
        return $query;
    }

Also available in: Atom PDF