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

[24 Jul 9:35] Bruno CHATAIGNER
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.
[27 Jul 20:44] MySQL Verification Team
Thank you for the bug report. Please provide a complete test case SQL script and the C# file test case. Thanks.
[30 Jul 10:42] Bruno CHATAIGNER
Test database request

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

[30 Jul 10:43] Bruno CHATAIGNER
Code sample

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

[30 Jul 13:45] MySQL Verification Team
Thank you for the feedback.
[11 Aug 13:09] Bruno CHATAIGNER
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 !