mysql: change collation of tag table

utf8mb4_unicode_ci considers that 'caché' is equal to 'cache' which
can lead to attaching incorrect tags to entries. This issue is due to
some unicode normalization done by MySQL.

utf8mb4_bin makes no unicode normalization, letting wallabag to consider
'cache' and 'caché' as two different tags.

We change the collation of the whole table as Doctrine does not support
setting a collation on a column for a specific platform (it tries to
apply utf8mb4_bin even for pgsql and sqlite).

Fixes #3302

Signed-off-by: Kevin Decherf <kevin@kdecherf.com>

3 files changed, 80 insertions, 1 deletions

diff --git a/app/DoctrineMigrations/Version20190511165128.php b/app/DoctrineMigrations/Version20190511165128.php
new file mode 100644
index 00000000..7b6b1bec
--- /dev/null
+++ b/app/DoctrineMigrations/Version20190511165128.php
@@ -0,0 +1,30 @@
+<?php
+
+declare(strict_types=1);
+
+namespace Application\Migrations;
+
+use Doctrine\DBAL\Schema\Schema;
+use Wallabag\CoreBundle\Doctrine\WallabagMigration;
+
+/**
+ * Convert tab label to utf8mb4_bin (MySQL only).
+ */
+final class Version20190511165128 extends WallabagMigration
+{
+    public function up(Schema $schema): void
+    {
+        $this->skipIf('mysql' !== $this->connection->getDatabasePlatform()->getName(), 'This migration only apply to MySQL');
+
+        $this->addSql('ALTER TABLE ' . $this->getTable('tag') . ' CHANGE `label` `label` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;');
+        $this->addSql('ALTER TABLE ' . $this->getTable('tag') . ' CHANGE `slug` `slug` VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;');
+    }
+
+    public function down(Schema $schema): void
+    {
+        $this->skipIf('mysql' !== $this->connection->getDatabasePlatform()->getName(), 'This migration only apply to MySQL');
+
+        $this->addSql('ALTER TABLE ' . $this->getTable('tag') . ' CHANGE `slug` `slug` VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;');
+        $this->addSql('ALTER TABLE ' . $this->getTable('tag') . ' CHANGE `label` `label` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;');
+    }
+}
diff --git a/src/Wallabag/CoreBundle/Entity/Tag.php b/src/Wallabag/CoreBundle/Entity/Tag.php
index a6dc8c50..95c47bbd 100644
--- a/src/Wallabag/CoreBundle/Entity/Tag.php
+++ b/src/Wallabag/CoreBundle/Entity/Tag.php
@@ -13,7 +13,10 @@ use JMS\Serializer\Annotation\XmlRoot;
  * Tag.
  *
  * @XmlRoot("tag")
- * @ORM\Table(name="`tag`")
+ * @ORM\Table(
+ *     name="`tag`",
+ *     options={"collate"="utf8mb4_bin", "charset"="utf8mb4"},
+ * )
  * @ORM\Entity(repositoryClass="Wallabag\CoreBundle\Repository\TagRepository")
  * @ExclusionPolicy("all")
  */
diff --git a/tests/Wallabag/CoreBundle/Controller/TagControllerTest.php b/tests/Wallabag/CoreBundle/Controller/TagControllerTest.php
index be17dcf5..47c83a7b 100644
--- a/tests/Wallabag/CoreBundle/Controller/TagControllerTest.php
+++ b/tests/Wallabag/CoreBundle/Controller/TagControllerTest.php
@@ -221,4 +221,50 @@ class TagControllerTest extends WallabagCoreTestCase
         $this->assertInstanceOf(Tag::class, $newTag, 'Tag "specific label" exists.');
         $this->assertTrue($newTag->hasEntry($freshEntry), 'Tag "specific label" is assigned to the entry.');
     }
+
+    public function testAddUnicodeTagLabel()
+    {
+        $this->logInAs('admin');
+        $client = $this->getClient();
+
+        $entry = new Entry($this->getLoggedInUser());
+        $entry->setUrl('http://0.0.0.0/tag-caché');
+        $this->getEntityManager()->persist($entry);
+        $this->getEntityManager()->flush();
+        $this->getEntityManager()->clear();
+
+        $crawler = $client->request('GET', '/view/' . $entry->getId());
+
+        $form = $crawler->filter('form[name=tag]')->form();
+
+        $data = [
+            'tag[label]' => 'cache',
+        ];
+
+        $client->submit($form, $data);
+
+        $crawler = $client->request('GET', '/view/' . $entry->getId());
+
+        $form = $crawler->filter('form[name=tag]')->form();
+
+        $data = [
+            'tag[label]' => 'caché',
+        ];
+
+        $client->submit($form, $data);
+
+        $newEntry = $client->getContainer()
+            ->get('doctrine.orm.entity_manager')
+            ->getRepository('WallabagCoreBundle:Entry')
+            ->find($entry->getId());
+
+        $tags = $newEntry->getTags()->toArray();
+        foreach ($tags as $key => $tag) {
+            $tags[$key] = $tag->getLabel();
+        }
+
+        $this->assertGreaterThanOrEqual(2, \count($tags));
+        $this->assertNotFalse(array_search('cache', $tags, true), 'Tag cache is assigned to the entry');
+        $this->assertNotFalse(array_search('caché', $tags, true), 'Tag caché is assigned to the entry');
+    }
 }