nicer file type search

* the previous one could allow a SQL injection, since the
  `opts.filetype` value came straight from the browser

* this more precise regex match will not produce spurious
  matches (which were very unlikely, true, but still, let's be
  precise) (`video/movingimages` would have matched `%image%`!)

1 files changed, 12 insertions, 1 deletions

diff --git a/packages/backend/src/core/SearchService.ts b/packages/backend/src/core/SearchService.ts
index 9dc53dafef..4fd1d72f22 100644
--- a/packages/backend/src/core/SearchService.ts
+++ b/packages/backend/src/core/SearchService.ts
@@ -220,7 +220,18 @@ export class SearchService {
 			}
 
 			if (opts.filetype) {
-				query.andWhere(`note."attachedFileTypes"::varchar LIKE '%${opts.filetype}%'`);
+				// this is very ugly, but the "correct" solution would
+				// be `and exists (select 1 from
+				// unnest(note."attachedFileTypes") x(t) where t like
+				// :type)` and I can't find a way to get TypeORM to
+				// generate that; this hack works because `~*` is
+				// "regexp match, ignoring case" and the stringified
+				// version of an array of varchars (which is what
+				// `attachedFileTypes` is) looks like `{foo,bar}`, so
+				// we're looking for opts.filetype as the first half
+				// of a MIME type, either at start of the array (after
+				// the `{`) or later (after a `,`)
+				query.andWhere(`note."attachedFileTypes"::varchar ~* :type`, { type: `[{,]${opts.filetype}/` });
 			}
 
 			this.queryService.generateVisibilityQuery(query, me);