Bug #103160 MySQL.Data.EntityFramework does not handle LIKE (Edm.IndexOf) cases
Submitted: 31 Mar 4:36 Modified: 6 Apr 4:08
Reporter: ncoda harmless Email Updates:
Status: Analyzing Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:8.0.23 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any

[31 Mar 4:36] ncoda harmless
Description:
There is a regression MySQL.Data.EntityFramework from v6.9 (what we have been stuck using because of this) where it produces invalid SQL.

This occurs when doing a LINQ query partially matching items from a string array.

e.g. where var matchText = new string[] { "a'b" };
context.Vehicles.Where(v => matchText.Any(t => v.Name.Contains(t)));

which produces SQL which contains
 WHERE `Extent1`.`Name` LIKE %a'c%

This has 2 issues, it is not surrounded by quotes, and also the quote in the data is not escaped.

It should have produced something like
 WHERE `Extent1`.`Name` LIKE '%a\'c%'

For some reason using EF 6.1.1 with the older connector did not visit this code path and therefore didn't have the issue.

How to repeat:
Add this test to the CodeFirstTests.cs

   [Test]
      public void TestListMatchingLike()
      {

 using (VehicleDbContext2 context = new VehicleDbContext2())
         {
            context.Database.Delete();
            context.Database.Initialize(true);

            context.Vehicles.Add(new Car2 { Id = 1, Name = "Mustang", Year = 2012, CarProperty = "Car" });
            context.Vehicles.Add(new Bike2 { Id = 101, Name = "Mountain", Year = 2011, BikeProperty = "Bike" });
            context.SaveChanges();

            // this one succeeds
            string[] matchText = new string[] { "must", "tan" };
            var list = context.Vehicles.Where(v => matchText.Any(t => v.Name.Contains(t)));
            Assert.AreEqual(1, list.Count());

            // this one fails
            matchText = new string[] { "mus't", "tan" };
            list = context.Vehicles.Where(v => matchText.Any(t => v.Name.Contains(t)));
            Assert.AreEqual(1, list.Count()); //EXCEPTION HERE

            // this one also fails
            matchText = new string[] { "tan" }; 
            list = context.Vehicles.Where(v => matchText.Any(t => v.Name.Contains(t)));
            Assert.AreEqual(1, list.Count()); //EXCEPTION HERE

            // this one also matches incorrectly
            matchText = new string[] { "%" }; 
            list = context.Vehicles.Where(v => matchText.Any(t => v.Name.Contains(t)));
            Assert.AreEqual(0, list.Count()); //SHOULD MATCH THE LITERAL %

         }
}

Suggested fix:
In SqlGenerator.cs function TryPromoteToLike has all the issues.

It appears that the line approx 716 should be changed from
like.Pattern = new LiteralFragment(string.Format("%{0}%", c.Value.ToString()));

to something like 
like.Pattern = new LiteralFragment(EscapeLikeLiteralValue(string.Format("'%{0}%'", c.Value.ToString()));

where EscapeLikeLiteralValue handles all the 'like' escapes
string EscapeLikeLiteralValue(string s) => s.Replace(@"\", @"\\\\")
               .Replace("'", @"\'")
               .Replace("’", @"\'")
               .Replace(((char)0).ToString(), @"\0")
               .Replace("%", @"\%")
               .Replace("_", @"\_");

Also each of the other places that are adding a new LiteralFragment() in this TryPromoteToLike should also use the EscapeLikeLiteralValue() so each of these cases doesn't crash as well.
[31 Mar 4:45] ncoda harmless
Sorry fix should be
like.Pattern = new LiteralFragment(string.Format("'%{0}%'", EscapeLikeLiteralValue(c.Value.ToString()));
[1 Apr 12:57] MySQL Verification Team
Hello,

Thank you for the bug report.
Could you please provide complete repeatable test case (sample project, etc. - please make it as private if you prefer) to confirm this issue at our end?

Regards,
Ashwini Patil
[5 Apr 22:57] ncoda harmless
The above test case I had added to a fork of the MySQL Connector/NET from
https://github.com/mysql/mysql-connector-net/tree/8.0/EntityFramework/tests/MySql.EntityFr...

Here is my fork with the fix and additional tests
https://github.com/ncodaharmless/mysql-connector-net

Is this what you are after? I could submit a pull request if that is better?