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:
Category:Connector / ODBC Severity:S3 (Non-critical)
Version: 8.00.20 OS:Windows
Assigned to: CPU Architecture:x86 (64_x86)

[13 Mar 2020 18:00] Marcus Hampel
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.
[7 May 2020 15:59] MySQL Verification Team
Could you please try version 8.0.20. Thanks.
[8 May 2020 7:55] Marcus Hampel
Same effect with 8.00.20
[13 Jul 2020 11:06] MySQL Verification Team
Please try version 8.0.21. Thanks.
[13 Jul 2020 11:16] Marcus Hampel
[15 Jul 2020 20:55] MySQL Verification Team
Thank you for the feedback. Please print here running your test case. Thanks.
[16 Jul 2020 6:41] Marcus Hampel
Where should I print something? The test case can be downloaded via the link in the description.
[1 Sep 2020 18:34] MySQL Verification Team
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.
[2 Sep 2020 8:08] Marcus Hampel
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.
[10 Nov 2020 8:06] Bogdan Degtyariov
I was able to repeat the problem. The bug is verified
[16 Nov 2020 6:27] Bogdan Degtyariov
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.
[16 Nov 2020 9:35] Bogdan Degtyariov
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.
[18 Nov 2020 3:55] Bogdan Degtyariov
Posted by developer:
The patch is now in the source tree.
[31 Aug 2021 20:53] Philip Olson
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.