Fix #17793 - insert UUID (#17797)

* Add/Change table schema - Add UUID in default options
* Insert/Update - Generate uuid only not defined and not check null option
* Add/Change table schema - Hide UUID from default options if database not support & add unit test

Fix #17793

Signed-off-by: Mo Sureerat <sureemo@gmail.com>

11 files changed, 365 insertions, 10 deletions

diff --git a/libraries/classes/Html/Generator.php b/libraries/classes/Html/Generator.php
index fb841d1a47..8fa2e55415 100644
--- a/libraries/classes/Html/Generator.php
+++ b/libraries/classes/Html/Generator.php
@@ -311,6 +311,11 @@ class Generator
             $defaultFunction = $cfg['DefaultFunctions']['first_timestamp'];
         }
 
+        // For uuid field, no default function
+        if ($field['True_Type'] === 'uuid') {
+            return '';
+        }
+
         // For primary keys of type char(36) or varchar(36) UUID if the default
         // function
         // Only applies to insert mode, as it would silently trash data on updates.
diff --git a/libraries/classes/InsertEdit.php b/libraries/classes/InsertEdit.php
index 3301c529c5..3e6ab3e411 100644
--- a/libraries/classes/InsertEdit.php
+++ b/libraries/classes/InsertEdit.php
@@ -870,9 +870,9 @@ class InsertEdit
                     . $columnNameAppendix . '" value="' . $type . '">';
             }
 
-            if ($column['True_Type'] === 'bit') {
+            if (in_array($column['True_Type'], ['bit', 'uuid'], true)) {
                 $htmlOutput .= '<input type="hidden" name="fields_type'
-                    . $columnNameAppendix . '" value="bit">';
+                    . $columnNameAppendix . '" value="' . $column['True_Type'] . '">';
             }
         }
 
@@ -1808,6 +1808,18 @@ class InsertEdit
             $currentValue = "''";
         }
 
+        // For uuid type, generate uuid value
+        // if empty value but not set null or value is uuid() function
+        if (
+            $type === 'uuid'
+                && ! isset($multiEditColumnsNull[$key])
+                && ($currentValue == "''"
+                    || $currentValue == ''
+                    || $currentValue === "'uuid()'")
+        ) {
+            $currentValue = 'uuid()';
+        }
+
         return $currentValue;
     }
 
diff --git a/libraries/classes/Table.php b/libraries/classes/Table.php
index 16f202b6ce..d8c16716cd 100644
--- a/libraries/classes/Table.php
+++ b/libraries/classes/Table.php
@@ -485,7 +485,7 @@ class Table implements Stringable
      * @param string      $collation        collation
      * @param bool|string $null             with 'NULL' or 'NOT NULL'
      * @param string      $defaultType      whether default is CURRENT_TIMESTAMP,
-     *                                       NULL, NONE, USER_DEFINED
+     *                                       NULL, NONE, USER_DEFINED, UUID
      * @param string      $defaultValue     default value for USER_DEFINED
      *                                       default type
      * @param string      $extra            'AUTO_INCREMENT'
@@ -638,6 +638,11 @@ class Table implements Stringable
                         }
 
                         break;
+                    case 'UUID':
+                    case 'uuid()':
+                        $query .= ' DEFAULT uuid()';
+
+                        break;
                     case 'NONE':
                     default:
                         break;
diff --git a/libraries/classes/Twig/UtilExtension.php b/libraries/classes/Twig/UtilExtension.php
index 9e365c4dd5..41a6648ff9 100644
--- a/libraries/classes/Twig/UtilExtension.php
+++ b/libraries/classes/Twig/UtilExtension.php
@@ -83,6 +83,10 @@ class UtilExtension extends AbstractExtension
                 ['is_safe' => ['html']]
             ),
             new TwigFunction(
+                'is_uuid_supported',
+                [Util::class, 'isUUIDSupported']
+            ),
+            new TwigFunction(
                 'is_foreign_key_supported',
                 [ForeignKey::class, 'isSupported']
             ),
