MySQL Bugs: #98946: Crash, invaid or inconsistent data by using SQLBindParameter()

Bug #98946	Crash, invaid or inconsistent data by using SQLBindParameter()
Submitted:	13 Mar 2020 18:00	Modified:	31 Aug 2021 20:53
Reporter:	Marcus Hampel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	8.00.20	OS:	Windows
Assigned to:		CPU Architecture:	x86 (64_x86)

Description:
Crash, invalid or inconsistent data by using SQLBindParameter() when binding columns of type double into character buffers (length of SQLColAttribute(SQL_DESC_DISPLAY_SIZE)).

How to repeat:
Test program created: https://www.dropbox.com/s/ma5jap1vsx3a6g0/test_odbc.c?dl=0

Program create table with 7 lines of id and double values.
You can select single or multiple ids (0-6) by parameter (idMin, idMax).

If i use idMin=0 and idMax=6 the result seems correct, but with different result 
by record 0:

select (SQLExecDirect):
Record 1: [0], [0]

select (SQLExecute, without parameters):
Record 1: [0], [0]

select (SQLExecute, with parameters):
Record 1: [0], [0.000000]

If i choose idMin=4 and idMax=4 i got this result:

select (SQLExecDirect):
Record 1: [4], [1.79e308]

select (SQLExecute, without parameters):
Record 1: [4], [1.79e308]

select (SQLExecute, with parameters):
Record 1: [4], [17899999999999999637689(... random data ...)]

With idMin=5 and idMax=5 this:

select (SQLExecDirect):
Record 1: [5], [-2.23e-308]

select (SQLExecute, without parameters):
Record 1: [5], [-2.23e-308]

select (SQLExecute, with parameters):
Record 1: [5], [-0.000000]

With idMin=2 and idMax=2 this:
select (SQLExecDirect):
Record 1: [2], [123456789012345]

select (SQLExecute, without parameters):
Record 1: [2], [123456789012345]

select (SQLExecute, with parameters):
Record 1: [2], [123456789012345.000000]

With Driver 5.3.14, program crashed with idMin=4 and idMax=4 and other constellations.

Could you please try version 8.0.20. Thanks.

Same effect with 8.00.20

Please try version 8.0.21. Thanks.
https://dev.mysql.com/downloads/connector/odbc/

same

Thank you for the feedback. Please print here running your test case. Thanks.

Where should I print something? The test case can be downloaded via the link in the description.

Sorry for the delay. I compiled your test case, created and tested an System DSN and when executing it I got?

d:\code\bug98946\test_odbc\x64\Release>test_odbc DSN root MetSol80@# 4 4
SQLConnect failed: -1

Then wanted to see the output of your application. Thanks.

I have not implemented a complete error analysis for the test program. Sorry for that. If the connection failed, most times the DSN (Data Source Name), user or password is wrong.

You named your System (or User) DSN "DSN"? You have to use the name you set in the ODBC Datasorce Administrator.

I was able to repeat the problem. The bug is verified

Posted by developer:
 
ODBC Driver can obtain results from the server in two ways:

 1. Normal statements:
    Using MYSQL_ROW where the data (except binary) is represented as
    character strings. This is most certainly so for Double and
    Float types.
    The value "1.79e308" is received by ODBC Driver as a character
    string. The client program requests data as SQL_C_CHAR, so the
    value is passed to it without any conversion.
    

 2. Server side prepared statements:
    Using MYSQL_BIND structure where the data is directly obtained
    as a required MYSQL_XXXXX type. For Double and Float the 
    structure will contain the native binary representation of
    the value. Then it is converted to the string because the
    client application requested so. Since both Double and Float
    are not exact types the string conversion might produce a
    result like "1.789999999999999999999999998888888888887e308"

This breaks the integrity of the results. Therefore, the Double and Float binds in MYSQL_BIND should be replaced by MYSQL_STRING.
If needed the string data will be converted to the native type, but it will be done in a uniform way, so the results for the Server Side Prepared Statements and normal statements would be identical in both string and native representation.

Posted by developer:

The previous message explained why the data returned by SQLExecDirect() and SQLPrepare()/SQLExecute() was inconsistent.
However, the crash happened for a different reason.
The call of SQLColAttribute(hstmt, ..., SQL_DESC_DISPLAY_SIZE, ...) does not always give the accurate number before the actual data is read.
The buffer bound by SQLBindCol() is not long enough, the last parameter valBufRead given to it is not always indicating the number of "written" bytes, but the number of "available" bytes.
In this case the data was fetched in SQLFetch(), but not all available bytes went into the destination buffer. However, the call to fwrite() used the number of "available" bytes. That is why the last part always contained random symbols. So, the driver did not write past the buffer boundaries, but the program read past the buffer, which could cause read violation and crash.

The driver should have not expanded the Double value longer than ODBC specification of this type, which is 24, but the client side should not have ignored the number of available bytes and not tried to access past the buffer boundaries.

A good way of checking if the data was truncated (as in the present case) would be to bind the status through SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR, ...), which would report the data truncation. Then the correct course would be to allocate enough memory (as returned through valBufRead) and get the entire value using SQLGetData() function.

Nevertheless, the behavior displayed by the ODBC Driver needs to be fixed as well.

Posted by developer:
 
The patch is now in the source tree.

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

Double and Float values could differ depending if the query was standard
or utilized server side prepared statements. Standard used MSYSQL_ROW for
non-binary data to represent them as character strings, whereas server
side prepared statements used the MYSQL_BIND structure and the specific
MySQL type which could lead towards inconsistent results. Now this is
performed in a uniform way to yield the same results.

Thank you for the bug report, and sorry for the abnormally long delay in posting this release note.