Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-jdbc by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group