Bug #81388
closed
SQL-keyword "recursive" not enclosed in ticks - inserts into tt_content fail with MariaDB >=10.2
0%
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.
Updated by Michael Stucki over 7 years ago
- Project changed from 9 to TYPO3 Core
- TYPO3 Version set to 8
Wrong project.
Updated by Morton Jonuschat over 7 years ago
- Is duplicate of Bug #81341: recursive is a reserved keyword in MariaDB 10.2. Causes errors in queries for tt_content... added
Updated by Morton Jonuschat over 7 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.
Updated by Klaus Hinum about 5 years 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;
}