diff --git a/libraries/classes/Util.php b/libraries/classes/Util.php
index fd57f42ebd..d747801c1f 100644
--- a/libraries/classes/Util.php
+++ b/libraries/classes/Util.php
@@ -7,6 +7,7 @@ namespace PhpMyAdmin;
 use PhpMyAdmin\Dbal\ResultInterface;
 use PhpMyAdmin\Html\Generator;
 use PhpMyAdmin\Html\MySQLDocumentation;
+use PhpMyAdmin\Query\Compatibility;
 use PhpMyAdmin\Query\Utilities;
 use PhpMyAdmin\SqlParser\Components\Expression;
 use PhpMyAdmin\SqlParser\Context;
@@ -1757,6 +1758,14 @@ class Util
     }
 
     /**
+     * This function is to check whether database support UUID
+     */
+    public static function isUUIDSupported(): bool
+    {
+        return Compatibility::isUUIDSupported($GLOBALS['dbi']);
+    }
+
+    /**
      * Checks if the current user has a specific privilege and returns true if the
      * user indeed has that privilege or false if they don't. This function must
      * only be used for features that are available since MySQL 5, because it
diff --git a/phpstan-baseline.neon b/phpstan-baseline.neon
index 20c0526e81..0997eac0d9 100644
--- a/phpstan-baseline.neon
+++ b/phpstan-baseline.neon
@@ -10526,11 +10526,6 @@ parameters:
 			path: test/classes/InsertEditTest.php
 
 		-
-			message: "#^Parameter \\#2 \\$haystack of method PHPUnit\\\\Framework\\\\Assert\\:\\:assertStringContainsString\\(\\) expects string, mixed given\\.$#"
-			count: 26
-			path: test/classes/InsertEditTest.php
-
-		-
 			message: "#^Method PhpMyAdmin\\\\Tests\\\\IpAllowDenyTest\\:\\:proxyIPs\\(\\) return type has no value type specified in iterable type array\\.$#"
 			count: 1
 			path: test/classes/IpAllowDenyTest.php
diff --git a/templates/columns_definitions/column_attributes.twig b/templates/columns_definitions/column_attributes.twig
index a3b0284ec6..170fb71f1a 100644
--- a/templates/columns_definitions/column_attributes.twig
+++ b/templates/columns_definitions/column_attributes.twig
@@ -46,6 +46,11 @@
     <option value="CURRENT_TIMESTAMP"{{ column_meta['DefaultType'] is defined and column_meta['DefaultType'] == 'CURRENT_TIMESTAMP' ? ' selected' }}>
       CURRENT_TIMESTAMP
     </option>
+    {% if is_uuid_supported() %}
+    <option value="UUID"{{ column_meta['DefaultType'] is defined and column_meta['DefaultType'] == 'UUID' ? ' selected' }}>
+      UUID
+    </option>
+    {% endif %}
   </select>
   {% if char_editing == 'textarea' %}
     <textarea name="field_default_value[{{ column_number }}]" cols="15" class="textfield default_value">{{ default_value }}</textarea>
diff --git a/test/classes/Html/GeneratorTest.php b/test/classes/Html/GeneratorTest.php
index f483c8c008..69d15cfc7c 100644
--- a/test/classes/Html/GeneratorTest.php
+++ b/test/classes/Html/GeneratorTest.php
@@ -452,4 +452,80 @@ class GeneratorTest extends AbstractTestCase
             Generator::getServerSSL()
         );
     }
+
+    /**
+     * Test for Generator::getDefaultFunctionForField
+     *
+     * @param array  $field      field settings
+     * @param bool   $insertMode true if insert mode
+     * @param string $expected   expected result
+     * @psalm-param array<string, string|bool|null> $field
+     *
+     * @dataProvider providerForTestGetDefaultFunctionForField
+     */
+    public function testGetDefaultFunctionForField(
+        array $field,
+        bool $insertMode,
+        string $expected
+    ): void {
+        $result = Generator::getDefaultFunctionForField($field, $insertMode);
+
+        $this->assertEquals($expected, $result);
+    }
+
+    /**
+     * Data provider for Generator::getDefaultFunctionForField test
+     *
+     * @return array
+     * @psalm-return array<int, array{array<string, string|bool|null>, bool, string}>
+     */
+    public function providerForTestGetDefaultFunctionForField(): array
+    {
+        return [
+            [
+                [
+                    'True_Type' => 'GEOMETRY',
+                    'first_timestamp' => false,
+                    'Extra' => null,
+                    'Key' => '',
+                    'Type' => '',
+                    'Null' => 'NO',
+                ],
+                true,
+                'ST_GeomFromText',
+            ],
+            [
+                [
+                    'True_Type' => 'timestamp',
+                    'first_timestamp' => true,
+                    'Extra' => null,
+                    'Key' => '',
+                    'Type' => '',
+                    'Null' => 'NO',
+                ],
+                true,
+                'NOW',
+            ],
+            [
+                [
+                    'True_Type' => 'uuid',
+                    'first_timestamp' => false,
+                    'Key' => '',
+                    'Type' => '',
+                ],
+                true,
+                '',
+            ],
+            [
+                [
+                    'True_Type' => '',
+                    'first_timestamp' => false,
+                    'Key' => 'PRI',
+                    'Type' => 'char(36)',
+                ],
+                true,
+                'UUID',
+            ],
+        ];
+    }
 }
