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

[31 Mar 2021 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 2021 4:45] ncoda harmless
Sorry fix should be
like.Pattern = new LiteralFragment(string.Format("'%{0}%'", EscapeLikeLiteralValue(c.Value.ToString()));
[1 Apr 2021 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 2021 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?
[21 Sep 2021 13:09] MySQL Verification Team
Hello,

Thank you for the details.
May I kindly request you to try latest 8.0.26 Connector/Net and report us back if you are still seeing this issue at your end.

Regards,
Ashwini Patil
[20 Oct 2021 0:33] ncoda harmless
Hi,

versions 8.0.26 and 8.0.27 both appear to have fixed PART of the issue, so definitely progressing towards getting resolved.

In the tests I provided above, the first and third failures are resolved but the second isn't.

NOW PASSES!!
            matchText = new string[] { "mus't", "tan" };
            list = context.Vehicles.Where(v => matchText.Any(t => v.Name.Contains(t)));
            Assert.AreEqual(1, list.Count());

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

So the SqlGenerator is still not putting the closing quote on this case:
MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%tan%))) AS `GroupBy1`' at line 6

NOW PASSES!!
            matchText = new string[] { "%" }; 
            list = context.Vehicles.Where(v => matchText.Any(t => v.Name.Contains(t)));
            Assert.AreEqual(0, list.Count()); //SHOULD MATCH THE LITERAL %
[10 May 6:43] MySQL Verification Team
Thank you for the feedback. Please try version 8.0.29. Thanks.
[27 May 4:50] ncoda harmless
Still the same issue. Version 8.0.29 behaves the same as 8.0.26 and 8.0.27 regarding this.