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

[Johnathon Lee (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-2138?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12710933#comment-12710933 ] 

Johnathon Lee commented on TEIID-2138:
--------------------------------------

Given your last statement [1],  I believe the expectation from the client(s) is for each of those examples to actually return 1 record [2] and that they are attempting to use the backslash as an escape already.  Do you have thoughts that there is a connection property that the clients can change in order for this to occur?


[1]
Checking with pg, they do indeed default their like escape to '\' (which is not conforming, but common). The best resolution is to explicitly specify the escape character e.g. 'x' like ... escape '\'. However if these are clients that cannot change their sql, then we'll have to offer yet another pg emulation option.

[2]
Record: ddl_alterable
                
> 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
>
> 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