Home page logo

bugtraq logo Bugtraq mailing list archives

Oracle JDBC: Inconsistent handling of timestamps
From: Peter Conrad <conrad () tivano de>
Date: Mon, 31 Mar 2003 10:48:05 +0200

Product: Oracle database 8.1.7 & JDBC "thin" driver
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


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

 - 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

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

    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 = 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");

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


  By Date           By Thread  

Current thread:
  • Oracle JDBC: Inconsistent handling of timestamps Peter Conrad (Mar 31)
[ Nmap | Sec Tools | Mailing Lists | Site News | About/Contact | Advertising | Privacy ]