I am creating an encrypted database.
Let's say I store "John," which will be encrypted and stored as "Yoky."
John | Yoky
Now I store "Johnny", which would be encrypted and stored as "Koaddy"
John | Yoky
Johnny | Koaddy
Now with the previous storage I will not get any type of regular expression search functionality. If I wanted to search for "Jo%" it would not work.
But what happens if I store the values after breaking them? as
Jo | Yoky , Koaddy
Joh | Yoky , Koaddy
John | Yoky , Koaddy
Johnn | Koaddy
Johnny | Koaddy
Here the regular expression searches will work "Jo%", "Joh%" both will give Yoky and Koaddy, which is what I want.
I can see the obvious security flaw above that anyone can map Jo, Joh.
So I have decided to store the encryption of these.
AES I will encrypt my heels and keep them.
qkjklewr!j== | Yoky , Koaddy
klkadsopos== | Yoky , Koaddy
oensd%21op== | Yoky , Koaddy
kaknvp23b02== | Koaddy
kashdi2094j== | Koaddy
When performing any type of search, say "Joh", I will first encrypt "Joh" and then I will perform the search, therefore, it will be assigned to the AES encrypted value of "Joh", that is, klkadsopos ==
Note: Both the column will use different keys and algorithms to protect the data.
Note: This storage will be encrypted with TDE.
I need to understand if I am missing something fundamental here.
