Directus `search` query parameter allows enumeration of non permitted fields

@@ -1,17 +1,18 @@
+import type { NumericValue } from '@directus/types';
 import type { Knex } from 'knex';
+import { NumberDatabaseHelper, type NumberInfo } from '../types.js';
 import { maybeStringifyBigInt } from '../utils/maybe-stringify-big-int.js';
 import { numberInRange } from '../utils/number-in-range.js';
-import { NumberDatabaseHelper, type NumberInfo } from '../types.js';
-import type { NumericValue } from '@directus/types';
 
 export class NumberHelperMSSQL extends NumberDatabaseHelper {
 	override addSearchCondition(
 		dbQuery: Knex.QueryBuilder,
 		collection: string,
 		name: string,
 		value: NumericValue,
+		logical: 'and' | 'or',
 	): Knex.QueryBuilder {
-		return dbQuery.orWhere({ [`${collection}.${name}`]: maybeStringifyBigInt(value) });
+		return dbQuery[logical].where({ [`${collection}.${name}`]: maybeStringifyBigInt(value) });
 	}
 
 	override isNumberValid(value: NumericValue, info: NumberInfo) {

@@ -1,6 +1,6 @@
+import type { NumericValue } from '@directus/types';
 import type { Knex } from 'knex';
 import { NumberDatabaseHelper } from '../types.js';
-import type { NumericValue } from '@directus/types';
 import { maybeStringifyBigInt } from '../utils/maybe-stringify-big-int.js';
 
 export class NumberHelperOracle extends NumberDatabaseHelper {
@@ -9,7 +9,8 @@ export class NumberHelperOracle extends NumberDatabaseHelper {
 		collection: string,
 		name: string,
 		value: NumericValue,
+		logical: 'and' | 'or',
 	): Knex.QueryBuilder {
-		return dbQuery.orWhere({ [`${collection}.${name}`]: maybeStringifyBigInt(value) });
+		return dbQuery[logical].where({ [`${collection}.${name}`]: maybeStringifyBigInt(value) });
 	}
 }

@@ -1,6 +1,6 @@
+import type { NumericValue } from '@directus/types';
 import type { Knex } from 'knex';
 import { NumberDatabaseHelper } from '../types.js';
-import type { NumericValue } from '@directus/types';
 import { maybeStringifyBigInt } from '../utils/maybe-stringify-big-int.js';
 
 export class NumberHelperSQLite extends NumberDatabaseHelper {
@@ -9,7 +9,8 @@ export class NumberHelperSQLite extends NumberDatabaseHelper {
 		collection: string,
 		name: string,
 		value: NumericValue,
+		logical: 'and' | 'or',
 	): Knex.QueryBuilder {
-		return dbQuery.orWhere({ [`${collection}.${name}`]: maybeStringifyBigInt(value) });
+		return dbQuery[logical].where({ [`${collection}.${name}`]: maybeStringifyBigInt(value) });
 	}
 }

@@ -1,6 +1,6 @@
+import type { NumericType, NumericValue } from '@directus/types';
 import type { Knex } from 'knex';
 import { DatabaseHelper } from '../types.js';
-import type { NumericType, NumericValue } from '@directus/types';
 
 export type NumberInfo = {
 	type: NumericType;
@@ -14,8 +14,9 @@ export abstract class NumberDatabaseHelper extends DatabaseHelper {
 		collection: string,
 		name: string,
 		value: NumericValue,
+		logical: 'and' | 'or',
 	): Knex.QueryBuilder {
-		return dbQuery.orWhere({ [`${collection}.${name}`]: value });
+		return dbQuery[logical].where({ [`${collection}.${name}`]: value });
 	}
 
 	isNumberValid(_value: NumericValue, _info: NumberInfo) {

@@ -1,12 +1,11 @@
-import type { Accountability, Filter, Permission, Query, SchemaOverview } from '@directus/types';
+import type { Accountability, Query, SchemaOverview } from '@directus/types';
 import type { Knex } from 'knex';
+import { isArray } from 'lodash-es';
+import { getAstFromQuery } from '../database/get-ast-from-query/get-ast-from-query.js';
 import getDatabase from '../database/index.js';
-import { fetchPermissions } from '../permissions/lib/fetch-permissions.js';
-import { fetchPolicies } from '../permissions/lib/fetch-policies.js';
-import { dedupeAccess } from '../permissions/modules/process-ast/utils/dedupe-access.js';
-import { validateAccess } from '../permissions/modules/validate-access/validate-access.js';
+import { runAst } from '../database/run-ast/run-ast.js';
+import { processAst } from '../permissions/modules/process-ast/process-ast.js';
 import type { AbstractServiceOptions } from '../types/index.js';
-import { applyFilter, applySearch } from '../utils/apply-query.js';
 
 export class MetaService {
 	knex: Knex;
@@ -39,124 +38,39 @@ export class MetaService {
 	}
 
 	async totalCount(collection: string): Promise<number> {
-		const dbQuery = this.knex(collection);
-
-		let hasJoins = false;
-
-		if (this.accountability && this.accountability.admin === false) {
-			const context = { knex: this.knex, schema: this.schema };
-
-			await validateAccess(
-				{
-					accountability: this.accountability,
-					action: 'read',
-					collection,
-				},
-				context,
-			);
-
-			const policies = await fetchPolicies(this.accountability, context);
-
-			const permissions = await fetchPermissions(
-				{
-					action: 'read',
-					policies,
-					accountability: this.accountability,
-				},
-				context,
-			);
-
-			const collectionPermissions = permissions.filter((permission) => permission.collection === collection);
-
-			const rules = dedupeAccess(collectionPermissions);
-			const cases = rules.map(({ rule }) => rule);
-
-			const filter = {
-				_or: cases,
-			};
-
-			const result = applyFilter(this.knex, this.schema, dbQuery, filter, collection, {}, cases, permissions);
-			hasJoins = result.hasJoins;
-		}
-
-		if (hasJoins) {
-			const primaryKeyName = this.schema.collections[collection]!.primary;
-
-			dbQuery.countDistinct({ count: [`${collection}.${primaryKeyName}`] });
-		} else {
-			dbQuery.count('*', { as: 'count' });
-		}
-
-		const result = await dbQuery.first();
-
-		return Number(result?.count ?? 0);
+		return this.filterCount(collection, {});
 	}
 
 	async filterCount(collection: string, query: Query): Promise<number> {
-		const dbQuery = this.knex(collection);
-
-		let filter = query.filter || {};
-		let hasJoins = false;
-		let cases: Filter[] = [];
-		let permissions: Permission[] = [];
-
-		if (this.accountability && this.accountability.admin === false) {
-			const context = { knex: this.knex, schema: this.schema };
-
-			await validateAccess(
-				{
-					accountability: this.accountability,
-					action: 'read',
-					collection,
-				},
-				context,
-			);
-
-			const policies = await fetchPolicies(this.accountability, context);
-
-			permissions = await fetchPermissions(
-				{
-					action: 'read',
-					policies,
-					accountability: this.accountability,
-				},
-				context,
-			);
-
-			const collectionPermissions = permissions.filter((permission) => permission.collection === collection);
-
-			const rules = dedupeAccess(collectionPermissions);
-			cases = rules.map(({ rule }) => rule);
-
-			const permissionsFilter = {
-				_or: cases,
-			};
-
-			if (Object.keys(filter).length > 0) {
-				filter = { _and: [permissionsFilter, filter] };
-			} else {
-				filter = permissionsFilter;
-			}
-		}
-
-		if (Object.keys(filter).length > 0) {
-			({ hasJoins } = applyFilter(this.knex, this.schema, dbQuery, filter, collection, {}, cases, permissions));
-		}
-
-		if (query.search) {
-			applySearch(this.knex, this.schema, dbQuery, query.search, collection);
-		}
-
-		if (hasJoins) {
-			const primaryKeyName = this.schema.collections[collection]!.primary;
+		const aggregateQuery: Query = {
+			aggregate: {
+				count: ['*'],
+			},
+			search: query.search ?? null,
+			filter: query.filter ?? null,
+		};
+
+		let ast = await getAstFromQuery(
+			{
+				collection,
+				query: aggregateQuery,
+				accountability: this.accountability,
+			},
+			{
+				schema: this.schema,
+				knex: this.knex,
+			},
+		);
 
-			dbQuery.countDistinct({ count: [`${collection}.${primaryKeyName}`] });
-		} else {
-			dbQuery.count('*', { as: 'count' });
-		}
+		ast = await processAst(
+			{ ast, action: 'read', accountability: this.accountability },
+			{ knex: this.knex, schema: this.schema },
+		);
 
-		const result = await dbQuery.first();
+		const records = await runAst(ast, this.schema, this.accountability, {
+			knex: this.knex,
+		});
 
-		return Number(result?.count ?? 0);
+		return Number((isArray(records) ? records[0]?.['count'] : records?.['count']) ?? 0);
 	}
 }

@@ -1,5 +1,5 @@
-import type { SchemaOverview } from '@directus/types';
-import knex from 'knex';
+import type { Permission, SchemaOverview } from '@directus/types';
+import knex, { Knex } from 'knex';
 import { MockClient, createTracker } from 'knex-mock-client';
 import { describe, expect, test, vi } from 'vitest';
 import { applyFilter, applySearch } from './apply-query.js';
@@ -28,6 +28,20 @@ const FAKE_SCHEMA: SchemaOverview = {
 					validation: null,
 					alias: false,
 				},
+				text1: {
+					field: 'text',
+					defaultValue: null,
+					nullable: false,
+					generated: false,
+					type: 'text',
+					dbType: null,
+					precision: null,
+					scale: null,
+					special: [],
+					note: null,
+					validation: null,
+					alias: false,
+				},
 				float: {
 					field: 'float',
 					defaultValue: null,
@@ -76,10 +90,26 @@ const FAKE_SCHEMA: SchemaOverview = {
 	relations: [],
 };
 
+const permissions = [
+	{
+		collection: 'test',
+		action: 'read',
+		fields: ['text', 'float', 'integer', 'id'],
+		permissions: {
+			text: {},
+		},
+	},
+] as unknown as Permission[];
+
 class Client_SQLite3 extends MockClient {}
 
 describe('applySearch', () => {
 	function mockDatabase(dbClient: string = 'Client_SQLite3') {
+		const whereQueries = {
+			whereRaw: vi.fn(() => self),
+			where: vi.fn(() => self),
+		};
+
 		const self: Record<string, any> = {
 			client: {
 				constructor: {
@@ -89,6 +119,8 @@ describe('applySearch', () => {
 			andWhere: vi.fn(() => self),
 			orWhere: vi.fn(() => self),
 			orWhereRaw: vi.fn(() => self),
+			and: whereQueries,
+			or: whereQueries,
 		};
 
 		return self;
@@ -98,58 +130,239 @@ describe('applySearch', () => {
 		'Prevent %s from being cast to number',
 		async (number) => {
 			const db = mockDatabase();
+			const queryBuilder = db as any;
 
-			db['andWhere'].mockImplementation((callback: () => void) => {
-				// detonate the andWhere function
-				callback.call(db);
-				return db;
+			db['andWhere'].mockImplementation((callback: (queryBuilder: Knex.QueryBuilder) => void) => {
+				callback(queryBuilder);
+				return queryBuilder;
 			});
 
-			applySearch(db as any, FAKE_SCHEMA, db as any, number, 'test');
+			queryBuilder['orWhere'].mockImplementation((callback: (queryBuilder: Knex.QueryBuilder) => void) => {
+				callback(queryBuilder);
+				return queryBuilder;
+			});
+
+			applySearch(db as any, FAKE_SCHEMA, queryBuilder, number, 'test', {}, permissions);
 
 			expect(db['andWhere']).toBeCalledTimes(1);
-			expect(db['orWhere']).toBeCalledTimes(0);
-			expect(db['orWhereRaw']).toBeCalledTimes(1);
-			expect(db['orWhereRaw']).toBeCalledWith('LOWER(??) LIKE ?', ['test.text', `%${number.toLowerCase()}%`]);
+			expect(queryBuilder['orWhere']).toBeCalledTimes(1);
+			expect(queryBuilder['orWhereRaw']).toBeCalledTimes(0);
+			expect(queryBuilder['and']['whereRaw']).toBeCalledTimes(1);
+
+			expect(queryBuilder['and']['whereRaw']).toBeCalledWith('LOWER(??) LIKE ?', [
+				'test.text',
+				`%${number.toLowerCase()}%`,
+			]);
 		},
 	);
 
 	test.each(['1234', '-128', '12.34'])('Casting number %s', async (number) => {
 		const db = mockDatabase();
+		const queryBuilder = db as any;
+
+		db['andWhere'].mockImplementation((callback: (queryBuilder: Knex.QueryBuilder) => void) => {
+			callback(queryBuilder);
+			return queryBuilder;
+		});
 
-		db['andWhere'].mockImplementation((callback: () => void) => {
-			// detonate the andWhere function
-			callback.call(db);
-			return db;
+		queryBuilder['orWhere'].mockImplementation((callback: (queryBuilder: Knex.QueryBuilder) => void) => {
+			callback(queryBuilder);
+			return queryBuilder;
 		});
 
-		applySearch(db as any, FAKE_SCHEMA, db as any, number, 'test');
+		applySearch(db as any, FAKE_SCHEMA, queryBuilder, number, 'test', {}, permissions);
 
 		expect(db['andWhere']).toBeCalledTimes(1);
-		expect(db['orWhere']).toBeCalledTimes(2);
-		expect(db['orWhereRaw']).toBeCalledTimes(1);
-		expect(db['orWhereRaw']).toBeCalledWith('LOWER(??) LIKE ?', ['test.text', `%${number.toLowerCase()}%`]);
+		expect(queryBuilder['orWhere']).toBeCalledTimes(3);
+		expect(queryBuilder['orWhereRaw']).toBeCalledTimes(0);
+		expect(queryBuilder['and']['whereRaw']).toBeCalledTimes(1);
+
+		expect(queryBuilder['and']['whereRaw']).toBeCalledWith('LOWER(??) LIKE ?', [
+			'test.text',
+			`%${number.toLowerCase()}%`,
+		]);
 	});
 
 	test('Query is falsy if no other clause is added', async () => {
 		const db = mockDatabase();
+		const queryBuilder = db as any;
 
 		const schemaWithStringFieldRemoved = JSON.parse(JSON.stringify(FAKE_SCHEMA));
 
 		delete schemaWithStringFieldRemoved.collections.test.fields.text;
 
-		db['andWhere'].mockImplementation((callback: () => void) => {
-			// detonate the andWhere function
-			callback.call(db);
-			return db;
+		db['andWhere'].mockImplementation((callback: (queryBuilder: Knex.QueryBuilder) => void) => {
+			callback(queryBuilder);
+			return queryBuilder;
+		});
+
+		queryBuilder['orWhere'].mockImplementation((callback: (queryBuilder: Knex.QueryBuilder) => void) => {
+			callback(queryBuilder);
+			return queryBuilder;
+		});
+
+		applySearch(db as any, schemaWithStringFieldRemoved, queryBuilder, 'searchstring', 'test', {}, permissions);
+
+		expect(db['andWhere']).toBeCalledTimes(1);
+		expect(queryBuilder['orWhere']).toBeCalledTimes(0);
+		expect(queryBuilder['orWhereRaw']).toBeCalledTimes(1);
+		expect(queryBuilder['orWhereRaw']).toBeCalledWith('1 = 0');
+	});
+
+	test('Remove forbidden field "text" from search', () => {
+		const db = mockDatabase();
+		const queryBuilder = db as any;
+
+		db['andWhere'].mockImplementation((callback: (queryBuilder: Knex.QueryBuilder) => void) => {
+			callback(queryBuilder);
+			return queryBuilder;
+		});
+
+		queryBuilder['orWhere'].mockImplementation((callback: (queryBuilder: Knex.QueryBuilder) => void) => {
+			callback(queryBuilder);
+			return queryBuilder;
+		});
+
+		applySearch(db as any, FAKE_SCHEMA, queryBuilder, 'directus', 'test', {}, [
+			{
+				collection: 'test',
+				action: 'read',
+				fields: ['text1'],
+				permissions: {
+					text: {},
+				},
+			} as unknown as Permission,
+		]);
+
+		expect(db['andWhere']).toBeCalledTimes(1);
+		expect(queryBuilder['orWhere']).toBeCalledTimes(1);
+		expect(queryBuilder['orWhereRaw']).toBeCalledTimes(0);
+		expect(queryBuilder['and']['whereRaw']).toBeCalledTimes(1);
+		expect(queryBuilder['and']['whereRaw']).toBeCalledWith('LOWER(??) LIKE ?', ['test.text1', `%directus%`]);
+	});
+
+	test('Add all fields for * field rule and no item rule', () => {
+		const db = mockDatabase();
+		const queryBuilder = db as any;
+
+		db['andWhere'].mockImplementation((callback: (queryBuilder: Knex.QueryBuilder) => void) => {
+			callback(queryBuilder);
+			return queryBuilder;
+		});
+
+		queryBuilder['orWhere'].mockImplementation((callback: (queryBuilder: Knex.QueryBuilder) => void) => {
+			callback(queryBuilder);
+			return queryBuilder;
+		});
+
+		applySearch(db as any, FAKE_SCHEMA, queryBuilder, `1`, 'test', {}, [
+			{
+				collection: 'test',
+				action: 'read',
+				fields: ['*'],
+				permissions: null,
+			} as unknown as Permission,
+		]);
+
+		expect(db['andWhere']).toBeCalledTimes(1);
+		expect(queryBuilder['orWhere']).toBeCalledTimes(0);
+		expect(queryBuilder['orWhereRaw']).toBeCalledTimes(0);
+		expect(queryBuilder['or']['whereRaw']).toBeCalledTimes(2);
+		expect(queryBuilder['or']['where']).toBeCalledTimes(2);
+	});
+
+	test('Add all fields for * field rule and item rules', () => {
+		const db = mockDatabase();
+		const queryBuilder = db as any;
+
+		db['andWhere'].mockImplementation((callback: (queryBuilder: Knex.QueryBuilder) => void) => {
+			callback(queryBuilder);
+			return queryBuilder;
+		});
+
+		queryBuilder['orWhere'].mockImplementation((callback: (queryBuilder: Knex.QueryBuilder) => void) => {
+			callback(queryBuilder);
+			return queryBuilder;
+		});
+
+		applySearch(db as any, FAKE_SCHEMA, queryBuilder, '1', 'test', {}, [
+			{
+				collection: 'test',
+				action: 'read',
+				fields: ['*'],
+				permissions: {
+					text: {},
+				},
+			} as unknown as Permission,
+		]);
+
+		expect(db['andWhere']).toBeCalledTimes(1);
+		expect(queryBuilder['orWhere']).toBeCalledTimes(0);
+		expect(queryBuilder['orWhereRaw']).toBeCalledTimes(0);
+		expect(queryBuilder['or']['whereRaw']).toBeCalledTimes(2);
+		expect(queryBuilder['or']['where']).toBeCalledTimes(2);
+	});
+
+	test('Add all fields when at least one policy contains a * field rule', () => {
+		const db = mockDatabase();
+		const queryBuilder = db as any;
+
+		db['andWhere'].mockImplementation((callback: (queryBuilder: Knex.QueryBuilder) => void) => {
+			callback(queryBuilder);
+			return queryBuilder;
+		});
+
+		queryBuilder['orWhere'].mockImplementation((callback: (queryBuilder: Knex.QueryBuilder) => void) => {
+			callback(queryBuilder);
+			return queryBuilder;
+		});
+
+		applySearch(db as any, FAKE_SCHEMA, queryBuilder, '1', 'test', {}, [
+			{
+				collection: 'test',
+				action: 'read',
+				fields: ['text'],
+				permissions: {
+					text: {},
+				},
+			} as unknown as Permission,
+			{
+				collection: 'test',
+				action: 'read',
+				fields: ['*'],
+				permissions: {
+					text: {},
+				},
+			} as unknown as Permission,
+		]);
+
+		expect(db['andWhere']).toBeCalledTimes(1);
+		expect(queryBuilder['orWhere']).toBeCalledTimes(1);
+		expect(queryBuilder['orWhereRaw']).toBeCalledTimes(0);
+		expect(queryBuilder['or']['whereRaw']).toBeCalledTimes(2);
+		expect(queryBuilder['or']['where']).toBeCalledTimes(3);
+	});
+
+	test('Add field(s) without permissions for admin', () => {
+		const db = mockDatabase();
+		const queryBuilder = db as any;
+
+		db['andWhere'].mockImplementation((callback: (queryBuilder: Knex.QueryBuilder) => void) => {
+			callback(queryBuilder);
+			return queryBuilder;
+		});
+
+		queryBuilder['orWhere'].mockImplementation((callback: (queryBuilder: Knex.QueryBuilder) => void) => {
+			callback(queryBuilder);
+			return queryBuilder;
 		});
 
-		applySearch(db as any, schemaWithStringFieldRemoved, db as any, 'searchstring', 'test');
+		applySearch(db as any, FAKE_SCHEMA, queryBuilder, '1', 'test', {}, []);
 
 		expect(db['andWhere']).toBeCalledTimes(1);
-		expect(db['orWhere']).toBeCalledTimes(0);
-		expect(db['orWhereRaw']).toBeCalledTimes(1);
-		expect(db['orWhereRaw']).toBeCalledWith('1 = 0');
+		expect(queryBuilder['orWhere']).toBeCalledTimes(0);
+		expect(queryBuilder['orWhereRaw']).toBeCalledTimes(0);
+		expect(queryBuilder['or']['whereRaw']).toBeCalledTimes(2);
 	});
 });
 
@@ -198,7 +411,7 @@ describe('applyFilter', () => {
 						_and: [{ [field]: { [`_${filterOperator}`]: filterValue } }],
 					};
 
-					const { query } = applyFilter(db, FAKE_SCHEMA, queryBuilder, rootFilter, collection, {}, []);
+					const { query } = applyFilter(db, FAKE_SCHEMA, queryBuilder, rootFilter, collection, {}, [], []);
 
 					const tracker = createTracker(db);
 					tracker.on.select('*').response([]);
@@ -264,7 +477,7 @@ describe('applyFilter', () => {
 			},
 		};
 
-		const { query } = applyFilter(db, BIGINT_FAKE_SCHEMA, queryBuilder, rootFilter, collection, {}, []);
+		const { query } = applyFilter(db, BIGINT_FAKE_SCHEMA, queryBuilder, rootFilter, collection, {}, [], []);
 
 		const tracker = createTracker(db);
 		tracker.on.select('*').response([]);
@@ -324,7 +537,7 @@ describe('applyFilter', () => {
 			},
 		};
 
-		const { query } = applyFilter(db, sampleSchema, queryBuilder, rootFilter, collection, {});
+		const { query } = applyFilter(db, sampleSchema, queryBuilder, rootFilter, collection, {}, [], []);
 
 		const tracker = createTracker(db);
 		tracker.on.select('*').response([]);

@@ -72,10 +72,6 @@ export default function applyQuery(
 		}
 	}
 
-	if (query.search) {
-		applySearch(knex, schema, dbQuery, query.search, collection);
-	}
-
 	// `cases` are the permissions cases that are required for the current data set. We're
 	// dynamically adding those into the filters that the user provided to enforce the permission
 	// rules. You should be able to read an item if one or more of the cases matches. The actual case
@@ -137,6 +133,10 @@ export default function applyQuery(
 		dbQuery.groupBy(columns);
 	}
 
+	if (query.search) {
+		applySearch(knex, schema, dbQuery, query.search, collection, aliasMap, permissions);
+	}
+
 	if (query.aggregate) {
 		applyAggregate(schema, dbQuery, query.aggregate, collection, hasJoins);
 	}
@@ -923,38 +923,94 @@ export function applySearch(
 	dbQuery: Knex.QueryBuilder,
 	searchQuery: string,
 	collection: string,
+	aliasMap: AliasMap,
+	permissions: Permission[],
 ) {
 	const { number: numberHelper } = getHelpers(knex);
-	const fields = Object.entries(schema.collections[collection]!.fields);
 
-	dbQuery.andWhere(function () {
+	const allowedFields = new Set(permissions.filter((p) => p.collection === collection).flatMap((p) => p.fields ?? []));
+
+	let fields = Object.entries(schema.collections[collection]!.fields);
+
+	const { cases, caseMap } = getCases(collection, permissions, []);
+
+	// Add field restrictions if non-admin and "everything" is not allowed
+	if (cases.length !== 0 && !allowedFields.has('*')) {
+		fields = fields.filter((field) => allowedFields.has(field[0]));
+	}
+
+	dbQuery.andWhere(function (queryBuilder) {
 		let needsFallbackCondition = true;
 
 		fields.forEach(([name, field]) => {
-			if (['text', 'string'].includes(field.type)) {
-				this.orWhereRaw(`LOWER(??) LIKE ?`, [`${collection}.${name}`, `%${searchQuery.toLowerCase()}%`]);
-				needsFallbackCondition = false;
-			} else if (isNumericField(field)) {
-				const number = parseNumericString(searchQuery);
+			const whenCases = (caseMap[name] ?? []).map((caseIndex) => cases[caseIndex]!);
 
-				if (number === null) {
-					return; // unable to parse
-				}
+			const fieldType = getFieldType(field);
 
-				if (numberHelper.isNumberValid(number, field)) {
-					numberHelper.addSearchCondition(this, collection, name, number);
-					needsFallbackCondition = false;
-				}
-			} else if (field.type === 'uuid' && isValidUuid(searchQuery)) {
-				this.orWhere({ [`${collection}.${name}`]: searchQuery });
+			if (fieldType !== null) {
 				needsFallbackCondition = false;
+			} else {
+				return;
+			}
+
+			if (cases.length !== 0 && whenCases?.length !== 0) {
+				queryBuilder.orWhere((subQuery) => {
+					addSearchCondition(subQuery, name, fieldType, 'and');
+
+					applyFilter(knex, schema, subQuery, { _or: whenCases }, collection, aliasMap, cases, permissions);
+				});
+			} else {
+				addSearchCondition(queryBuilder, name, fieldType, 'or');
 			}
 		});
 
 		if (needsFallbackCondition) {
-			this.orWhereRaw('1 = 0');
+			queryBuilder.orWhereRaw('1 = 0');
 		}
 	});
+
+	function addSearchCondition(
+		queryBuilder: Knex.QueryBuilder,
+		name: string,
+		fieldType: 'string' | 'numeric' | 'uuid',
+		logical: 'and' | 'or',
+	) {
+		if (fieldType === null) {
+			return;
+		}
+
+		if (fieldType === 'string') {
+			queryBuilder[logical].whereRaw(`LOWER(??) LIKE ?`, [`${collection}.${name}`, `%${searchQuery.toLowerCase()}%`]);
+		} else if (fieldType === 'numeric') {
+			numberHelper.addSearchCondition(queryBuilder, collection, name, parseNumericString(searchQuery)!, logical);
+		} else if (fieldType === 'uuid') {
+			queryBuilder[logical].where({ [`${collection}.${name}`]: searchQuery });
+		}
+	}
+
+	function getFieldType(field: FieldOverview): null | 'string' | 'numeric' | 'uuid' {
+		if (['text', 'string'].includes(field.type)) {
+			return 'string';
+		}
+
+		if (isNumericField(field)) {
+			const number = parseNumericString(searchQuery);
+
+			if (number === null) {
+				return null;
+			}
+
+			if (numberHelper.isNumberValid(number, field)) {
+				return 'numeric';
+			}
+		}
+
+		if (field.type === 'uuid' && isValidUuid(searchQuery)) {
+			return 'uuid';
+		}
+
+		return null;
+	}
 }
 
 export function applyAggregate(