How to prevent “bad” dates in Oracle?

I am working on a database for our project. Our back-end guys were able to insert ‘00.00.0000’ date into Oracle from Java (column data type is DATE). I guess it’s something to do with raw values as in this example:

alter session set nls_date_format='fmdd.month.yyyy hh24:mi:ss';
declare
  d date;
begin
  dbms_stats.convert_raw_value(hextoraw('7764057f7f77aa'), d);
  dbms_output.put_line(d);
end;
/

127.may.1900 126:118:87

How can I prevent this?

Answer

You can use a function to check whether dates are valid by converting them to a string and back and add this to a CHECK constraint to your date columns to ensure all dates are valid.

However, you should not need to do this as you should be mandating that arbitrary code is not executed on your systems and all the application code should include input validation so that only sanitized values are inserted into the database.

An example:

Oracle Setup:

CREATE TABLE Dates (
  d DATE
);

CREATE FUNCTION isValidDate( dt IN DATE ) RETURN NUMBER
IS
  d DATE;
BEGIN
  d := TO_DATE( TO_CHAR( dt, 'fxYYYY-MM-DD HH24:MI:SS' ), 'fxYYYY-MM-DD HH24:MI:SS' );
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;

Insert Dates:

declare
  d DATE;

  FUNCTION createUnvalidatedDate(
    centuries INT := 0,
    years     INT := 0,
    months    INT := 0,
    days      INT := 0,
    hours     INT := 0,
    minutes   INT := 0,
    seconds   INT := 0
  ) RETURN DATE
  IS
    dt         DATE;
    hex_string CHAR(14);
  BEGIN
    hex_string := LPAD( TO_CHAR( centuries + 100, 'fmXX' ), 2, '0' )
               || LPAD( TO_CHAR( years + 100,     'fmXX' ), 2, '0' )
               || LPAD( TO_CHAR( months,          'fmXX' ), 2, '0' )
               || LPAD( TO_CHAR( days,            'fmXX' ), 2, '0' )
               || LPAD( TO_CHAR( hours + 1,       'fmXX' ), 2, '0' )
               || LPAD( TO_CHAR( minutes + 1,     'fmXX' ), 2, '0' )
               || LPAD( TO_CHAR( seconds + 1,     'fmXX' ), 2, '0' );
    dbms_stats.convert_raw_value(hextoraw(hex_string), dt);
    RETURN dt;
  END;
begin
  d := createUnvalidatedDate( 19, 0, 5, 127, 126, 118, 87 );
  INSERT INTO Dates ( d ) VALUES ( d );
  d := createUnvalidatedDate( 0, 0, 0, 0, 0, 0, 0 );
  INSERT INTO Dates ( d ) VALUES ( d );
  d := createUnvalidatedDate( 20, 19, 6, 24, 10, 28, 30 );
  INSERT INTO Dates ( d ) VALUES ( d );
end;
/

Check Validity:

SELECT d, isValidDate( d ) AS valid
FROM   Dates;

Outputs:

D                     | VALID
:-------------------- | ----:
1900-5-127T126:118:87 |     0
0-0-0T0:0:0           |     0
2019-6-24T10:28:30    |     1

Which shows that the first two values are invalid and the last one is a valid date.

db<>fiddle here