Bug #90226 SQLTables fails with "statement invalid" when a table pattern is used
Submitted: 27 Mar 2018 8:45 Modified: 27 Apr 2018 13:44
Reporter: Thomas Hughes Email Updates:
Status: No Feedback Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:5.3.10 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[27 Mar 2018 8:45] Thomas Hughes
Description:
If the SQLTables call is used with a pattern for the table name then it will fail and an invalid statement error will be reported. The reason is that the SQL being executed is:

SELECT TABLE_NAME, TABLE_COMMENT, TABLE_TYPE, TABLE_SCHEMA FROM ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND ( TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW' ) ) TABLES WHERE TABLE_NAME LIKE '

when it should be this:

SELECT TABLE_NAME, TABLE_COMMENT, TABLE_TYPE, TABLE_SCHEMA FROM ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND ( TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW' ) ) TABLES WHERE TABLE_NAME LIKE 'ORDERS__'

How to repeat:
Call SQLTables with a pattern for the table name.

Suggested fix:
In table_status_i_s in driver/catalog.c use mysql_real_escape_string_quote to quote the pattern instead of the deprecated mysql_real_escape_string which is erroring with CR_INSECURE_API_ERR because the server has NO_BACKSLASH_ESCAPES enabled.
[27 Mar 2018 9:02] Thomas Hughes
Note that there are a bunch of other uses of that function in driver/catalog.c and driver/catalog_no_i_s.c that will probably affect other calls, and SQLTables when patterns are used for other things. Also one in driver/cursor.c all of which should probably be fixed.
[27 Mar 2018 13:44] Chiranjeevi Battula
Hello  Thomas,

Thank you for the bug report.
Could you please provide repeatable test case (exact steps/sample code, which pattern are using?, create table statements and data etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.
[28 Apr 2018 1:00] Bugs System
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".