Re: A creepy story about dates. How to prevent it?

On Wed, 18 Jun 2003, Tom Lane wrote:

> "scott.marlowe" <scott.marlowe@ihs.com> writes:
> > IMHO it is a bug.  We don't let postgresql "guess" about a lot of more
> > obvious things (i.e. int4 to int8 casting, etc...) and letting it guess
> > about dates makes it non-ACID compliant.
>
> How do you arrive at that conclusion?

The same way I come to all my conclusions, logic. :-)  but seriously...

Why not accept a date of 04/44/2003 and just wrap it into May?  It's
the same kind of thing.  I told my database where I live, and expect it to
only accept dates that are valid in my locale.  If a user feeds it a date
that isn't right, I expect the database to error out.

> > If it isn't a bug, how do I implement a check constraint to stop it from
> > happening?  I'd like to know my database accepts properly formatted input
> > and rejects the rest.  That's what the C in ACID means, right?
>
> Do the checking in your application.

I do.  I make sure it's ##/##/#### (i.e. a simple regex works)

The database already does the rest of the checking for me, it just happens
to think it might be helpful to coerce some bad dates for me, but others
that are obviously wrong are tossed out.

Here's a scenario for how we can wind up teaching a user to enter dates
the wrong way.  The day is 22 feb.  They enter this date, in the US, where
mm/dd/yyyy is standard:

22/02/2003

The database converts it to

02/22/2003 silently.

Next day, they enter

23/02/2003

Again, it takes it silently.

So on and so forth.  On the first day of march they put in:

01/03/2003 which the database takes as January 03, and happily puts it in.
with a couple of weeks of "training" the user now believes they are
putting the date right, but it is wrong.  No error.

Next day, we get

02/03/2003.  The database puts in Feb 03.  Again, the user doesn't know.

We continue the rest of the year this way.  Somewhere along the line, the
user notices all their reports have the wrong date.  Which ones were for
feb 03 and which ones were for march 02?  We don't know.

Why bother checking for wrong dates sometimes (i.e. 03/33/2003) but not
all the time (i.e. 13/03/2003)?

> Something you think is improperly
> formatted probably shouldn't get to the database in the first place.

Agreed.  But that's not the point.  It is properly formatted, i.e.
mm/dd/yyyy, it's just out of range.  That's not the same at all.

> If you aren't doing any format checking at all, you're possibly
> vulnerable to SQL injection attacks.

I do plenty of format checking, this isn't the same.  This is range
checking.  I expect my database to do that for me.

> I do now seem to recall an agreement that a GUC switch to disable
> date-interpretation guessing would be okay, though.

I'm pretty sure it was the other way around, make strict locale / date
checking the standard and a GUC to turn it off for folks who really want
to use a broken database.  :-)