MySQL Bugs: #98817: Syntax error while using SQLPutData for large binary data

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:	Windows
Assigned to:		CPU Architecture:	x86

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.

myodbc log file

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

I added the generated log file for error details.

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.

Thank you for the bug report. Please provide the create table statement and the file.c client test case. Thanks.

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".

Test case

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

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

modified test case for 64bits

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

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.

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'

Thank you for the feedback.

Posted by developer:
 
Verified with 8.0.20. The issue still happens.

Posted by developer:
 
The patch has been pushed to the source repository

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.