How to prevent "bad" dates in Oracle?

Question

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?

Adding a constraint if you only accept a date after # or before # ? — AxelH, Jun 24 '19 at 08:44
There isn't much you can do on the DB side to prevent invalid/corrupt data being hand-crafted. Your application should use proper data types rather than converting from raw. But there are instances of things like this being allowed anyway. (The `imp` tool used to corrupt dates by a similar mechanism, as I recall.) However, why do you *guess* this is related to raw values; how are the back-end people *actually* creating the bad data? What data type is the DB column? (I don't see anything to suggest you're storing dates as string, but it isn't clear that you are not, I suppose...) — Alex Poole, Jun 24 '19 at 08:57
Fix your java code to sanitize data before it inserts it into the database. If you are just allowing arbitrary java code to be executed then what do you expect when someone says to you "we ran this malicious code and, guess what, it did something malicious!". — MT0, Jun 24 '19 at 09:02
@MT0 - Yes, you are 100% right. But this is not my Java code and I am not a project manager. I can't tell anyone what to do. — user3687706, Jun 24 '19 at 09:17
@user3687706 - you may not be able to tell them what to do, but you can tell them (and your project manager) what they are doing wrong, and why it isn't your fault - and that it has to be fixed at the Java end. — Alex Poole, Jun 24 '19 at 09:19
@user3687706 but surely you can raise concerns to project managers or your managers, and add suggestions to mitigate the issue? — Boneist, Jun 24 '19 at 09:20
@a_horse_with_no_name - I think you're right, but you can at least get dates that sometimes *appear* to be 0000-00-00, [as here](https://stackoverflow.com/q/12455152/266304). — Alex Poole, Jun 24 '19 at 09:41
@a_horse_with_no_name Try this: ```create table test_table ( field date ); / declare d date; begin dbms_stats.convert_raw_value(hextoraw('48484648484648484848'), d); insert into test_table values(d); commit; end; /``` — user3687706, Jun 24 '19 at 09:44
`select to_char(field, 'YYYY-MM-DD') from test_table;` => 0000-00-00. Some clients, at least, don't see that when relying on NLS settings. — Alex Poole, Jun 24 '19 at 09:58

MT0 · Accepted Answer · 2019-06-24T09:51:48.063

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

The output seems to depend on the SQL client being used. SQL Developer shows me: https://i.imgur.com/GUeFesQ.png — , Jun 24 '19 at 09:56
@a_horse_with_no_name It probably depends on the `NLS_DATE_FORMAT` settings. The [DB<>Fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=deebd0c5891fcfff1e4e16d154346501) explicitly sets it to `fmYYYY-MM-DD"T"HH24:MI:SS` (the ISO8601 version of the OP's NLS settings from the question). — MT0, Jun 24 '19 at 09:58
It seems to be the client as well as the settings (SQL Developer still doesn't show zeros with NLS_DATE_FORMAT set like that). An explicit `to_char()` will though. Incidentally, another reason not to rely on this extra check is that you could end up with a *wrong* date that is still valid - just not what was intended. Corruption isn't necessarily obvious. — Alex Poole, Jun 24 '19 at 10:01
@AlexPoole You could also use the `DUMP` function ([db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=441363ab975ae70fc7c95eceeb9a1689)) to see the binary values for the underlying `DATE` value and compare that on different systems regardless of whether the client and/or NLS settings are different. — MT0, Jun 24 '19 at 10:05

How to prevent "bad" dates in Oracle?

1 Answers1