Hi everybody!
There is a particular type of input that causes trouble when calling
DatabaseMetaData's methods such as getTables(), ecc.

The input I'm talking about are strings containing backslash characters
in situations like the following:
dbmd.getTables(null, null, "my\\table", types);

The result is generally a wrong answer from the getTables method or, in
the worst situation where the backslash is the last character, a
PSQLException.

Some sample code:

public void testPostgresQuote() throws Exception {
System.out.println("testPostgresQuote:");
Connection conn = null;
try {
String[] types = {"TABLE"};
conn = ConnectionFactory.getConnection();
DatabaseMetaData dbmd = conn.getMetaData();
dbmd.getTables(null, null, "\\", types);
} finally {
if (conn != null) {
try {conn.close();} catch (Exception e2) {}
}
}
}

this causes a nasty exception (and opens a possibility for SQL
injection?):

ERROR: unterminated quoted string at or near "'\' AND (false ) ORDER
BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME "
org.postgresql.util.PSQLException: ERROR: unterminated quoted string at
or near "'\' AND (false ) ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME "
at
org.postgresql.core.v3.QueryExecutorImpl.receiveEr rorResponse(QueryExecutorImpl.java:1512)
at
org.postgresql.core.v3.QueryExecutorImpl.processRe sults(QueryExecutorImpl.java:1297)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(Q ueryExecutorImpl.java:188)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execut e(AbstractJdbc2Statement.java:430)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execut eWithFlags(AbstractJdbc2Statement.java:332)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execut eQuery(AbstractJdbc2Statement.java:231)
at
org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData .getTables(AbstractJdbc2DatabaseMetaData.java:2190 )


I've looked at the source code for postgresql-jdbc-8.1-404 and the
trouble seems to come from the escapeQuotes function in
AbstractJdbc2DatabaseMetaData.java

protected static String escapeQuotes(String s) {
StringBuffer sb = new StringBuffer();
int length = s.length();
char prevChar = ' ';
char prevPrevChar = ' ';
for (int i = 0; i < length; i++)
{
char c = s.charAt(i);
sb.append(c);
if (c == '\'' && (prevChar != '\\' || (prevChar == '\\' &&
prevPrevChar == '\\')))
{
sb.append("'");
}
prevPrevChar = prevChar;
prevChar = c;
}
return sb.toString();
}


I believe escapeQuotes only checks for single quotes, and does nothing
to backslashes.
My temporary solution has been to patch escapeQuotes as follows:

protected static String escapeQuotes(String s) {
StringBuffer sb = new StringBuffer();
int length = s.length();
char prevChar = ' ';
char prevPrevChar = ' ';
for (int i = 0; i < length; i++)
{
char c = s.charAt(i);
if ( c == '\\' || c == '\'' )
sb.append('\\');

sb.append(c);
}
return sb.toString();
}

.... which seems to work.

Regards

Paolo

On Fri, 3 Feb 2006, Paolo Predonzani wrote:

> Hi everybody!
> There is a particular type of input that causes trouble when calling
> DatabaseMetaData's methods such as getTables(), ecc.
>
> The input I'm talking about are strings containing backslash characters
> in situations like the following:
> dbmd.getTables(null, null, "my\\table", types);
>

I've applied your patch to 8.0, 8.1 and HEAD CVS branches. I'll also
note that a search for "my\\table" still won't work because backslash is
the LIKE pattern escape character as well, so you'll need "my\\\\table".

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly