Bug #81388
closed
SQL-keyword "recursive" not enclosed in ticks - inserts into tt_content fail with MariaDB >=10.2
Added by Robert Görke almost 7 years ago.
Updated over 4 years ago.
Category:
Database API (Doctrine DBAL)
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.
- Project changed from 9 to TYPO3 Core
- TYPO3 Version set to 8
- Is duplicate of Bug #81341: recursive is a reserved keyword in MariaDB 10.2. Causes errors in queries for tt_content... added
- 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.
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
Updated by 
Updated by 
Updated by