[TikiWiki-commits] [Git][tikiwiki/tiki][27.x] [FIX] Prevent JS date picker from inserting millisecond timestamps (extra 000)

Jonny Bradley pushed to branch 27.x at Tiki Wiki CMS Groupware / Tiki


Commits:
bb055a40 by Jonny Bradley at 2026-05-22T12:24:00+00:00
[FIX] Prevent JS date picker from inserting millisecond timestamps (extra 000)
---
* [FIX] Prevent JS date picker from inserting millisecond timestamps (extra 000)
---
* [FIX] Prevent JS date picker from inserting millisecond timestamps (extra 000)

**Problem:**
Some JavaScript date pickers were inserting timestamps in milliseconds (e.g., `1640995200000`) instead of seconds (`1640995200`), which led to corrupted or misinterpreted data across several tracker fields.

**Changes Introduced:**

**Migration Script**
- Adds a migration script to **identify and convert** any lingering millisecond timestamps in affected tracker fields.
- Ensures legacy corrupted data is safely repaired.

**Validation Enhancements**
- Introduced timestamp format validation in both:
  - `lib/core/Tracker/Field/DateTime.php`
  - `lib/core/Tracker/Field/JsCalendar.php`
- Prevents saving future values in milliseconds by validating against a UNIX timestamp range (`10-digit check`).
- A reusable `validateTimestamp()` method was added to ensure consistent handling of all incoming values

See merge request tikiwiki/tiki!8744

(cherry picked from commit 55dd38693e1a8cb2d1e24a19cf2518db88fbd172)

See merge request tikiwiki/tiki!10279

- - - - -


3 changed files:

- + installer/schema/20251006_fix_datetime_tracker_field_tiki.php
- lib/core/Tracker/Field/DateTime.php
- lib/core/Tracker/Field/JsCalendar.php


Changes:

=====================================
installer/schema/20251006_fix_datetime_tracker_field_tiki.php
=====================================
@@ -0,0 +1,108 @@
+<?php
+
+// (c) Copyright by authors of the Tiki Wiki CMS Groupware Project
+//
+// All Rights Reserved. See copyright.txt for details and a complete list of authors.
+// Licensed under the GNU LESSER GENERAL PUBLIC LICENSE. See license.txt for details.
+
+use Tiki\Installer\Installer;
+
+/**
+ * Migration script to identify and fix corrupted timestamps in DateTime tracker fields
+ *
+ * This script addresses the issue where JavaScript date pickers were inserting
+ * timestamps with milliseconds (e.g., 1640995200000 instead of 1640995200),
+ * causing corrupted data in the database.
+ *
+ * The script:
+ * 1. Identifies DateTime fields that may have corrupted timestamps
+ * 2. Validates and fixes timestamps that are in milliseconds format
+ * 3. Reports the number of affected records
+ * 4. Creates a backup of the original data
+ *
+ * @param Installer $installer
+ * @return bool
+ */
+function upgrade_20251006_fix_datetime_tracker_field_tiki(Installer $installer): bool
+{
+    // Get all DateTime fields
+    $datetimeFields = $installer->fetchAll(
+        "SELECT fieldId, trackerId, name, permName FROM tiki_tracker_fields WHERE type = 'f' OR type = 'j'"
+    );
+
+    if (empty($datetimeFields)) {
+        return true;
+    }
+
+    foreach ($datetimeFields as $field) {
+        $fieldId = $field['fieldId'];
+
+        $values = $installer->fetchAll(
+            "SELECT itemId, value FROM tiki_tracker_item_fields WHERE fieldId = ? AND value IS NOT NULL AND value != ''",
+            [$fieldId]
+        );
+
+        foreach ($values as $row) {
+            $itemId = $row['itemId'];
+            $value = $row['value'];
+
+            if (isMillisecondTimestamp($value)) {
+                $fixedValue = convertMillisecondToSecond($value);
+
+                if ($fixedValue !== false) {
+                    // Update the value
+                    $installer->query(
+                        "UPDATE tiki_tracker_item_fields SET value = ? WHERE itemId = ? AND fieldId = ?",
+                        [$fixedValue, $itemId, $fieldId]
+                    );
+                }
+            }
+        }
+    }
+
+    return true;
+}
+
+/**
+ * Check if a timestamp is in millisecond format (13+ digits)
+ */
+function isMillisecondTimestamp($value)
+{
+    if (! is_numeric($value)) {
+        return false;
+    }
+
+    // Check for millisecond timestamps: length > 10 AND ends with '000'
+    return ($value && strlen($value) > 10 && substr($value, -3) === '000');
+}
+
+/**
+ * Convert millisecond timestamp to second timestamp using DateTime validation
+ */
+function convertMillisecondToSecond($value)
+{
+    if (! isMillisecondTimestamp($value)) {
+        return false;
+    }
+
+    // Convert milliseconds to seconds
+    $seconds = intval($value / 1000);
+
+    // Use DateTime::createFromFormat to validate and correct the timestamp
+    try {
+        $datetime = \DateTime::createFromFormat('U', (string)$seconds);
+        if ($datetime === false) {
+            return false;
+        }
+
+        // Validate the result is a reasonable timestamp (after Unix epoch)
+        $timestamp = $datetime->getTimestamp();
+        if ($timestamp < 0) {
+            return false;
+        }
+
+        return (string)$timestamp;
+    } catch (Exception $e) {
+        return false;
+    }
+}


