Bug #98817 Syntax error while using SQLPutData for large binary data
Submitted: 3 Mar 2020 15:53 Modified: 3 Sep 2020 20:47
Reporter: Jean-Christophe Voogden Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:8.0.19 OS:Microsoft Windows
Assigned to: CPU Architecture:x86

[3 Mar 2020 15:53] Jean-Christophe Voogden
Description:
When inserting binary data (BLOBS) using SQLPutData(), we get syntax errors reported.

The driver generates an invalid query (trash characters at the end) :

1583247723:INSERT INTO "VACHMBIN" ("CNOMINTBIN", "CLGCHBIN", "CCHAMPBIN") VALUES (17, 5080, _binary'(\ ..... ')Æ\Æò<tv ì ;
1583247723:Using direct execution;
1583247723:query has been executed;
1583247723: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 '?\??<tv ' at line 1;

This problem does not occur with the previous version (8.0.18).

Server is MySQL Community 8.0.13.

Regards,

How to repeat:
Bind a parameter with SQL_LEN_DATA_AT_EXEC and use SQLPutData to store value after execution.
[3 Mar 2020 15:54] Jean-Christophe Voogden
myodbc log file

Attachment: myodbc.sql (application/octet-stream, text), 54.82 KiB.

[3 Mar 2020 15:55] Jean-Christophe Voogden
I added the generated log file for error details.
[3 Mar 2020 16:41] Jean-Christophe Voogden
When inserting binary data (BLOBS) using SQLPutData(), we get syntax errors reported.

The driver generates an invalid query (trash characters at the end) :

1583247723:INSERT INTO "VACHMBIN" ("CNOMINTBIN", "CLGCHBIN", "CCHAMPBIN") VALUES (17, 5080, _binary'(\ ..... ')Æ\Æò<tv ì ;
1583247723:Using direct execution;
1583247723:query has been executed;
1583247723: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 '?\??<tv ' at line 1;

This problem does not occur with the previous version (8.0.18).

Server is MySQL Community 8.0.13.
[26 Mar 2020 20:43] MySQL Verification Team
Thank you for the bug report. Please provide the create table statement and the file.c client test case. Thanks.
[27 Apr 2020 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[5 May 2020 9:45] Jean-Christophe Voogden
Test case

Attachment: images.c (text/plain), 5.17 KiB.

[5 May 2020 9:48] Jean-Christophe Voogden
Sorry for the delay. The attached test case is self contained and work correctly with odbc driver 8.0.18 and below but fails with odbc driver 8.0.19.

The test program assumes a anonymous local connection to the "test" database, with proper privileges.

The program is build using Microsoft Visual Studio 2017 compiler, in 32bits.

The failure does not occur if the no_ssps=1 option is removed from the DSN
[5 May 2020 9:52] Jean-Christophe Voogden
modified test case for 64bits

Attachment: images.c (text/plain), 5.16 KiB.

[5 May 2020 10:33] MySQL Verification Team
Thank you for the feedback. You wrote:
"The failure does not occur if the no_ssps=1 option is removed from the DSN"
Which is the case of bug below:
https://bugs.mysql.com/bug.php?id=99319
"I have followed the same. However, I have noticed that with NO_SSPS=1 I am getting error while with NO_SSPS=0 this works fine."

Please check for duplicate/related issue. Thanks.
[5 May 2020 11:47] Jean-Christophe Voogden
I don't think they are related. The problem is a corruption of the request leading to a syntax error, not a stored procedure parameter description problem.

As a side note we encountered this stored procedure problem, it is alleviated on our side by rewriting the stored procedure statement..

Aka instead of executing 'call procedure(?, ?)' we do something like 'set @p1=?; set @p2=?; call proc(@p1, @p2); select @p1, @p2'
[5 May 2020 12:27] MySQL Verification Team
Thank you for the feedback.
[20 May 2020 8:22] Bogdan Degtyariov
Posted by developer:
 
Verified with 8.0.20. The issue still happens.
[4 Jun 2020 3:30] Bogdan Degtyariov
Posted by developer:
 
The patch has been pushed to the source repository
[3 Sep 2020 20:47] Philip Olson
Posted by developer:
 
Fixed as of the upcoming MySQL Connector/ODBC 8.0.22 release, and here's the proposed changelog entry from the documentation team:

Inserting binary data (BLOBs) using SQLPutData() would report a syntax
error.

Thank you for the bug report.