Validate EF.Functions.Collate input

[AndriySvyryd]

Bug description

The second EF.Functions.Collate parameter is currently directly embedded in the SQL. However, this could cause unpredictable results if it is created form user input.

Your code

var results = context.Users.Where(u => EF.Functions.Collate(u.Name, collationMode) == "test").ToList();

EF Core version

9.0.0

[roji]

Quick cross-database investigations: In PostgreSQL, Sqlite and MySQL collations are identifiers just like any others and can be quoted. SQL Server is the odd one out, where collations cannot be quoted.

We'll switch to quoting by default, and providers for databases which don't support this can override and introduce checks to narrow down the set of accepted characters.

Database comparison

SQL Server

docs

Collations are not regular identifiers and cannot be quoted:

SELECT CASE WHEN 'foo' = 'Foo' COLLATE [SQL_Latin1_General_CP1_CS_AS] THEN 1 ELSE 0 END; -- syntax error

PostgreSQL

Collations can be quoted like other identifiers (this is already being done).

SQLite

Collations can be quoted like other identifiers.

SELECT 'foo' = 'Foo' COLLATE "BINARY"; -- 0
SELECT select 'foo' = 'Foo' COLLATE "NOCASE"; -- 1

MySQL

Collations can be quoted like other identifiers.

SELECT 'foo' COLLATE `utf8mb4_0900_as_cs` = 'Foo'; -- 0
SELECT 'foo' COLLATE `utf8mb4_0900_ai_ci` = 'Foo'; -- 1