diff --git a/test/classes/InsertEditTest.php b/test/classes/InsertEditTest.php
index 549cac76df..36186fd19e 100644
--- a/test/classes/InsertEditTest.php
+++ b/test/classes/InsertEditTest.php
@@ -17,10 +17,14 @@ use ReflectionProperty;
 use stdClass;
 
 use function hash;
+use function is_object;
+use function is_scalar;
+use function is_string;
 use function mb_substr;
 use function md5;
 use function password_verify;
 use function sprintf;
+use function strval;
 
 use const MYSQLI_PRI_KEY_FLAG;
 use const MYSQLI_TYPE_DECIMAL;
@@ -538,6 +542,8 @@ class InsertEditTest extends AbstractTestCase
             ]
         );
 
+        $result = $this->parseString($result);
+
         $this->assertStringContainsString('title="comment&gt;"', $result);
 
         $this->assertStringContainsString('f1&lt;', $result);
@@ -834,6 +840,8 @@ class InsertEditTest extends AbstractTestCase
             ]
         );
 
+        $result = $this->parseString($result);
+
         $this->assertStringContainsString(
             '<textarea name="fieldsb" class="char charField" '
             . 'data-maxlength="10" rows="7" cols="1" dir="abc/" '
@@ -1183,6 +1191,8 @@ class InsertEditTest extends AbstractTestCase
             ]
         );
 
+        $result = $this->parseString($result);
+
         $this->assertStringContainsString('<input type="hidden" name="fields_typeb" value="datetime">', $result);
 
         // case 4: (else -> date)
@@ -1209,7 +1219,65 @@ class InsertEditTest extends AbstractTestCase
             ]
         );
 
+        $result = $this->parseString($result);
+
         $this->assertStringContainsString('<input type="hidden" name="fields_typeb" value="date">', $result);
