MySQL Bugs: #104068: ODBC connector uses deprecated mysql_real_escape

Bug #104068	ODBC connector uses deprecated mysql_real_escape_string function
Submitted:	21 Jun 2021 9:01	Modified:	11 Mar 2022 13:19
Reporter:	Thomas Hughes	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	8.0.23	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The ODBC connector has 20 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.

How to repeat:
Set NO_BACKSLASH_ESCAPES and then try executing any statement with parameters using the ODBC connector and it will throw errors because insert_param() will fail to insert the parameter value properly.

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.

Hello Thomas Hughes,

Thank you for the bug report.
Could you please provide repeatable test case (exact steps, sample project, etc. - please make it as private if you prefer) to confirm this issue at our end?

Regards,
Ashwini Patil

Test case

Attachment: odbc104068.c (text/x-csrc), 1.98 KiB.

I've added a test case - a simple CREATE TABLE and compile command are in the comments at the top.

As I said this just exercises one path that leads to such a call but there are others that I have run into in the past though I forget the details.

Also I discovered while writing this test case that to trigger this one it's important to SQL_CLOSE the statement between the prepare and execute as that causes the server side prepared statement to be discarded and the execute to be done with local parameter substitution.

Our reason for doing that was to discard any remaining unfetched rows from a previous execution so I now need to see if there's a better way we should be doing that...

Looks like I managed to lose the create table - it is just:

CREATE TABLE TimeInterval (dtEnd datetime)

Hello Thomas Hughes,

Thank you for the requested details.
May I kindly request you to try latest 8.0.28 connector/ODBC and report us back if you are still seeing this issue along with the error?. Thanks.

Regards,
Ashwini Patil

I can't reproduce it with 8.0.28 using that test case no.

That said nothing has actually changed in respect of my original analysis so I suspect what is happening is that the bound parameter case is no longer reaching the call to mysql_real_escape_string_quote in insert_param - presumably something has changed elsewhere that causes it to bind parameters in a different way for that example.

Hello Thomas Hughes,

Thank you for confirming that issue is no longer reproducible on Connector / ODBC 8.0.28.
I'm closing this report for now but if you encounter the issue again then please open the report with the details once again.
In the meantime I would check internally with Developers to confirm if any related bug fix handled this issue and if any then keep you posted here.

Thank you for your interest in MySQL!

Regards,
Ashwini Patil