search

babelfish-for-postgresql / babelfish_extensions

Babelfish for PostgreSQL provides the capability for PostgreSQL to work with applications written for Microsoft SQL Server. Babelfish understands the SQL Server wire-protocol and T-SQL, the Microsoft SQL Server query and procedural language, so you don’t have to switch database drivers or rewrite all of your application queries.
https://babelfishpg.org/
Apache License 2.0
273 stars 93 forks source link

Too strict handling of table_type parameter in sp_tables procedure #2404

Closed staticlibs closed 5 months ago

staticlibs commented 6 months ago

What happened?

With sp_tables procedure Babelfish supports table_type parameter, for example the following call will return both tables and view:

EXEC sp_tables NULL, NULL, NULL, '''VIEW'',''TABLE'''

But is appeared that table_type parameter handling in MSSQL is more relaxed. For example, the following query is sent by IBM Cognos Analytics application, it is likely be passed through mssql-jdbc impl of JDBC getTables introspection method:

EXEC sp_tables NULL, NULL, NULL, '''''''SYSTEM TABLE'''',''''TABLE'''',''''VIEW'''''''

This "double-escaped" query returns empty results in Babelfish.

Besides this double-quoted example, MSSQL also accepts table_type with unbalanced quotes like this:

EXEC sp_tables NULL, NULL, NULL, '''TABLE'''''',''''VIEW'',''SYSTEM TABLE'''

Current Babelfish handling of table_type with unnest(string_to_array(table_type, ',')) seems to be too strict, it is suggested to relax it to accepts strings with invalid quotation that are accepted by MSSQL.

Version

BABEL_3_X_DEV (Default)

Extension

babelfishpg_tsql (Default)

Which flavor of Linux are you using when you see the bug?

Fedora

Relevant log output

No response

Code of Conduct

staticlibs commented 5 months ago

PR was merged, closing the issue.