Skip to content
Snippets Groups Projects

fixes #4434 Disable special column names starting without underscore

Merged fixes #4434 Disable special column names starting without underscore
F4434DisableSpecialColumnNamesStartingWithoutUnderscore into master
Merged Marc Egger requested to merge F4434DisableSpecialColumnNamesStartingWithoutUnderscore into master
Compare
and
Show latest version
4 files
+ 253
− 287
Compare changes
  • Side-by-side
  • Inline
Files
4
extension/Classes/Core/Database/DatabaseUpdate.php
+ 47
− 84
@@ -134,7 +134,7 @@ class DatabaseUpdate {
$versionInfo = $this->getDatabaseVersion();
$old = $versionInfo[QFQ_VERSION_KEY] ?? false;
if (version_compare($old, '19.7.2') === -1) {
if (version_compare($old, '19.9.9') === -1) { #TODO: change version number before new release! (replace 19.9.9 with the version of the new release)
$this->updateSpecialColumns();
}
@@ -165,7 +165,7 @@ class DatabaseUpdate {
/**
* Check if there are special columns without prepended underscore in the QFQ application. If yes, then throw an error.
* A link is provided to automatically prepend all found special columns.
* A link is provided to automatically prepend all found special columns. And another link to skip the auto-replacement.
*
* @throws \CodeException
* @throws \DbException
@@ -173,68 +173,33 @@ class DatabaseUpdate {
*/
private function updateSpecialColumns() {
// Prepare regex patterns to find "AS <special column name>"
$special_columns = array(
COLUMN_LINK,
COLUMN_EXEC,
COLUMN_PPAGE,
COLUMN_PPAGEC,
COLUMN_PPAGED,
COLUMN_PPAGEE,
COLUMN_PPAGEH,
COLUMN_PPAGEI,
COLUMN_PPAGEN,
COLUMN_PPAGES,
COLUMN_PAGE,
COLUMN_PAGEC,
COLUMN_PAGED,
COLUMN_PAGEE,
COLUMN_PAGEH,
COLUMN_PAGEI,
COLUMN_PAGEN,
COLUMN_PAGES,
COLUMN_YANK,
COLUMN_PPDF,
COLUMN_FFILE,
COLUMN_ZZIP,
COLUMN_PDF,
COLUMN_FILE,
COLUMN_ZIP,
COLUMN_EXCEL,
COLUMN_SAVE_PDF,
COLUMN_THUMBNAIL,
COLUMN_MONITOR,
COLUMN_MIME_TYPE,
COLUMN_FILE_SIZE,
COLUMN_NL2BR,
COLUMN_HTMLENTITIES,
COLUMN_STRIPTAGS,
COLUMN_EXCEL_PLAIN,
COLUMN_EXCEL_STRING,
COLUMN_EXCEL_BASE64,
COLUMN_EXCEL_NUMERIC,
COLUMN_BULLET,
COLUMN_CHECK,
COLUMN_IMG,
COLUMN_MAILTO,
COLUMN_SENDMAIL,
COLUMN_VERTICAL);
$special_columns = ['link', 'exec', 'Page', 'Pagec', 'Paged', 'Pagee', 'Pageh', 'Pagei', 'Pagen', 'Pages'
, 'page', 'pagec', 'paged', 'pagee', 'pageh', 'pagei', 'pagen', 'pages', 'yank', 'Pdf', 'File', 'Zip'
, 'pdf', 'file', 'zip', 'excel', 'savePdf', 'thumbnail', 'monitor', 'mimeType', 'fileSize', 'nl2br'
, 'htmlentities', 'striptags', 'XLS', 'XLSs', 'XLSb', 'XLSn', 'bullet', 'check', 'img', 'mailto'
, 'sendmail', 'vertical'];
$make_pattern = function ($column) {
return '/([aA][sS]\s+)(' . $column . ')/s';
};
$patterns = array_map($make_pattern, $special_columns);
// Prepare search and replace
$placeholder = '%%%UNDERLINE%%%'; // used temporarily to mark where '_' should go
$actionSpecialColumn = $_GET[ACTION_SPECIAL_COLUMN_UPDATE] ?? ''; // get parameter to decide whether to execute the replacement
$dbT3 = $this->store->getVar(SYSTEM_DB_NAME_T3, STORE_SYSTEM);
$message = ''; // error message in case an old special column is found
// TT_CONTENT
// TT_CONTENT tt_content.bodytext
$message_fe = '';
$res = $this->db->sql("SELECT * FROM " . $dbT3 . ".tt_content WHERE CType='qfq_qfq' AND deleted=0;");
if (defined('PHPUNIT_QFQ')) {
$res = array();
} else {
$res = $this->db->sql("SELECT uid, header, bodytext FROM " . $dbT3 . ".tt_content WHERE CType='qfq_qfq' AND deleted=0;");
}
foreach ($res as $i => $tt_content) {
$replaced_placeholder = preg_replace($patterns, '${1}' . $placeholder . '${2}', $tt_content['bodytext']);
if (strpos($replaced_placeholder, $placeholder) !== false) {
if ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_REPLACE) {
if ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_DO_REPLACE) {
$replace = str_replace($placeholder, '_', $replaced_placeholder);
$query = "UPDATE " . $dbT3 . ".tt_content SET bodytext='" . addslashes($replace) . "' WHERE uid='" . $tt_content['uid'] . "'";
$this->db->sql($query);
@@ -246,62 +211,60 @@ class DatabaseUpdate {
}
}
if ($message_fe != '') {
$message .= '<hr><h3>TT_CONTENT</h3>' . $message_fe;
$message .= '<hr><h3>Typo3 Table: tt_content (column: bodytext)</h3>' . $message_fe;
}
// FORM ELEMENTS
// FORM ELEMENTS FormElement.value, FormElement.note
$message_ttc = '';
$res = $this->db->sql("SELECT * FROM FormElement as fe WHERE fe.type='note' AND fe.value LIKE '%#!report%' OR fe.note LIKE '%#!report%';");
if (defined('PHPUNIT_QFQ')) {
$res = array();
} else {
$res = $this->db->sql("SELECT fe.id, fe.name, fe.value, fe.note FROM FormElement as fe WHERE fe.type='note' AND fe.value LIKE '#!report%' OR fe.note LIKE '%#!report%';");
}
foreach ($res as $i => $tt_content) {
// FormElement.value
$replaced_placeholder = preg_replace($patterns, '${1}' . $placeholder . '${2}', $tt_content['value']);
if (strpos($replaced_placeholder, $placeholder) !== false) {
if ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_REPLACE) {
$replace = str_replace($placeholder, '_', $replaced_placeholder);
$query = "UPDATE FormElement SET value='" . addslashes($replace) . "' WHERE id='" . $tt_content['id'] . "'";
$this->db->sql($query);
foreach (['value', 'note'] as $j => $columnName) {
$replaced_placeholder = preg_replace($patterns, '${1}' . $placeholder . '${2}', $tt_content[$columnName]);
if (strpos($replaced_placeholder, $placeholder) !== false) {
if ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_DO_REPLACE) {
$replace = str_replace($placeholder, '_', $replaced_placeholder);
$query = "UPDATE FormElement SET " . $columnName . "='" . addslashes($replace) . "' WHERE id='" . $tt_content['id'] . "'";
$this->db->sql($query);
}
$message_ttc .= '<hr><b>' . $tt_content['name'] . ' [id:' . $tt_content['id'] . '] (FormElement.' . $columnName . ')</b><br><br>';
$message_ttc .= str_replace($placeholder,
'<span style="font-weight: bold; color: red;">>>>_</span>',
htmlentities($replaced_placeholder));
}
$message_ttc .= '<hr><b>' . $tt_content['name'] . ' [id:' . $tt_content['id'] . '] (FormElement.value)</b><br><br>';
$message_ttc .= str_replace($placeholder,
'<span style="font-weight: bold; color: red;">>>>_</span>',
htmlentities($replaced_placeholder));
}
// FormElement.note
$replaced_placeholder = preg_replace($patterns, '${1}' . $placeholder . '${2}', $tt_content['note']);
if (strpos($replaced_placeholder, $placeholder) !== false) {
if ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_REPLACE) {
$replace = str_replace($placeholder, '_', $replaced_placeholder);
$query = "UPDATE FormElement SET note='" . addslashes($replace) . "' WHERE id='" . $tt_content['id'] . "'";
$this->db->sql($query);
}
$message_ttc .= '<hr><b>' . $tt_content['name'] . ' [id:' . $tt_content['id'] . '] (FormElement.note)</b><br><br>';
$message_ttc .= str_replace($placeholder,
'<span style="font-weight: bold; color: red;">>>>_</span>',
htmlentities($replaced_placeholder));
}
}
if ($message_ttc != '') {
$message .= '<hr><h3>FORM ELEMENTS</h3>' . $message_ttc;
$message .= '<hr><h3>QFQ Table: FormElement (columns: value and note)</h3>' . $message_ttc;
}
if ($message != '' ) {
if ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_REPLACE) {
// show error message or save log
if ($message != '') {
if ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_DO_REPLACE) {
// save log file
$message = '<h1>Special column names replaced</h1>The following special column names were replaced.<hr>' . $message;
Logger::logMessage($message, SYSTEM_FILEADMIN_PROTECTED_LOG . '/' . date("YmdHi") . '_special_columns_auto_update.html');
} elseif ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_DO_SKIP_REPLACE) {
// do nothing
} else {
// show error
$message = $actionSpecialColumn
. '<h2>Special Column names without prepended underscore found.</h2>'
. ' Those are not supported any longer.'
. '<h2>SOLUTION</h2>'
. '<a href="?' . http_build_query(array_merge($_GET, array(ACTION_SPECIAL_COLUMN_UPDATE => ACTION_SPECIAL_COLUMN_REPLACE))) . '">Click here</a>'
. 'Click <a href="?' . http_build_query(array_merge($_GET, array(ACTION_SPECIAL_COLUMN_UPDATE => ACTION_SPECIAL_COLUMN_DO_REPLACE))) . '">Auto-Replace</a>'
. ' to automatically prepend the found column names with an underscore.'
. ' In the report bellow the missing underscores are marked by "<span style="font-weight: bold; color: red;">>>>_</span>".'
. ' In the report below the missing underscores are marked by "<span style="font-weight: bold; color: red;">>>>_</span>".'
. ' This report will be saved in ' . SYSTEM_FILEADMIN_PROTECTED_LOG . ' after the automatic replacement.'
. ' <br><br>To update qfq without changing the special columns (your app will probably be broken): '
. '<a href="?' . http_build_query(array_merge($_GET, array(ACTION_SPECIAL_COLUMN_UPDATE => ACTION_SPECIAL_COLUMN_DO_SKIP_REPLACE))) . '">Skip Auto-Replace</a>'
. '<h2>Report</h2>'
. $message;
$errorMsg[ERROR_MESSAGE_TO_USER] = 'Error while updating qfq.';
$errorMsg[ERROR_MESSAGE_TO_USER] = 'Error while updating qfq. ';
$errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $message;
$errorMsg[ERROR_MESSAGE_TO_DEVELOPER_SANITIZE] = false;
throw new \DbException(json_encode($errorMsg), ERROR_PLAY_SQL_FILE);