Bug #35253
closedInstaller should respect backticks while creating sql queries
Added by Kay Strobach over 12 years ago. Updated over 8 years ago.
80%
Description
The TYPO3 SQL parser removes backticks from table and field names in SQL statements. This causes errors.
E.g. a valid SQL file entry like this (with backticks):
CREATE TABLE `user_piwikintegration_site` ( `group` varchar(250) NOT NULL, ) TYPE=InnoDB AUTO_INCREMENT=2 ;
is converted to this invalid SQL (without backticks):
CREATE TABLE user_piwikintegration_site ( group varchar(250) NOT NULL ) ENGINE=InnoDB;
While the first query is valid, the modified query causes an error:
Error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group varchar(250) NOT NULL' at line 2
The Problem can be easily fixed, by editing t3lib_install::getUpdateSuggestions in t3lib/class.t3lib_install.php
Todo:- add backticks around table names
- add backticks around fieldnames
Updated by Kay Strobach over 12 years ago
file is class.t3lib_install_sql.php -> i'm working on this issue!
Updated by Kay Strobach over 12 years ago
- % Done changed from 0 to 80
public function getUpdateSuggestions($diffArr, $keyList = 'extra,diff') { $statements = array(); $deletedPrefixKey = $this->deletedPrefixKey; $remove = 0; if ($keyList == 'remove') { $remove = 1; $keyList = 'extra'; } $keyList = explode(',', $keyList); foreach ($keyList as $theKey) { if (is_array($diffArr[$theKey])) { foreach ($diffArr[$theKey] as $table => $info) { $whole_table = array(); if (is_array($info['fields'])) { foreach ($info['fields'] as $fN => $fV) { if ($info['whole_table']) { $whole_table[] = '`' . $fN . '` ' . $fV; } else { // Special case to work around MySQL problems when adding auto_increment fields: if (stristr($fV, 'auto_increment')) { // The field can only be set "auto_increment" if there exists a PRIMARY key of that field already. // The check does not look up which field is primary but just assumes it must be the field with the auto_increment value... if (isset($diffArr['extra'][$table]['keys']['PRIMARY'])) { // Remove "auto_increment" from the statement - it will be suggested in a 2nd step after the primary key was created $fV = str_replace(' auto_increment', '', $fV); } else { // In the next step, attempt to clear the table once again (2 = force) $info['extra']['CLEAR'] = 2; } } if ($theKey == 'extra') { if ($remove) { if (substr($fN, 0, strlen($deletedPrefixKey)) != $deletedPrefixKey) { $statement = 'ALTER TABLE `' . $table . '` CHANGE `' . $fN . '` `' . $deletedPrefixKey . $fN . '` ' . $fV . ';'; $statements['change'][md5($statement)] = $statement; } else { $statement = 'ALTER TABLE `' . $table . '` DROP `' . $fN . '`;'; $statements['drop'][md5($statement)] = $statement; } } else { $statement = 'ALTER TABLE `' . $table . '` ADD `' . $fN . '` ' . $fV . ';'; $statements['add'][md5($statement)] = $statement; } } elseif ($theKey == 'diff') { $statement = 'ALTER TABLE `' . $table . '` CHANGE `' . $fN . '` `' . $fN . '` ' . $fV . ';'; $statements['change'][md5($statement)] = $statement; $statements['change_currentValue'][md5($statement)] = $diffArr['diff_currentValues'][$table]['fields'][$fN]; } } } } if (is_array($info['keys'])) { foreach ($info['keys'] as $fN => $fV) { if ($info['whole_table']) { $whole_table[] = $fV; } else { if ($theKey == 'extra') { if ($remove) { $statement = 'ALTER TABLE `' . $table . '`' . ($fN == 'PRIMARY' ? ' DROP PRIMARY KEY' : ' DROP KEY `' . $fN . '`') . ';'; $statements['drop'][md5($statement)] = $statement; } else { $statement = 'ALTER TABLE `' . $table . '` ADD ' . $fV . ';'; $statements['add'][md5($statement)] = $statement; } } elseif ($theKey == 'diff') { $statement = 'ALTER TABLE `' . $table . '`' . ($fN == 'PRIMARY' ? ' DROP PRIMARY KEY' : ' DROP KEY `' . $fN . '`') . ';'; $statements['change'][md5($statement)] = $statement; $statement = 'ALTER TABLE ' . $table . ' ADD ' . $fV . ';'; $statements['change'][md5($statement)] = $statement; } } } } if (is_array($info['extra'])) { $extras = array(); $extras_currentValue = array(); $clear_table = FALSE; foreach ($info['extra'] as $fN => $fV) { // Only consider statements which are missing in the database but don't remove existing properties if (!$remove) { if (!$info['whole_table']) { // If the whole table is created at once, we take care of this later by imploding all elements of $info['extra'] if ($fN == 'CLEAR') { // Truncate table must happen later, not now // Valid values for CLEAR: 1=only clear if keys are missing, 2=clear anyway (force) if (count($info['keys']) || $fV == 2) { $clear_table = TRUE; } continue; } else { $extras[] = $fN . '=' . $fV; $extras_currentValue[] = $fN . '=' . $diffArr['diff_currentValues'][$table]['extra'][$fN]; } } } } if ($clear_table) { $statement = 'TRUNCATE TABLE `' . $table . '`;'; $statements['clear_table'][md5($statement)] = $statement; } if (count($extras)) { $statement = 'ALTER TABLE `' . $table . '` ' . implode(' ', $extras) . ';'; $statements['change'][md5($statement)] = $statement; $statements['change_currentValue'][md5($statement)] = implode(' ', $extras_currentValue); } } if ($info['whole_table']) { if ($remove) { if (substr($table, 0, strlen($deletedPrefixKey)) != $deletedPrefixKey) { $statement = 'ALTER TABLE `' . $table . '` RENAME `' . $deletedPrefixKey . $table . '`;'; $statements['change_table'][md5($statement)] = $statement; } else { $statement = 'DROP TABLE `' . $table . '`;'; $statements['drop_table'][md5($statement)] = $statement; } // count: $count = $GLOBALS['TYPO3_DB']->exec_SELECTcountRows('*', $table); $statements['tables_count'][md5($statement)] = $count ? 'Records in table: ' . $count : ''; } else { $statement = 'CREATE TABLE `' . $table . "` (\n" . implode(",\n", $whole_table) . "\n)"; if ($info['extra']) { foreach ($info['extra'] as $k => $v) { if ($k == 'COLLATE' || $k == 'CLEAR') { continue; // Skip these special statements. TODO: collation support is currently disabled (needs more testing) } $statement .= ' ' . $k . '=' . $v; // Add extra attributes like ENGINE, CHARSET, etc. } } $statement .= ';'; $statements['create_table'][md5($statement)] = $statement; } } } } } return $statements; }
This function works in 4.7 and 4.5, the basement is 4.7 This function is located in
- 4.5: t3lib_install::getUpdateSuggestions in t3lib/class.t3lib_install.php
- 4.7: t3lib_install_sql::getUpdateSuggestions in t3lib/class.t3lib_install_sql.php
Updated by Gerrit Code Review over 12 years ago
- Status changed from New to Under Review
Patch set 1 for branch master has been pushed to the review server.
It is available at http://review.typo3.org/9894
Updated by Gerrit Code Review over 12 years ago
Patch set 2 for branch master has been pushed to the review server.
It is available at http://review.typo3.org/9894
Updated by Gerrit Code Review almost 12 years ago
Patch set 3 for branch master has been pushed to the review server.
It is available at http://review.typo3.org/9894
Updated by Gerrit Code Review over 11 years ago
Patch set 4 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/9894
Updated by Gerrit Code Review about 11 years ago
Patch set 5 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/9894
Updated by Mathias Schreiber over 9 years ago
- Status changed from Under Review to Accepted
- Is Regression set to No
Updated by Morton Jonuschat over 8 years ago
- Status changed from Accepted to Rejected
Escaping SQL identifiers with backticks is a MySQL specific syntax that breaks DBAL/SqlParser and won't be supported at this time. It is advised not to use reserved SQL keywords as table/field/index names.