+
+        // case 5: (else -> bit)
+        $column['True_Type'] = 'bit';
+        $result = $this->callFunction(
+            $this->insertEdit,
+            InsertEdit::class,
+            'getValueColumnForOtherDatatypes',
+            [
+                $column,
+                'defchar',
+                'a',
+                'b',
+                'c',
+                22,
+                '&lt;',
+                12,
+                1,
+                '/',
+                '&lt;',
+                "foo\nbar",
+                $extracted_columnspec,
+                false,
+            ]
+        );
+
+        $result = $this->parseString($result);
+
+        $this->assertStringContainsString('<input type="hidden" name="fields_typeb" value="bit">', $result);
+
+        // case 6: (else -> uuid)
+        $column['True_Type'] = 'uuid';
+        $result = $this->callFunction(
+            $this->insertEdit,
+            InsertEdit::class,
+            'getValueColumnForOtherDatatypes',
+            [
+                $column,
+                'defchar',
+                'a',
+                'b',
+                'c',
+                22,
+                '&lt;',
+                12,
+                1,
+                '/',
+                '&lt;',
+                "foo\nbar",
+                $extracted_columnspec,
+                false,
+            ]
+        );
+
+        $result = $this->parseString($result);
+
+        $this->assertStringContainsString('<input type="hidden" name="fields_typeb" value="uuid">', $result);
     }
 
     /**
@@ -1316,6 +1384,8 @@ class InsertEditTest extends AbstractTestCase
             [$url_params]
         );
 
+        $result = $this->parseString($result);
+
         $this->assertStringContainsString('index.php?route=/table/change', $result);
 
         $this->assertStringContainsString('ShowFunctionFields=1&ShowFieldTypesInDataEditView=0', $result);
@@ -1552,7 +1622,7 @@ class InsertEditTest extends AbstractTestCase
         unset($column['Default']);
         $column['True_Type'] = 'char';
 
-        $result = $this->callFunction(
+        $result = (array) $this->callFunction(
             $this->insertEdit,
             InsertEdit::class,
             'getSpecialCharsAndBackupFieldForInsertingMode',
@@ -1798,7 +1868,7 @@ class InsertEditTest extends AbstractTestCase
         $GLOBALS['dbi'] = $dbi;
         $this->insertEdit = new InsertEdit($GLOBALS['dbi']);
 
-        $result = $this->callFunction(
+        $result = (array) $this->callFunction(
             $this->insertEdit,
             InsertEdit::class,
             'getWarningMessages',
@@ -2472,6 +2542,46 @@ class InsertEditTest extends AbstractTestCase
         );
 
         $this->assertEquals("''", $result);
+
+        // case 10
+        $result = $this->insertEdit->getCurrentValueForDifferentTypes(
+            false,
+            '0',
+            ['uuid'],
+            '',
+            [],
+            0,
+            ['a'],
+            [],
+            [1],
+            true,
+            true,
+            '',
+            'test_table',
+            []
+        );
+
+        $this->assertEquals('uuid()', $result);
+
+        // case 11
+        $result = $this->insertEdit->getCurrentValueForDifferentTypes(
+            false,
+            '0',
+            ['uuid'],
+            'uuid()',
+            [],
+            0,
+            ['a'],
+            [],
+            [1],
+            true,
+            true,
+            '',
+            'test_table',
+            []
+        );
+
+        $this->assertEquals('uuid()', $result);
     }
 
     /**
@@ -2769,6 +2879,8 @@ class InsertEditTest extends AbstractTestCase
             ]
         );
 
+        $actual = $this->parseString($actual);
+
         $this->assertStringContainsString('col', $actual);
         $this->assertStringContainsString('<option>AES_ENCRYPT</option>', $actual);
         $this->assertStringContainsString('<span class="column_type" dir="ltr">varchar(20)</span>', $actual);
@@ -2827,6 +2939,9 @@ class InsertEditTest extends AbstractTestCase
                 '',
             ]
         );
+
+        $actual = $this->parseString($actual);
+
         $this->assertStringContainsString('qwerty', $actual);
         $this->assertStringContainsString('<option>UUID</option>', $actual);
         $this->assertStringContainsString('<span class="column_type" dir="ltr">datetime</span>', $actual);
@@ -3083,4 +3198,24 @@ class InsertEditTest extends AbstractTestCase
             $actual
         );
     }
+
+    /**
+     * Convert mixed type value to string
+     *
+     * @param mixed $value
+     *
+     * @return string
+     */
+    private function parseString($value)
+    {
+        if (is_string($value)) {
+            return $value;
+        }
+
+        if (is_object($value) || is_scalar($value)) {
+            return strval($value);
+        }
+
+        return '';
+    }
 }
