Re: Bug with PreparedStatements using EXTRACT function

From: Matthias Böhm <fliegenblues(at)gmx(dot)net>
To: "'Maciek Sakrejda'" <msakrejda(at)truviso(dot)com>
Cc: <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Bug with PreparedStatements using EXTRACT function
Date: 2012-03-30 10:13:47
Message-ID: 000001cd0e5d$cf737490$6e5a5db0$@net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

> If you use one of the type-specific setters, it should work.

No, it doesn't: I'm using setDate(int, java.util.sql.Date) or
setTimestamp(int, java.util.sql.Timestamp) for setting the parameter, but
the information about the parameter type seems to get lost:

A legal query without using a parameter is:

SELECT EXTRACT (YEAR FROM DATE '1990-06-07 +02:00:00')

But when I prepare a statement with "SELECT EXTRACT (YEAR FROM ?)" and use
the setDate method to set the parameter and after this, use the toString
method of the PreparedStatement, I get the following:

SELECT EXTRACT (YEAR FROM '1991-09-07 +02:00:00')

In this query the obviously required DATE is missing!

On the contrary, trying to prepare a statement with "SELECT EXTRACT (YEAR
FROM DATE ?)" results, after calling toString on the prepared statement, in
the following, seemingly correct query:

SELECT EXTRACT (YEAR FROM DATE '1991-09-07 +02:00:00')

But when I try to execute the query I get a "syntax error at $1".

So there seems no way to prepare such a query without using the explicit
cast.

> The driver does attempt to do
> some type-guessing based on the Java "source" parameter type, but
> there is something of a disconnect between Java and Postgres types, so
> this does *not* occur for java.util.Date, and for java.sql.Timestamp,
> it occurs in such a way as to break for your use case

Yes, I took a look at the code, and the problem seems to be that bindString
method is called with Oid.UNSPECIFIED, so that the information about the
type is lost.

Still it is not clear to me why preparing a statement with "SELECT EXTRACT
(YEAR FROM DATE ?)" doesn't work as well, because it yields, as already
mentioned above, a seemingly valid query.

In response to

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Jan Lieskovsky 2012-03-30 12:28:02 CVE DISPUTE notification: postgresql-jdbc: SQL injection due improper escaping of JDBC statement parameters
Previous Message Maciek Sakrejda 2012-03-29 16:45:36 Re: Bug with PreparedStatements using EXTRACT function