java - 如何防止Oracle中的 "bad"日期？

Question

我正在为我们的项目开发数据库。我们的后端人员能够从 Java 将“00.00.0000”日期插入 Oracle(列数据类型为 DATE)。我想这与原始值有关，如下例所示:

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

如何防止这种情况发生？

Answer 1

您可以使用函数来检查日期是否有效，方法是将其转换为字符串并返回，并将其添加到日期列的 CHECK 约束中，以确保所有日期都有效。

但是，您不需要这样做，因为您应该强制要求不在您的系统上执行任意代码，并且所有应用程序代码都应包含输入验证，以便仅将经过清理的值插入到数据库。

一个例子:

Oracle 设置:

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;

插入日期:

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;
/

检查有效性:

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

输出:

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

这表明前两个值无效，最后一个值是有效日期。

db<> fiddle here