MySQL Bugs: #116559: ODBC connector uses deprecated mysql_real_escape

Bug #116559	ODBC connector uses deprecated mysql_real_escape_string function
Submitted:	5 Nov 2024 13:00	Modified:	4 Mar 21:35
Reporter:	Tom Hughes	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	9.1.0	OS:	Any
Assigned to:	Rafal Somla	CPU Architecture:	Any

Description:
As previously explained in https://bugs.mysql.com/bug.php?id=104068 the ODBC connector has 20 or so uses of the deprecated mysql_real_escape_string function which should be replaced by mysql_real_escape_string_quote.

Without this all calls to the function will fail when NO_BACKSLASH_ESCAPES mode has been enabled and that failure won't even be detected as none of the callers actually checks the return value.

My previous ticket was closed because the specific example I gave had stopped failing but the root cause remains and has now resurfaced with newer drives.

How to repeat:
Set NO_BACKSLASH_ESCAPES and then try executing SQLSpecialColumns with SQL_BEST_ROWID throw a bad_length exception in ODBC_CATALOG::add_param because it will have failed to quote the string and tried to append -1 bytes.

This is is just one code path - there are many others which will also hit this function and fail.

Suggested fix:
Replace mysql_real_escape_string calls with mysql_real_escape_string_quote with an appropriate quote character added as the final argument - it will only take you about ten minutes (I've done it locallly so I know) and will fix this problem properly for all possible code paths.

HI Mr. Hughes,

Can you send us a full test case ........

Also, let us know if 8.0, 8.4 and 9.0 are affected or not .........

Test case

Attachment: odbc116559.c (text/x-csrc), 1.17 KiB.

I've attached a test case - compilation instructions are in a comment as are details of creating a test environment. It should fail with:

% ./odbc116559 
terminate called after throwing an instance of 'std::length_error'
  what():  basic_string::append
[1]    511930 IOT instruction (core dumped)  ./odbc116559

Note that this only tests the one code path but all calls to that routine need fixing!

Dear Mr. Hughes,

Thank you for the detailed description and the test case for this issue.
The bug is confirmed and the status is set to Verified.

The developers team is going to work on fixing it in the nearest time.

Posted by developer:
 
All uses of deprecated mysql_real_escape_string() function have been replaced by the internal myodbc_escape_string() implementation, which works similar to mysql_real_escape_string_quote(), but also is able to handle escaping of the patterns ('_' and '%') for the use in LIKE expressions.

Posted by developer:
 
Added the following entry to the C/ODBC 9.3.0 changelog: 

"All instances of the deprecated function mysql_real_escape_string() in the source code have been replaced by an internal implementation of myodbc_escape_string(), which works similarly and can also handle the escaping of wildcard characters ('_' and '%') in LIKE expressions."