Thread: escapeQuotes causes faults in DatabaseMataData

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.receiveErrorResponse(QueryExecutorImpl.java:1512)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:430)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:332)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(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