Win a copy of Testing JavaScript Applications this week in the HTML Pages with CSS and JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Search an encrypted fields

 
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am writing an application that searches an encrypted fields by using aes-256-ecb
I have a table 'encTest' with an encrypted column. I would write a query that search by column name1.
Since 'name2' column will not be there in reality, the following SQL query will not work.

SELECT * from encTest where name2 = 'Sarah';

I would like to know if it is safe to use.

SELECT * from encTest where name1 = AES_ENCRYPT('Sarah', 'keyForDescyption');


--------------------------------------
id         name1           name2
--------------------------------------
1 *&$_@)(&^$s)@&    Jennifer
2   !@%#@%sw$#@fdc    John
3   <>:#}{(#%@!*%a     Sarah
4    ~_!@*^$t$7%&Dj      Sara
--------------------------------------

Is there any better method to solve this problem? Thank you for reading this.
 
Saloon Keeper
Posts: 12165
258
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No, that won't work. The point of encryption is that when you encrypt something twice, both times it will return a different ciphertext.

You have to decrypt every cell in the database and compare it to your search string to find the rows you're interested in. This will probably be very slow. You can do this if performance is not an issue, but if it is, then you probably should be using a DBMS that supports encryption natively.

Another approach you can take is store a thumbprint together with your encrypted value, and search for the thumbprint instead of the actual value.

idvaluethumbprintactual (not in database)
1af6eeb9d1072a4f7acc29d1f48708a6be3a2b2532b8861285e7e3a4f9fc26260bda0a01c76aae97ef15d6b792f3dc3cbb6609d611e9c563a54a1393fe685e31bJennifer
2478ba1b71ea13397fe5a442dc76f86f41277e266554dfdcaaf7e4d0d83e06367a8cfcd74832004951b4408cdb0a5dbcd8c7e52d43f7fe244bf720582e05241daJohn
3db8ac1c259eb89d4a131b253bacfca5f1e7ac0bd464da51b38b1c29be49fac567e8c729e4e4ecc320cb411c4d1419bf5fbad733212d4e9491b7630aaef0b8b1cSarah
4a52f27e7fae7440c1d3fbc94216983adbb45771fac817a72be75aa9819823de17e8c729e4e4ecc320cb411c4d1419bf5fbad733212d4e9491b7630aaef0b8b1cSarah

As you can see, the encrypted value for Sarah is different for both rows, but the thumbprint is the same. You get the thumbprint by hashing the actual value, and then you can compare it with the thumbprint in the database.

The problem with this approach is that your encrypted values might become vulnerable to rainbow table attacks.
 
Stephan van Hulst
Saloon Keeper
Posts: 12165
258
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Another consideration is that when you store the same thumbprint for two different rows with the same actual value, your encrypted values might also become vulnerable to a certain kind of chosen plaintext attack. When an attacker knows that two different encrypted values belong with the same plaintext, that gives them extra information that might result in them retrieving the original key, which would obviously give them access to ALL values.

I strongly advice you to look into databases that support encryption natively.
 
Sheriff
Posts: 7108
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
PostGreSQL is a free, open source DB with encryption.
 
Saloon Keeper
Posts: 22289
151
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You cannot pattern-match a string on an encrypted string. Only the simplest of schemes translated byte-for-byte. The industrial-grade encryption schemes rip the bits apart and splatter them all over the ciphertext, combining them with other bits. The bits are considered as terms in a complex binary polynomial expression. So only by decrypting first can you do the match.

The "thumbnail" approach is technically feasible, but it's a security risk. Any information that was sensitive enough to require encryption is probably not something you'd want stored in a less secure manner. First, because someone could simply harvest the secured information from the thumbnail, secondly because knowing even the smallest bit of what the encrypted data is makes it that much easier to crack the rest of the ciphertext.
 
Waylon Wolf
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I was thinking the similar techniques. I took a chance to see if there are any other way around.
It looks like there is not! Thank you for being supportive.
 
Consider Paul's rocket mass heater.
    Bookmark Topic Watch Topic
  • New Topic