[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:comment-tabpanel&focusedCommentId=12710913#comment-12710913 ] 

Steven Hawkins edited comment on TEIID-2138 at 8/10/12 10:19 AM:
-----------------------------------------------------------------

Was there something in the pg escape reference that you thought seemed significant?  When in doubt just check the value using "SELECT E'...'"

All three of your examples are returning the correct results (according to Teiid).  {code}E'ddl\\_alterable'{code} means {code}'ddl\_alterable'{code} which without specifying the like escape character must match something with 'ddl', followed by '\', followed by any character, followed by 'alterable'

That is inline with the sql spec, but on some platforms they default to a like escape character of '\', which changes the meaning of the first match pattern.

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.
                
      was (Author: shawkins):
    Was there something in the pg escape reference that you thought seemed significant?  When in doubt just check the value using "SELECT E'...'"

All three of your examples are returning the correct results (according to Teiid).  E'ddl\\_alterable' means 'ddl\_alterable' which without specifying the like escape character must match something with 'ddl', followed by '\', followed by any character, followed by 'alterable'

That is inline with the sql spec, but on some platforms they default to a like escape character of '\', which changes the meaning of the first match pattern.

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.
                  
> 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