Re: [BUGS] BUG #3106: A problem with escaping table name pattern for DatabaseMetaData.getColumns()

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