MySQL Bugs: #100314: TableInsertStatement string special carac values object are uncorrectly managed

Bug #100314	TableInsertStatement string special carac values object are uncorrectly managed
Submitted:	24 Jul 2020 9:35	Modified:	7 Mar 2022 21:01
Reporter:	Bruno CHATAIGNER	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	8.0.21 and before	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	async, MySql.Data.EntityFrameworkCore, TableInsertStatement, values

Description:
When using MySqlx MySql.Data.EntityFrameworkCore, for async Task, we use : TableInsertStatement insertionRequest = _tblRepliTbAnoncehead.Insert(fieldsToInsert).Values(valuesToInsert);

Most of the time it works fine. The problem occurs only when One value object is a string object that contains ones of the following special caracter : 
- / \ % & =

It seems that the insert request construction is not correct. Perhaps an optimizer problem ?

In MySql general log, no trace for this request. The error seems to be only triggered in MySqlX Connector and not sent to MySQL server (Optimizer certainly controls that each request field exists in database Schema.GetTable

In the example below, AN_LOT_KEY is not a column, but a string value
In our case fieldsToInsert contains a string tab of 14 strings and none of them contains 'AN_LOT_KEY' value (controled by VS inspector). valuesToInsert contains one values object with 14 objects. One of those object is a string object with this value '13781&AN_LOT_KEY=41120'

In this case the sring value contains 13781&AN_LOT_KEY=41120. & caract seems to be missunterpreted by sql optimizer and it corrupt the request.

As those values are only indicatives values, we made a workaround with :
PublicItemId = PublicItemId.Replace("&", "_");
and 
PublicItemId = PublicItemId.Replace("=", "_");
It works with '13781_AN_LOT_KEY_41120' string value

Of course each string is prepared with :
string cleanMySqlxRequestString = MySql.Data.MySqlClient.MySqlHelper.EscapeString(requestString_);

Please find the exception details below :
2020-07-24 11:03:49.357 +02:00 [Error] An unhandled exception has occurred while executing the request.
MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'AN_LOT_KEY' in 'field list'
   at MySqlX.Protocol.XProtocol.DecodeAndThrowError(CommunicationPacket p)
   at MySqlX.Protocol.XProtocol.HasData(BaseResult rs)
   at MySqlX.XDevAPI.Common.BaseResult..ctor(InternalSession session)
   at MySqlX.XDevAPI.Common.Result..ctor(InternalSession session)
   at MySqlX.Sessions.XInternalSession.InsertRows(TableInsertStatement statement)
   at MySqlX.XDevAPI.Relational.TableInsertStatement.Execute()
   at MySqlX.XDevAPI.Common.BaseStatement`1.<ExecuteAsync>b__9_0()
   at System.Threading.Tasks.Task`1.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
   at MySqlX.XDevAPI.Common.BaseStatement`1.ExecuteAsync()
   at WebGrabberApiGateway.Services.GrabbingServicesSubTasks.InformationProperties.SetNewInformationFound(DeduplicationResponse deduplicationResponse, Source source, DeduplicationIndex[] deduplicationIndexes, Boolean isAnnexeDetected) in C:\Projets .Net\WebGrabberApiGateway\WebGrabberApiGateway\Services\GrabbingServicesSubTasks\InformationProperties.cs:line 226
   at WebGrabberApiGateway.Services.GrabbingServicesSubTasks.InformationProperties.SetNewInformationProperties(DeduplicationResponse deduplicationResponse, InitialInformationPropertiesInquiry initialInformationPropertiesRequest) in C:\Projets .Net\WebGrabberApiGateway\WebGrabberApiGateway\Services\GrabbingServicesSubTasks\InformationProperties.cs:line 352
   at WebGrabberApiGateway.Services.GrabbingServices.AskForInitialInformationProperties(InitialInformationPropertiesInquiry initialInformationPropertiesRequest) in C:\Projets .Net\WebGrabberApiGateway\WebGrabberApiGateway\Services\GrabbingServices.cs:line 201
   at WebGrabberApiGateway.Controllers.GrabbingProcessController.AskForInitialInformationProperties(InitialInformationPropertiesInquiry initialInformationPropertiesInquiry) in C:\Projets .Net\WebGrabberApiGateway\WebGrabberApiGateway\Controllers\GrabbingProcessController.cs:line 128
   at lambda_method(Closure , Object )
   at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Builder.RouterMiddleware.Invoke(HttpContext httpContext)
   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

How to repeat:
Each time ones of the following caracter is used in our TableInsertStatement :
- / \ % & and perhaps (not checked) =

Suggested fix:
Check TableInsertStatement insertionRequest = _tblRepliTbAnoncehead.Insert(fieldsToInsert).Values(valuesToInsert) interpretation when special caracters are in string object values.

Thank you for the bug report. Please provide a complete test case SQL script and the C# file test case. Thanks.

Test database request

Attachment: debug.sql (application/octet-stream, text), 2.29 KiB.

Code sample

Attachment: Program.cs (text/plain), 4.07 KiB.

Thank you for the feedback.

Also noticed the same kind of problem with a request containing ( ) caracters.
Not always, but sometimes, those ones are also missunterpreted by MySQLX :

For exemple FUNCTION sites_web.Guyane does not exist error message was genererated when inserting the value 'Guyane (973)' in the sample table in the field LIEU_EXE.
It is the first time it happens, we already inserted values with () caracters in this field, for example '(972) Martinique' is OK !

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.29 release, and here's the proposed changelog entry from the documentation team:

An exception was raised when using a TableInsertStatement object to insert
a string value containing special characters (- / \ % & =).

Thank you for the bug report.