[teiid-issues] [JBoss JIRA] (TEIID-2138) Use of the PG DSN for for ODBC metadata queries can result in improperly escaped SQL

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-2138?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-2138.
-----------------------------------

    Fix Version/s: 8.2
       Resolution: Done


Added an emulation option via the system property org.teiid.backslashDefaultMatchEscape.  For backporting, the admin guide docbook will need updated to have the appropriate entry from https://docs.jboss.org/author/display/TEIID/System+Properties

It may also be desirable as we add more properties to either add a single master property to enable all emulation options or to revisit localizing their effect to the odbc transport now that (as of 8.1) we support passing commands via object format over the embedded driver.  That gives us a way to pre-parse and rewrite rather than relying on global effects.
                
> Use of the PG DSN for for ODBC metadata queries can result in improperly escaped SQL
> ------------------------------------------------------------------------------------
>
>                 Key: TEIID-2138
>                 URL: https://issues.jboss.org/browse/TEIID-2138
>             Project: Teiid
>          Issue Type: Bug
>          Components: ODBC, Query Engine
>    Affects Versions: 7.7
>            Reporter: Johnathon Lee
>            Assignee: Steven Hawkins
>             Fix For: 8.2
>
>
> Various clients (Cognos, Excel, DBVisualizer) exhibit different results when querying metadata.
> For instance:
> Querying ModeShape on a fresh 5.3 deploy.
> {code}
> select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl\\_alterable' and n.oid = relnamespace
> {code}
>   0 Records
> {code}
> select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl_alterable' and n.oid = relnamespace
> {code}
>   1 Record:  ddl_alterable
> {code}
> select relname from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relname like E'ddl\_alterab__' and n.oid = relnamespace
> {code}
>   1 Record:  ddl_alterable
> Looking at [1] per [2].  It seems proper escaping of the E'literal' syntax is not always being performed.  The Third example above shows that a wildcard is being escaped and happens to match to a literal value it expects.
> [1]
> PostgreSQL also accepts "escape" string constants, which are an extension to the SQL standard. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g. E'foo'. (When continuing an escape string constant across lines, write E only before the first opening quote.) Within an escape string, a backslash character (\) begins a C-like backslash escape sequence, in which the combination of backslash and following character(s) represents a special byte value. \b is a backspace, \f is a form feed, \n is a newline, \r is a carriage return, \t is a tab. Also supported are \digits, where digits represents an octal byte value, and \xhexdigits, where hexdigits represents a hexadecimal byte value. (It is your responsibility that the byte sequences you create are valid characters in the server character set encoding.) Any other character following a backslash is taken literally. Thus, to include a backslash character, write two backslashes (\\). Also, a single quote can be included in an escape string by writing \', in addition to the normal way of ''.
> [2]  http://www.postgresql.org/docs/8.3/interactive/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira