databases: do you need to understand the security flaw?

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.