Oracle JDBC: Inconsistent handling of timestamps

[Peter Conrad <conrad () tivano de>]

Product: Oracle database 8.1.7 & JDBC "thin" driver 8.1.7.1
Issue:   Inconsistent handling of timestamps
Impact:  Minor (as a security issue, what comes to mind is bad timestamps
         when logging to an Oracle DB)
         Could be a major problem for any application relying on certain
         timestamp properties, though.
History: Posted to Oracle JDBC web forum on October 28th - no vendor response
         (see http://www.oracle.com/forums/message.jsp?id=1422122&gid=390686 )
         Sent email to secalert_us () oracle com on December 3rd
          - vendor acknowledged receipt on Dec 4
         Requested status from secalert_us () oracle com on Feb 18 and Mar 17
          - no response so far


Description:

Certain java.sql.Timestamp values aren't written to (or retrieved from)
the database correctly. Timestamps affected are in the time interval just
before switchover from DST to non-DST (the bug was noticed on 
October 27th 2002 for the first time, when the switchover from MET/DST to MET
took place). Various timestamp values in the range
2:00 AM - 2:59:59 AM (MET/DST) on October 27th 2002 as well as on October
26th 2003 have been verified to reproduce the bug, with the database as
well as the JDBC client running in MET.

What happens is this:

 - We insert a new row into table T, column C , giving it timestamp X, like
   INSERT INTO T (C) VALUES (X)

 - Later, we try to retrieve the row using
   ResultSet = SELECT C FROM T WHERE C = X

 - We find that ResultSet.C <> X!
   (More precisely: ResultSet.C = X + 1 hour)


Example code:

The following code snippet can be used to reproduce the bug in the MET 
timezone. The "problem" timestamp probably has to be adjusted for other
timezones.


    Connection c = DriverManager.getConnection(DB_URL, DB_USER, DB_PWD);
    PreparedStatement p = c.prepareStatement("CREATE TABLE BugTest (ts DATE NOT NULL)");
    p.execute();
    p.close();

    Timestamp problem = new Timestamp(1067130000000L); // 26.10.03 02:00 MET/DST

    p = c.prepareStatement("INSERT INTO BugTest (ts) VALUES (?)");
    p.setTimestamp(1, problem);
    p.execute();
    p.close();

    p = c.prepareStatement("SELECT * FROM BugTest WHERE ts = ?");
    p.setTimestamp(1, problem);
    ResultSet rs = p.executeQuery();
    if (rs.next()) {
        Timestamp ts = rs.getTimestamp(1);
        if (ts.equals(problem)) {
            System.out.println("Everything's OK");
        } else {
            System.out.println("Gotcha! DB returns " + ts.getTime()
                               + " but we gave it "
                               + problem.getTime()
                               + "!");
        }
    }
    p = c.prepareStatement("DROP TABLE BugTest");
    p.execute();
    p.close();
    c.close();

-- 
Peter Conrad                        Tel: +49 6102 / 80 99 072
[ t]ivano Software GmbH             Fax: +49 6102 / 80 99 071
Bahnhofstr. 18
63263 Neu-Isenburg

Germany