Better restrict tag search (#4882)

* Better restrict tag search
#fix https://github.com/FreshRSS/FreshRSS/issues/4877
Search only on full tag names and not on parts of tag names

* Better whitespace handling

3 files changed, 16 insertions, 8 deletions

diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index b63515223..cda51e5b4 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -10,6 +10,10 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		return true;
 	}
 
+	protected static function sqlConcat($s1, $s2) {
+		return 'CONCAT(' . $s1 . ',' . $s2 . ')';	//MySQL
+	}
+
 	public static function sqlHexDecode(string $x): string {
 		return 'unhex(' . $x . ')';
 	}
@@ -943,8 +947,8 @@ SQL;
 			}
 			if ($filter->getTags()) {
 				foreach ($filter->getTags() as $tag) {
-					$sub_search .= 'AND ' . $alias . 'tags LIKE ? ';
-					$values[] = "%{$tag}%";
+					$sub_search .= 'AND ' . static::sqlConcat('TRIM(' . $alias . 'tags) ', " ' #'") . ' LIKE ? ';
+					$values[] = "%{$tag} #%";
 				}
 			}
 			if ($filter->getInurl()) {
@@ -968,8 +972,8 @@ SQL;
 			}
 			if ($filter->getNotTags()) {
 				foreach ($filter->getNotTags() as $tag) {
-					$sub_search .= 'AND ' . $alias . 'tags NOT LIKE ? ';
-					$values[] = "%{$tag}%";
+					$sub_search .= 'AND ' . static::sqlConcat('TRIM(' . $alias . 'tags) ', " ' #'") . ' NOT LIKE ? ';
+					$values[] = "%{$tag} #%";
 				}
 			}
 			if ($filter->getNotInurl()) {
diff --git a/app/Models/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php
index 8039581e6..35f3ef676 100644
--- a/app/Models/EntryDAOSQLite.php
+++ b/app/Models/EntryDAOSQLite.php
@@ -10,6 +10,10 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO {
 		return false;
 	}
 
+	protected static function sqlConcat($s1, $s2) {
+		return $s1 . '||' . $s2;
+	}
+
 	public static function sqlHexDecode(string $x): string {
 		return $x;
 	}
diff --git a/tests/app/Models/SearchTest.php b/tests/app/Models/SearchTest.php
index a2832c710..fe686e7ba 100644
--- a/tests/app/Models/SearchTest.php
+++ b/tests/app/Models/SearchTest.php
@@ -318,17 +318,17 @@ class SearchTest extends PHPUnit\Framework\TestCase {
 			],
 			[
 				'#tag Hello OR (author:Alice inurl:example) OR (f:3 intitle:World) OR L:12',
-				' ((e.tags LIKE ? AND (e.title LIKE ? OR e.content LIKE ?) )) OR ((e.author LIKE ? AND e.link LIKE ? )) OR' .
+				" ((TRIM(e.tags) || ' #' LIKE ? AND (e.title LIKE ? OR e.content LIKE ?) )) OR ((e.author LIKE ? AND e.link LIKE ? )) OR" .
 					' ((e.id_feed IN (?) AND e.title LIKE ? )) OR ((e.id IN (SELECT et.id_entry FROM `_entrytag` et WHERE et.id_tag IN (?)) )) ',
-				['%tag%','%Hello%', '%Hello%', '%Alice%', '%example%', '3', '%World%', '12']
+				['%tag #%','%Hello%', '%Hello%', '%Alice%', '%example%', '3', '%World%', '12']
 			],
 			[
 				'#tag Hello (author:Alice inurl:example) (f:3 intitle:World) label:Bleu',
-				' ((e.tags LIKE ? AND (e.title LIKE ? OR e.content LIKE ?) )) AND' .
+				" ((TRIM(e.tags) || ' #' LIKE ? AND (e.title LIKE ? OR e.content LIKE ?) )) AND" .
 					' ((e.author LIKE ? AND e.link LIKE ? )) AND' .
 					' ((e.id_feed IN (?) AND e.title LIKE ? )) AND' .
 					' ((e.id IN (SELECT et.id_entry FROM `_entrytag` et, `_tag` t WHERE et.id_tag = t.id AND t.name IN (?)) )) ',
-				['%tag%', '%Hello%', '%Hello%', '%Alice%', '%example%', '3', '%World%', 'Bleu']
+				['%tag #%', '%Hello%', '%Hello%', '%Alice%', '%example%', '3', '%World%', 'Bleu']
 			],
 			[
 				'!((author:Alice intitle:hello) OR (author:Bob intitle:world))',