diff --git a/test/classes/TableTest.php b/test/classes/TableTest.php
index c95cff3d68..55230856a3 100644
--- a/test/classes/TableTest.php
+++ b/test/classes/TableTest.php
@@ -706,6 +706,54 @@ class TableTest extends AbstractTestCase
             $query
         );
 
+        //$default_type is UUID
+        $type = 'UUID';
+        $default_type = 'UUID';
+        $move_to = '';
+        $query = Table::generateFieldSpec(
+            $name,
+            $type,
+            $length,
+            $attribute,
+            $collation,
+            $null,
+            $default_type,
+            $default_value,
+            $extra,
+            '',
+            $virtuality,
+            $expression,
+            $move_to
+        );
+        $this->assertEquals(
+            '`PMA_name` UUID PMA_attribute NULL DEFAULT uuid()',
+            $query
+        );
+
+        //$default_type is uuid()
+        $type = 'UUID';
+        $default_type = 'uuid()';
+        $move_to = '';
+        $query = Table::generateFieldSpec(
+            $name,
+            $type,
+            $length,
+            $attribute,
+            $collation,
+            $null,
+            $default_type,
+            $default_value,
+            $extra,
+            '',
+            $virtuality,
+            $expression,
+            $move_to
+        );
+        $this->assertEquals(
+            '`PMA_name` UUID PMA_attribute NULL DEFAULT uuid()',
+            $query
+        );
+
         //$default_type is NONE
         $type = 'BOOLEAN';
         $default_type = 'NONE';
diff --git a/test/classes/UtilTest.php b/test/classes/UtilTest.php
index 1c5a254aae..3e00a0e68e 100644
--- a/test/classes/UtilTest.php
+++ b/test/classes/UtilTest.php
@@ -2446,4 +2446,65 @@ class UtilTest extends AbstractTestCase
             Util::getScriptNameForOption($target, $location)
         );
     }
+
+    /**
+     * Tests for Util::testIsUUIDSupported() method.
+     *
+     * @param bool $isMariaDB True if mariadb
+     * @param int  $version   Database version as integer
+     * @param bool $expected  Expected Result
+     *
+     * @dataProvider provideForTestIsUUIDSupported
+     */
+    public function testIsUUIDSupported(bool $isMariaDB, int $version, bool $expected): void
+    {
+        $dbi = $this->getMockBuilder(DatabaseInterface::class)
+            ->disableOriginalConstructor()
+            ->getMock();
+
+        $dbi->expects($this->any())
+            ->method('isMariaDB')
+            ->will($this->returnValue($isMariaDB));
+
+        $dbi->expects($this->any())
+            ->method('getVersion')
+            ->will($this->returnValue($version));
+
+        $oldDbi = $GLOBALS['dbi'];
+        $GLOBALS['dbi'] = $dbi;
+        $this->assertEquals(Util::isUUIDSupported(), $expected);
+        $GLOBALS['dbi'] = $oldDbi;
+    }
+
+    /**
+     * Data provider for isUUIDSupported() tests.
+     *
+     * @return array
+     * @psalm-return array<int, array{bool, int, bool}>
+     */
+    public function provideForTestIsUUIDSupported(): array
+    {
+        return [
+            [
+                false,
+                60100,
+                false,
+            ],
+            [
+                false,
+                100700,
+                false,
+            ],
+            [
+                true,
+                60100,
+                false,
+            ],
+            [
+                true,
+                100700,
+                true,
+            ],
+        ];
+    }
 }