| Bug #103160 | MySQL.Data.EntityFramework does not handle LIKE (Edm.IndexOf) cases | ||
|---|---|---|---|
| Submitted: | 31 Mar 2021 4:36 | Modified: | 15 Nov 2022 19:16 |
| Reporter: | ncoda harmless | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / NET | Severity: | S2 (Serious) |
| Version: | 8.0.29 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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 2022 6:43]
MySQL Verification Team
Thank you for the feedback. Please try version 8.0.29. Thanks.
[27 May 2022 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.
[16 Aug 2022 9:15]
MySQL Verification Team
Hello, Thank you for the feedback. Verified as described. Regards, Ashwini Patil
[15 Nov 2022 17:20]
Daniel Valdez
Posted by developer: The bug was caused because the function that created the SQL query was not escaping the values in a proper way. The fix was to add a method that covers all cases when the string needs to be escaped and apply it before building the SQL query.
[15 Nov 2022 19:16]
Christine Cole
Posted by developer: Fixed as of the upcoming MySQL Connector/NET 8.0.32 release, and here's the proposed changelog entry from the documentation team: In some cases, the function that creates SQL queries did not specify an escape character properly. This fix adds a new method to handle the cases in which a string could require an escape character and applies it before building the SQL query. Thank you for the bug report.
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.