Обсуждение: BUG #3106: A problem with escaping table name pattern for DatabaseMetaData.getColumns()

The following bug has been logged online:

Bug reference:      3106
Logged by:          Valery Meshkov
Email address:      meshok2001-news@yahoo.com
PostgreSQL version: 8.2.3
Operating system:   Windows
Description:        A problem with escaping table name pattern for
DatabaseMetaData.getColumns()
Details:

I am seeing a problem in the JDBC driver postgresql-8.2-504.jdbc3.jar with
getting columns of the table 'A_B'.  I am escaping  '_'  with the escape
value returned by DatabaseMetaData.getSearchStringEscape(), which in my case
is "\\\\"  (standard_conforming_strings is off).  When I pass the resulting
table name 'A\\_B' to DatabaseMetaData.getColumns() the number of
backslashes doubles again, resulting in 4 backslashes in the select
statement:

SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,
a.attlen,a.attnum,def.adsrc,dsc.description
FROM pg_catalog.pg_namespace n  JOIN pg_catalog.pg_class c ON
(c.relnamespace = n.oid)
JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid)
LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum
= def.adnum)
LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum =
dsc.objsubid)
LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND
dc.relname='pg_class')
LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND
dn.nspname='pg_catalog')  WHERE a.attnum > 0 AND NOT a.attisdropped  AND
c.relname LIKE 'A\\\\_B'  ORDER BY nspname,relname,attnum

With this select no columns are found.  If I change the escape string in the
debugger to have just one backslash ("\\") everything works fine.  It is
also working with the 8.1-404 driver.

On Sun, 4 Mar 2007, Valery Meshkov wrote:

> The following bug has been logged online:
>
> Bug reference:      3106
> PostgreSQL version: 8.2.3
> Description:        A problem with escaping table name pattern for
> DatabaseMetaData.getColumns()
> Details:
>
> I am seeing a problem in the JDBC driver postgresql-8.2-504.jdbc3.jar with
> getting columns of the table 'A_B'.  I am escaping  '_'  with the escape
> value returned by DatabaseMetaData.getSearchStringEscape(), which in my case
> is "\\\\"  (standard_conforming_strings is off).  When I pass the resulting
> table name 'A\\_B' to DatabaseMetaData.getColumns() the number of
> backslashes doubles again, resulting in 4 backslashes in the select
> statement:
>

The problem is that there is a different search string escape depending on
whether you plan to interpolate it into a query or pass it as a parameter
to a PreparedStatement.  The getSearchStringEscape method is assuming
you're going to interpolate it into a query and returns the doubled
version.  getColumns is assuming you're passing a parameter that it
then interpolates and must escape itself.

The fact that the javadoc for getColumns has a see also for
getSearchStringEscape implies to me that our implementation is wrong and
it shouldn't return the doubled version and anyone interpolating text into
a query must escape it appropriately including the search string escape.

I'll put a fix for this into the next release.

Kris Jurka