An unusual bug

Some of our QA people recently came across some unexpected behavior that took them a while to explain. They were testing format-preserving encryption with an Oracle database. They encryped a date, stored it in Oracle, read the encrypted value back out and then decrypted it. When this failed, they started trying to figure out what was going wrong, which took a while.

It seems that the ANSI date is only defined for dates from January 1, 1601 forward. Something like February 12, 1523 isn't a valid ANSI date, for example. But if you use FPE to encrypt a date and you don't define how the format is being preserved carefully enough, it's possible to get a valid date mapped to an invalid date.

It seems that if it's configured a particular way, Oracle doesn't like ANSI dates that are before 1/1/1601 and will automatically change them to 1/1/1601 if they appear. In most cases this works just fine because the vast majority of business data is from the past several decades. But when you FPE-encrypt a date and get an older date, this can actually cause trouble.

Yet another example of why IT is almost always harder than you first think it's going to be.

Leave a Reply

Your email address will not be published. Required fields are marked *