From 0324df6f889f18500cc8d201fdc2845f3e4d1acf Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Mon, 30 Oct 2023 20:47:50 +0100 Subject: SQL increase length of VARCHAR fields (#5756) * SQL increase length of VARCHAR fields Increase length of all fields, keeping the limits for: * Unique indexes on UTF-8: 191 bytes for MySQL; * Unique indexes on ASCII: 767 bytes for MySQL; * Max URL for external tools: 32768 characters; * Max VARCHAR: 65535 bytes for MySQL; Follow-up of https://github.com/FreshRSS/FreshRSS/pull/5038 Fix https://github.com/FreshRSS/FreshRSS/issues/4986 * Fix length test --- app/Models/Category.php | 2 +- app/Models/EntryDAO.php | 20 ++++++++++---------- app/Models/FeedDAO.php | 4 ++-- app/Models/TagDAO.php | 4 ++-- 4 files changed, 15 insertions(+), 15 deletions(-) (limited to 'app/Models') diff --git a/app/Models/Category.php b/app/Models/Category.php index 3bb64df8b..370c49709 100644 --- a/app/Models/Category.php +++ b/app/Models/Category.php @@ -143,7 +143,7 @@ class FreshRSS_Category extends Minz_Model { } public function _name(string $value): void { - $this->name = mb_strcut(trim($value), 0, 255, 'UTF-8'); + $this->name = mb_strcut(trim($value), 0, FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE, 'UTF-8'); } /** @param array|FreshRSS_Feed $values */ diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index b9f8d57cb..f7e9ffddc 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -109,18 +109,18 @@ SQL; } if ($this->addEntryPrepared) { $this->addEntryPrepared->bindParam(':id', $valuesTmp['id']); - $valuesTmp['guid'] = substr($valuesTmp['guid'], 0, 760); + $valuesTmp['guid'] = substr($valuesTmp['guid'], 0, 767); $valuesTmp['guid'] = safe_ascii($valuesTmp['guid']); $this->addEntryPrepared->bindParam(':guid', $valuesTmp['guid']); - $valuesTmp['title'] = mb_strcut($valuesTmp['title'], 0, 255, 'UTF-8'); + $valuesTmp['title'] = mb_strcut($valuesTmp['title'], 0, 65535, 'UTF-8'); $valuesTmp['title'] = safe_utf8($valuesTmp['title']); $this->addEntryPrepared->bindParam(':title', $valuesTmp['title']); - $valuesTmp['author'] = mb_strcut($valuesTmp['author'], 0, 255, 'UTF-8'); + $valuesTmp['author'] = mb_strcut($valuesTmp['author'], 0, 65535, 'UTF-8'); $valuesTmp['author'] = safe_utf8($valuesTmp['author']); $this->addEntryPrepared->bindParam(':author', $valuesTmp['author']); $valuesTmp['content'] = safe_utf8($valuesTmp['content']); $this->addEntryPrepared->bindParam(':content', $valuesTmp['content']); - $valuesTmp['link'] = substr($valuesTmp['link'], 0, 1023); + $valuesTmp['link'] = substr($valuesTmp['link'], 0, 32768); $valuesTmp['link'] = safe_ascii($valuesTmp['link']); $this->addEntryPrepared->bindParam(':link', $valuesTmp['link']); $valuesTmp['date'] = min($valuesTmp['date'], 2147483647); @@ -134,7 +134,7 @@ SQL; $valuesTmp['is_favorite'] = $valuesTmp['is_favorite'] ? 1 : 0; $this->addEntryPrepared->bindParam(':is_favorite', $valuesTmp['is_favorite'], PDO::PARAM_INT); $this->addEntryPrepared->bindParam(':id_feed', $valuesTmp['id_feed'], PDO::PARAM_INT); - $valuesTmp['tags'] = mb_strcut($valuesTmp['tags'], 0, 1023, 'UTF-8'); + $valuesTmp['tags'] = mb_strcut($valuesTmp['tags'], 0, 65535, 'UTF-8'); $valuesTmp['tags'] = safe_utf8($valuesTmp['tags']); $this->addEntryPrepared->bindParam(':tags', $valuesTmp['tags']); if (!isset($valuesTmp['attributes'])) { @@ -215,18 +215,18 @@ SQL; $this->updateEntryPrepared = $this->pdo->prepare($sql) ?: null; } if ($this->updateEntryPrepared) { - $valuesTmp['guid'] = substr($valuesTmp['guid'], 0, 760); + $valuesTmp['guid'] = substr($valuesTmp['guid'], 0, 767); $valuesTmp['guid'] = safe_ascii($valuesTmp['guid']); $this->updateEntryPrepared->bindParam(':guid', $valuesTmp['guid']); - $valuesTmp['title'] = mb_strcut($valuesTmp['title'], 0, 255, 'UTF-8'); + $valuesTmp['title'] = mb_strcut($valuesTmp['title'], 0, 65535, 'UTF-8'); $valuesTmp['title'] = safe_utf8($valuesTmp['title']); $this->updateEntryPrepared->bindParam(':title', $valuesTmp['title']); - $valuesTmp['author'] = mb_strcut($valuesTmp['author'], 0, 255, 'UTF-8'); + $valuesTmp['author'] = mb_strcut($valuesTmp['author'], 0, 65535, 'UTF-8'); $valuesTmp['author'] = safe_utf8($valuesTmp['author']); $this->updateEntryPrepared->bindParam(':author', $valuesTmp['author']); $valuesTmp['content'] = safe_utf8($valuesTmp['content']); $this->updateEntryPrepared->bindParam(':content', $valuesTmp['content']); - $valuesTmp['link'] = substr($valuesTmp['link'], 0, 1023); + $valuesTmp['link'] = substr($valuesTmp['link'], 0, 32768); $valuesTmp['link'] = safe_ascii($valuesTmp['link']); $this->updateEntryPrepared->bindParam(':link', $valuesTmp['link']); $valuesTmp['date'] = min($valuesTmp['date'], 2147483647); @@ -243,7 +243,7 @@ SQL; $this->updateEntryPrepared->bindValue(':is_favorite', $valuesTmp['is_favorite'] ? 1 : 0, PDO::PARAM_INT); } $this->updateEntryPrepared->bindParam(':id_feed', $valuesTmp['id_feed'], PDO::PARAM_INT); - $valuesTmp['tags'] = mb_strcut($valuesTmp['tags'], 0, 1023, 'UTF-8'); + $valuesTmp['tags'] = mb_strcut($valuesTmp['tags'], 0, 65535, 'UTF-8'); $valuesTmp['tags'] = safe_utf8($valuesTmp['tags']); $this->updateEntryPrepared->bindParam(':tags', $valuesTmp['tags']); if (!isset($valuesTmp['attributes'])) { diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index 508d38bfc..127702765 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -58,10 +58,10 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo { $valuesTmp['category'], mb_strcut(trim($valuesTmp['name']), 0, FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE, 'UTF-8'), $valuesTmp['website'], - sanitizeHTML($valuesTmp['description'], '', 1023), + sanitizeHTML($valuesTmp['description'], ''), $valuesTmp['lastUpdate'], isset($valuesTmp['priority']) ? (int)$valuesTmp['priority'] : FreshRSS_Feed::PRIORITY_MAIN_STREAM, - mb_strcut($valuesTmp['pathEntries'], 0, 511, 'UTF-8'), + mb_strcut($valuesTmp['pathEntries'], 0, 65535, 'UTF-8'), base64_encode($valuesTmp['httpAuth']), isset($valuesTmp['error']) ? (int)$valuesTmp['error'] : 0, isset($valuesTmp['ttl']) ? (int)$valuesTmp['ttl'] : FreshRSS_Feed::TTL_DEFAULT, diff --git a/app/Models/TagDAO.php b/app/Models/TagDAO.php index b41a6de72..8c0b94c46 100644 --- a/app/Models/TagDAO.php +++ b/app/Models/TagDAO.php @@ -20,7 +20,7 @@ WHERE NOT EXISTS (SELECT 1 FROM `_category` WHERE name = TRIM(?)) SQL; $stm = $this->pdo->prepare($sql); - $valuesTmp['name'] = mb_strcut(trim($valuesTmp['name']), 0, 63, 'UTF-8'); + $valuesTmp['name'] = mb_strcut(trim($valuesTmp['name']), 0, FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE, 'UTF-8'); if (!isset($valuesTmp['attributes'])) { $valuesTmp['attributes'] = []; } @@ -61,7 +61,7 @@ UPDATE `_tag` SET name=? WHERE id=? AND NOT EXISTS (SELECT 1 FROM `_category` WHERE name = ?) SQL; - $name = mb_strcut(trim($name), 0, 63, 'UTF-8'); + $name = mb_strcut(trim($name), 0, FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE, 'UTF-8'); $stm = $this->pdo->prepare($sql); if ($stm !== false && $stm->bindValue(':id', $id, PDO::PARAM_INT) && -- cgit v1.2.3