From ad1225d2438fad05683c739149d8568e8d1f894e Mon Sep 17 00:00:00 2001 From: Stefan Breunig Date: Tue, 2 Apr 2019 16:16:30 +0200 Subject: [PATCH] clamp dates with invalid years to 0 Asp er the MySQL documentation DATE and DATETIME only support ranges from '1000-01-01 00:00:00' to '9999-12-31 23:59:59', and TIMESTAMP from '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'. Values outside those ranges are not guaranteed to work. Additionally it mentions that invalid values are converted to their appropriate zero value. The patch converts dates that lead to an exception right now to their zero value. This is more lenient than what the ranges given in the documentation. The behaviour set through zeroDateTimeBehavior applies to these zero values. This fixes Bug#94872. --- .../mysql/cj/result/SqlDateValueFactory.java | 3 + .../cj/protocol/a/MysqlTextValueDecoder.java | 9 ++ .../cj/LocalizedErrorMessages.properties | 1 + src/test/java/testsuite/simple/DateTest.java | 131 ++++++++++++++++++ 4 files changed, 144 insertions(+) diff --git a/src/main/core-impl/java/com/mysql/cj/result/SqlDateValueFactory.java b/src/main/core-impl/java/com/mysql/cj/result/SqlDateValueFactory.java index 9fd5a1fe5..f70defe93 100644 --- a/src/main/core-impl/java/com/mysql/cj/result/SqlDateValueFactory.java +++ b/src/main/core-impl/java/com/mysql/cj/result/SqlDateValueFactory.java @@ -76,6 +76,9 @@ public Date localCreateFromDate(InternalDate idate) { if (idate.isZero()) { throw new DataReadException(Messages.getString("ResultSet.InvalidZeroDate")); } + if (idate.getYear() <= 0) { + throw new DataReadException(Messages.getString("ResultSet.InvalidYear", new Object[] { idate.getYear() })); + } this.cal.clear(); this.cal.set(idate.getYear(), idate.getMonth() - 1, idate.getDay()); diff --git a/src/main/protocol-impl/java/com/mysql/cj/protocol/a/MysqlTextValueDecoder.java b/src/main/protocol-impl/java/com/mysql/cj/protocol/a/MysqlTextValueDecoder.java index e2105aee0..7da8e92ce 100644 --- a/src/main/protocol-impl/java/com/mysql/cj/protocol/a/MysqlTextValueDecoder.java +++ b/src/main/protocol-impl/java/com/mysql/cj/protocol/a/MysqlTextValueDecoder.java @@ -67,6 +67,9 @@ /** Max string length of a signed long = 9223372036854775807 (19+1 for minus sign) */ public static final int MAX_SIGNED_LONG_LEN = 20; + /** Min supported year when parsing DATE, DATETIME and TIMESTAMP columns. This is more lenient than the ranges stated in the MySQL documentation. */ + private static final int DATE_MIN_VALID_YEAR = 1; + public T decodeDate(byte[] bytes, int offset, int length, ValueFactory vf) { return vf.createFromDate(getDate(bytes, offset, length)); } @@ -256,6 +259,9 @@ public static InternalDate getDate(byte[] bytes, int offset, int length) { throw new DataReadException(Messages.getString("ResultSet.InvalidLengthForType", new Object[] { length, "DATE" })); } int year = getInt(bytes, offset, offset + 4); + if (year < DATE_MIN_VALID_YEAR) { + return new InternalDate(); + } int month = getInt(bytes, offset + 5, offset + 7); int day = getInt(bytes, offset + 8, offset + 10); return new InternalDate(year, month, day); @@ -353,6 +359,9 @@ public static InternalTimestamp getTimestamp(byte[] bytes, int offset, int lengt } int year = getInt(bytes, offset, offset + 4); + if (year < DATE_MIN_VALID_YEAR) { + return new InternalTimestamp(); + } int month = getInt(bytes, offset + 5, offset + 7); int day = getInt(bytes, offset + 8, offset + 10); int hours = getInt(bytes, offset + 11, offset + 13); diff --git a/src/main/resources/com/mysql/cj/LocalizedErrorMessages.properties b/src/main/resources/com/mysql/cj/LocalizedErrorMessages.properties index 0cb7be30c..e447df371 100644 --- a/src/main/resources/com/mysql/cj/LocalizedErrorMessages.properties +++ b/src/main/resources/com/mysql/cj/LocalizedErrorMessages.properties @@ -423,6 +423,7 @@ ResultSet.UnableToConvertString=Cannot convert string ''{0}'' to {1} value ResultSet.UnknownSourceType=Cannot decode value of unknown source type ResultSet.InvalidTimeValue=The value ''{0}'' is an invalid TIME value. JDBC Time objects represent a wall-clock time and not a duration as MySQL treats them. If you are treating this type as a duration, consider retrieving this value as a string and dealing with it according to your requirements. ResultSet.InvalidZeroDate=Zero date value prohibited +ResultSet.InvalidYear=The date ''{0}'' is out of the supported range. # # Usage advisor messages for ResultSets diff --git a/src/test/java/testsuite/simple/DateTest.java b/src/test/java/testsuite/simple/DateTest.java index 190fcbbd6..86f6bbe43 100644 --- a/src/test/java/testsuite/simple/DateTest.java +++ b/src/test/java/testsuite/simple/DateTest.java @@ -173,6 +173,137 @@ public void testNanosParsing() throws SQLException { } } + /** + * Tests the behaviour on columns with out of range year values. + * + * @throws Exception + * if the test fails. + */ + public void testInvalidYearBehaviour() throws Exception { + Connection testConn = this.conn; + Connection roundConn = null; + Connection nullConn = null; + Connection exceptionConn = null; + try { + if (versionMeetsMinimum(5, 7, 4)) { + Properties props = new Properties(); + props.setProperty(PropertyKey.jdbcCompliantTruncation.getKeyName(), "false"); + if (versionMeetsMinimum(5, 7, 5)) { + String sqlMode = getMysqlVariable("sql_mode"); + if (sqlMode.contains("STRICT_TRANS_TABLES")) { + sqlMode = removeSqlMode("STRICT_TRANS_TABLES", sqlMode); + props.setProperty(PropertyKey.sessionVariables.getKeyName(), "sql_mode='" + sqlMode + "'"); + } + } + testConn = getConnectionWithProps(props); + this.stmt = testConn.createStatement(); + } + + this.stmt.executeUpdate("DROP TABLE IF EXISTS testInvalidYearBehaviour"); + this.stmt.executeUpdate("CREATE TABLE testInvalidYearBehaviour(fieldAsString VARCHAR(32), fieldAsDateTime DATETIME)"); + this.stmt.executeUpdate("INSERT INTO testInvalidYearBehaviour VALUES ('0000-12-12 00:00:00', '0000-12-12 00:00:00')"); + + roundConn = getConnectionWithProps("zeroDateTimeBehavior=ROUND"); + Statement roundStmt = roundConn.createStatement(); + this.rs = roundStmt.executeQuery("SELECT fieldAsString, fieldAsDateTime FROM testInvalidYearBehaviour"); + this.rs.next(); + + assertEquals("0001-01-01", this.rs.getDate(1).toString()); + assertEquals("0001-01-01", this.rs.getDate(2).toString()); + + PreparedStatement roundPrepStmt = roundConn.prepareStatement("SELECT fieldAsString, fieldAsDateTime FROM testInvalidYearBehaviour"); + this.rs = roundPrepStmt.executeQuery(); + this.rs.next(); + + assertEquals("0001-01-01", this.rs.getDate(1).toString()); + assertEquals("0001-01-01", this.rs.getDate(2).toString()); + + nullConn = getConnectionWithProps("zeroDateTimeBehavior=CONVERT_TO_NULL"); + Statement nullStmt = nullConn.createStatement(); + this.rs = nullStmt.executeQuery("SELECT fieldAsString, fieldAsDateTime FROM testInvalidYearBehaviour"); + + this.rs.next(); + + assertNull(this.rs.getDate(1)); + assertNull(this.rs.getTimestamp(1)); + assertNull(this.rs.getDate(2)); + assertNull(this.rs.getTimestamp(2)); + + PreparedStatement nullPrepStmt = nullConn.prepareStatement("SELECT fieldAsString, fieldAsDateTime FROM testInvalidYearBehaviour"); + this.rs = nullPrepStmt.executeQuery(); + + this.rs.next(); + + assertNull(this.rs.getDate(1)); + assertNull(this.rs.getTimestamp(1)); + assertNull(this.rs.getDate(2)); + assertNull(this.rs.getTimestamp(2)); + + exceptionConn = getConnectionWithProps("zeroDateTimeBehavior=EXCEPTION"); + Statement exceptionStmt = exceptionConn.createStatement(); + this.rs = exceptionStmt.executeQuery("SELECT fieldAsString, fieldAsDateTime FROM testInvalidYearBehaviour"); + + this.rs.next(); + + try { + this.rs.getDate(1); + fail("Exception should have been thrown when trying to retrieve invalid date"); + } catch (SQLException sqlEx) { + assertTrue(MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx.getSQLState())); + } + + try { + this.rs.getTimestamp(1); + fail("Exception should have been thrown when trying to retrieve invalid date"); + } catch (SQLException sqlEx) { + assertTrue(MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx.getSQLState())); + } + + try { + this.rs.getDate(2); + fail("Exception should have been thrown when trying to retrieve invalid date"); + } catch (SQLException sqlEx) { + assertTrue(MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx.getSQLState())); + } + + try { + this.rs.getTimestamp(2); + fail("Exception should have been thrown when trying to retrieve invalid date"); + } catch (SQLException sqlEx) { + assertTrue(MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx.getSQLState())); + } + + PreparedStatement exceptionPrepStmt = exceptionConn.prepareStatement("SELECT fieldAsString, fieldAsDateTime FROM testInvalidYearBehaviour"); + + try { + this.rs = exceptionPrepStmt.executeQuery(); + this.rs.next(); + this.rs.getDate(2); + fail("Exception should have been thrown when trying to retrieve invalid date"); + } catch (SQLException sqlEx) { + assertTrue(MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx.getSQLState())); + } + + } finally { + this.stmt.executeUpdate("DROP TABLE IF EXISTS testInvalidYearBehaviour"); + if (exceptionConn != null) { + exceptionConn.close(); + } + + if (nullConn != null) { + nullConn.close(); + } + + if (roundConn != null) { + roundConn.close(); + } + + if (testConn != this.conn) { + testConn.close(); + } + } + } + /** * Tests the configurability of all-zero date/datetime/timestamp handling in the driver. *