Return to Question

Radically edit and re-format

Source Link

edit approved Dec 5, 2019 at 7:02

Need to understand the security flaw Security flaws of storing strings and some of its substrings using Microsoft SQL Server Transparent Data Encryption?

I am creating a database oftable with an encrypted value., say users:

Let us say I store "John" which would beSay John encrypted and stored as "Yoky"is U2FsdGVkX193AOGlBRE1RNScJRGN9vSB4erIljJwaKw=.

JohnUserId | YokyName
------ | --------------------------------------------
1      | U2FsdGVkX193AOGlBRE1RNScJRGN9vSB4erIljJwaKw=

Now I store "Johnny" whichJohnny would be encrypted and store as "Koaddy"U2FsdGVkX19lYDuvuBg3BzTnZro4J6zKoo9s/TLeiPU=.

JohnUserId | Name
------ | Yoky--------------------------------------------
1      | U2FsdGVkX193AOGlBRE1RNScJRGN9vSB4erIljJwaKw=
Johnny2      | KoaddyU2FsdGVkX19lYDuvuBg3BzTnZro4J6zKoo9s/TLeiPU=

Now with the above storage I will not get any kind of regex search functionality. IfHow could I wanted to search "Jo%" it will not work.for names beginning with Jo?

But what if I store the values after breaking them. aswas thinking of storing:

NameStub | UserId
-------- | ------
Jo       | 1
Jo   Yoky , Koaddy  | 2
Joh      | 1
Joh   Yoky , Koaddy | 2
John     | 1
John  Yoky , Koaddy | 2
Johnn   |  | Koaddy2
Johnny  |  | Koaddy2

Here the regexI can now do searches will work "Jo%"like 'Jo%',"Joh%" 'Joh%' and both will give Yoky and Koaddy, which is what I wantinclude both users.

I can see the obvious security flaw with the above that anyone can map out Jo,Johstubs like Jo or Joh.

So I have decided to store the encryption of these.

I will AES encrypt my stubs and store them.encrypted:

qkjklewr!j==  NameStub    |   YokyUserId
----------- ,| Koaddy------
klkadsopos== enc(Jo)     | 1
enc(Jo)  Yoky , Koaddy
oensd%21op== | 2
enc(Joh)    |  1
enc(Joh) Yoky , Koaddy
kaknvp23b02== | 2
enc(John)   |   Koaddy1
kashdi2094j==enc(John)   | 2
enc(Johnn)  | 2
enc(Johnny) | Koaddy2

While performing any type of search say, "Joh", I will first encrypt "Joh" then perform the search, therefore it will map towhere enc(x) is just the AES encrypted value of "Joh",ifor x.e,klkadsopos==

NoteWhen searching for : Both the columnJoh, I will use different keysencrypt Joh and algorithms to protect the datathen query for matching stubs and any associated users with names for that stub.

Note : This storage will be TDE encrypted.

Both of the encrypted columns will use different keys and algorithms to protect the data.

I will be using Microsoft SQL Server Transparent Data Encryption (TDE).

Need to understand the security flaw?

I am creating a database of encrypted value.

Let us say I store "John" which would be encrypted and stored as "Yoky".

John | Yoky

Now I store "Johnny" which would be encrypted and store as "Koaddy"

John   | Yoky

Johnny | Koaddy

Now with the above storage I will not get any kind of regex search functionality. If I wanted to search "Jo%" it will not work.

But what if I store the values after breaking them. as

Jo      |   Yoky , Koaddy
Joh     |   Yoky , Koaddy
John    |   Yoky , Koaddy
Johnn   |   Koaddy
Johnny  |   Koaddy

Here the regex 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 out Jo,Joh.

So I have decided to store the encryption of these.

I will AES encrypt my stubs and store them.

qkjklewr!j==      |   Yoky , Koaddy
klkadsopos==      |   Yoky , Koaddy
oensd%21op==      |   Yoky , Koaddy
kaknvp23b02==     |   Koaddy
kashdi2094j==     |   Koaddy

While performing any type of search say, "Joh", I will first encrypt "Joh" then perform the search, therefore it will map to the AES encrypted value of "Joh",i.e,klkadsopos==

Note : Both the column will use different keys and algorithms to protect the data.

Note : This storage will be TDE encrypted.

Security flaws of storing strings and some of its substrings using Microsoft SQL Server Transparent Data Encryption?

I am creating a database table with an encrypted value, say users:

Say John encrypted is U2FsdGVkX193AOGlBRE1RNScJRGN9vSB4erIljJwaKw=.

UserId | Name
------ | --------------------------------------------
1      | U2FsdGVkX193AOGlBRE1RNScJRGN9vSB4erIljJwaKw=

Johnny would be encrypted as U2FsdGVkX19lYDuvuBg3BzTnZro4J6zKoo9s/TLeiPU=.

UserId | Name
------ | --------------------------------------------
1      | U2FsdGVkX193AOGlBRE1RNScJRGN9vSB4erIljJwaKw=
2      | U2FsdGVkX19lYDuvuBg3BzTnZro4J6zKoo9s/TLeiPU=

How could I search for names beginning with Jo?

I was thinking of storing:

NameStub | UserId
-------- | ------
Jo       | 1
Jo       | 2
Joh      | 1
Joh      | 2
John     | 1
John     | 2
Johnn    | 2
Johnny   | 2

I can now do searches like 'Jo%', 'Joh%' and both will include both users.

I can see the obvious security flaw with the above that anyone can map out stubs like Jo or Joh.

So I have decided to store the stubs encrypted:

NameStub    | UserId
----------- | ------
enc(Jo)     | 1
enc(Jo)     | 2
enc(Joh)    | 1
enc(Joh)    | 2
enc(John)   | 1
enc(John)   | 2
enc(Johnn)  | 2
enc(Johnny) | 2

where enc(x) is just the encrypted value for x.

When searching for Joh, I will encrypt Joh and then query for matching stubs and any associated users with names for that stub.

Note:

Both of the encrypted columns will use different keys and algorithms to protect the data.

I will be using Microsoft SQL Server Transparent Data Encryption (TDE).

Source Link

asked Dec 3, 2019 at 2:15

Shreyash Sharma

Need to understand the security flaw?

I am creating a database of encrypted value.

Let us say I store "John" which would be encrypted and stored as "Yoky".

John | Yoky

Now I store "Johnny" which would be encrypted and store as "Koaddy"

John   | Yoky

Johnny | Koaddy

Now with the above storage I will not get any kind of regex search functionality. If I wanted to search "Jo%" it will not work.

But what if I store the values after breaking them. as

Jo      |   Yoky , Koaddy
Joh     |   Yoky , Koaddy
John    |   Yoky , Koaddy
Johnn   |   Koaddy
Johnny  |   Koaddy

Here the regex 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 out Jo,Joh.

So I have decided to store the encryption of these.

I will AES encrypt my stubs and store them.

qkjklewr!j==      |   Yoky , Koaddy
klkadsopos==      |   Yoky , Koaddy
oensd%21op==      |   Yoky , Koaddy
kaknvp23b02==     |   Koaddy
kashdi2094j==     |   Koaddy

While performing any type of search say, "Joh", I will first encrypt "Joh" then perform the search, therefore it will map to the AES encrypted value of "Joh",i.e,klkadsopos==

Note : Both the column will use different keys and algorithms to protect the data.

Note : This storage will be TDE encrypted.

I need to understand if I am missing something fundamental here.

databases