fixes #4434 Disable special column names starting without underscore

extension/Classes/Core/Database/DatabaseUpdate.php

@@ -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,56 @@ 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%';");
+        $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);