SQL: Obfuscation is not Encryption

When I’m working in client sites, I get really concerned when I see personal data not being handled or protected appropriately.  And one of the biggest sins that I see is where developers have pretended to be encrypting data, but they really aren’t encrypting it.

I’m sure that looks good for a manager but please, just don’t do this !

When I look at the table definition shown in the main image above, my eye is drawn to the column called EncryptedTFN. In Australia, we refer to our tax numbers as TFNs. They are 11 digits long for most people and should never be stored in plain text in a database. The column should be encrypted.

At first glance, you might think it is encrypted, based on the column name.  But I know immediately, without looking any further, that this column isn’t encrypted.

Length

If the column was encrypted, it would be much longer than 12 characters long. Real encryption couldn’t afford to have an 11 digit number encoded into a 12 character field. It would be just far too easy to reverse engineer.

Encryption algorithms return much longer values. Often they’ll also use “salt” to ensure that encrypted values aren’t always the same. That makes them even longer.

Data Type

If the column was encrypted, it would likely be a varbinary data type, not a character string. It is possible to encode a binary value as a character string, but if you did that, it would be so much longer again.

So what is this?

So what’s happened here is that the developers have come up with their own “encryption algorithm”.

Never ever ever do this!

All they’ve done is obfuscate the value i.e. make it harder to read. But for anyone comfortable with how encoding works, this would be trivial to “decrypt”.

If you need to protect data, you need to use encryption. Algorithms for doing this are tricky to write but the hard work has already been done for you. SQL Server includes great options for using real encryption and hashing algorithms.

Don’t be the one who gets blamed when this goes seriously wrong.

If you’re not comfortable with using encryption, there’s no time like the present to learn about it. We have a great online encryption course that covers everything from understanding how encryption works, certificates, keys, digital signatures on stored procs, transparent database encryption (TDE), extensible key management (EKM), Always Encrypted (with/without secure enclaves) and more.

Why not be the one in your team that knows how this works? It’s never been more important. You’ll find it here:

https://training.sqldownunder.com/p/sqlserver-encryption

2020-10-27