1

I have an address table with human-readable columns like street, city, state, zip - and also columns with spatial data (the SQL Server geography type). Technically, this violates 3rd Normal form (i.e. since you can derive street, city, state from Geo) but we know the convenience reasons for doing this. I have custom encryption for the human-readable forms but I want to do math operations in the database against the geography columns - so this is not currently encrypted.

The problem: I effectively have plain-text and the corresponding cipher-text side-by-side. (i.e. I can go find out the street that a plain-text Geo coordinate corresponds to and now I know plain-text street and cipher-text street, somewhat compromising my encryption scheme).

I don't mind having the Geo exposed, but how can I hide/encrypt the link between Geo and human readable? I see that SQL Server does NOT support doing Transparent Data Encryption for columns of the geography type (which seems like it's exactly where you'd want it).

Stephen King
  • 201
  • 2
  • 12
Michael Arner
  • 11
  • 3
  • What is your "custom encryption" and why are you not using something standard like AES? – AndrolGenhald Apr 02 '18 at 18:27
  • Are you worried that the same plaintext always encrypts to the same ciphertext? Is that the problem? – Ben Apr 02 '18 at 18:28
  • @AndrolGenhald. I am using AES with some additional obfuscation. – Michael Arner Apr 02 '18 at 18:28
  • @Ben I got the impression that the worry was known plaintext. Michael, AES is safe against known plaintext attacks, but your obfuscation is pointless. – AndrolGenhald Apr 02 '18 at 18:29
  • @Ben. No I am worried that a Geo quantity that can be turned into plaintext is next to the actual plaintext. i.e. From Geographical Coordinates, I can get street address. So effectively, I have the encrypted street address and the plaintext street address side by side. – Michael Arner Apr 02 '18 at 18:30
  • @Androl. Ah! Can you point me towards a reference that "AES is safe against known plaintext?" I didn't realise that. – Michael Arner Apr 02 '18 at 18:31
  • @MichaelArner [These](https://crypto.stackexchange.com/q/1512) [links](https://security.stackexchange.com/q/138229) came up in my search, though I'm sure there's a better reference somewhere. – AndrolGenhald Apr 02 '18 at 18:33

0 Answers0