=====================================
lib/core/Tracker/Field/DateTime.php
=====================================
@@ -106,21 +106,8 @@ class Tracker_Field_DateTime extends \Tracker\Field\AbstractItemField implements
                 ? $requestData[$ins_id]
                 : $this->getValue();
 
-            if (! empty($value) && ! is_numeric($value)) {
-                throw new Services_Exception(tr('Invalid UNIX timestamp "%0"', $value), 400);
-            }
-
-            // Validate that the given raw timestamp value is a numeric representation and logically corresponds to a valid timestamp.
-            if ($value && is_numeric($value)) {
-                try {
-                    $datetime = DateTime::createFromFormat('U', $value);
-                    if ($datetime == false || $datetime->format('U') != $value) {
-                        throw new Services_Exception(tr('Invalid UNIX timestamp "%0"', $value), 400);
-                    }
-                } catch (Exception $e) {
-                    throw new Services_Exception($e->getMessage(), 400);
-                }
-            }
+            // Validate timestamp format - exception will bubble up to stop form submission
+            $this->validateTimestamp($value);
 
             $data['value'] = $value;
         }
@@ -223,14 +210,16 @@ class Tracker_Field_DateTime extends \Tracker\Field\AbstractItemField implements
     public function getDocumentPart(Search_Type_Factory_Interface $typeFactory)
     {
         $value = $this->getValue();
-        // possibly milliseconds from js picker
-        $value = ($value && strlen($value) > 10 && substr($value, -3) === '000') ? ($value / 1000) : $value;
-        $timestamp = $typeFactory->timestamp($value, $this->getOption('datetime') == 'd');
 
-        if ($value && strlen($value) > 10) {
-            trigger_error("Possibly incorrect timestamp value found when trying to send to search index. Tracker item " . $this->getItemId() . ", field " . $this->getConfiguration('permName') . ", value " . $value, E_USER_WARNING);
+        // Backward compatibility: Convert millisecond timestamps for existing data
+        // This handles cases where the migration hasn't been run yet
+        if ($value && strlen($value) > 10 && substr($value, -3) === '000') {
+            trigger_error("Possibly incorrect timestamp value found when trying to send to search index. Tracker item " . $this->getItemId() . ", field " . $this->getConfiguration('permName') . ", value " . $value . ". Converting milliseconds to seconds.", E_USER_WARNING);
+            $value = intval(intval($value) / 1000);
         }
 
+        $timestamp = $typeFactory->timestamp($value, $this->getOption('datetime') == 'd');
+
         $data = [
             $this->getBaseKey() => $timestamp,
         ];
@@ -346,4 +335,32 @@ class Tracker_Field_DateTime extends \Tracker\Field\AbstractItemField implements
     {
         return $this->getOption('datetime') === 'd';
     }
+
+    /**
+     * Validate timestamp format and throw exception if invalid
+     *
+     * @param mixed $value The timestamp value to validate
+     * @throws Services_Exception if timestamp is invalid
+     */
+    protected function validateTimestamp($value)
+    {
+        if (empty($value)) {
+            return;
+        }
+
+        if (! is_numeric($value)) {
+            throw new Services_Exception(tr('Invalid UNIX timestamp "%0"', $value), 400);
+        }
+
+        // Check for millisecond timestamps (length > 10 digits AND ends with '000')
+        if (strlen($value) > 10 && substr($value, -3) === '000') {
+            throw new Services_Exception(tr('Invalid timestamp format: "%0" appears to be in milliseconds. Expected seconds since Unix epoch.', $value), 400);
+        }
+
+        $datetime = DateTime::createFromFormat('U', $value);
+
+        if ($datetime == false || $datetime->format('U') != $value) {
+            throw new Services_Exception(tr('Invalid UNIX timestamp "%0"', $value), 400);
+        }
+    }
 }


=====================================
lib/core/Tracker/Field/JsCalendar.php
=====================================
@@ -78,9 +78,14 @@ class Tracker_Field_JsCalendar extends Tracker_Field_DateTime
             ? $requestData[$ins_id]
             : $this->getValue();
 
-        if (! empty($value) && ! is_int((int) $value)) {    // prevent corrupted date values getting saved (e.g. from inline edit sometimes)
-            $value = '';
-            Feedback::error(tr('Date Picker Field: "%0" is not a valid internal date value', $value));
+        if (! empty($value)) {
+            try {
+                // prevent corrupted date values getting saved (e.g. from inline edit sometimes)
+                $this->validateTimestamp($value);
+            } catch (Services_Exception $e) {
+                $value = '';
+                Feedback::error(tr('Date Picker Field: %0', $e->getMessage()));
+            }
         }
 
         // if local browser offset or timezone identifier is submitted, convert timestamp to server-based timezone



View it on GitLab: https://gitlab.com/tikiwiki/tiki/-/commit/bb055a40f0489eafa8740f6fa1c792ba7ba575b2

-- 
View it on GitLab: https://gitlab.com/tikiwiki/tiki/-/commit/bb055a40f0489eafa8740f6fa1c792ba7ba575b2
You're receiving this email because of your account on gitlab.com. Manage all notifications: https://gitlab.com/-/profile/notifications | Help: https://gitlab.com/help

_______________________________________________
TikiWiki-cvs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/tikiwiki-cvs