Peter Conrad
01/04/03, 09:35
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
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