0

I have a follow-up question to another question, Encrypt and decrypt a string, posted by someone else.

I tried out the code posted by Brett, the one that has 190 up votes (and counting). What I noticed is that it is possible that with the same text, salt, and shared secret to get different encrypted results. The different encrypted results do, however, decrypt to the expected value.

My problem is that means that if I encrypt various database fields, then without the consistency, I cannot encrypt values given by the user, and use that in a select statement in a database. The following code would thus fail:

SELECT `Id` FROM mySqlTable WHERE `userid` = 'encryptedentry1' AND `userpw` = 'encryptedentry2';

How does one do queries on a database, where the columns are encrypted?

Community
  • 1
  • 1
Sarah Weinberger
  • 15,041
  • 25
  • 83
  • 130
  • You shouldn't store enctrypted passwords, store only a hash. And there is no need to encrypt the username and/or to use it in the where-clause here. Even better: use an available, open source authentication system. DIY is always the biggest risk here. – H H Mar 19 '14 at 20:04
  • A reasonable encryption function produces a different ciphertext every time because of a random initialization vector. Using a static IV with CBC mode is somewhat equivalent to ECB mode, which is not secure; perhaps you've seen the "ECB penguin"? http://bobnalice.wordpress.com/2009/01/28/friends-don%E2%80%99t-let-friends-use-ecb-mode-encryption/ – ntoskrnl Mar 19 '14 at 20:13
  • I am relatively a newbie to encryption and cryptography, so cannot comment, other than I did see IV in the code. I am totally okay with a different ciphertext every time. I discovered that the hard way. My question is how to then use that in a query? – Sarah Weinberger Mar 19 '14 at 20:18
  • I just found this website, which explains hash verses cryptography. http://www.unixwiz.net/techtips/iguide-crypto-hashes.html – Sarah Weinberger Mar 19 '14 at 20:34
  • I understand the comment about using a hash for passwords and maybe that is the best, but that does not answer my question as I would still like to be able to encrypt (two-way) certain fields and do a query on them. – Sarah Weinberger Mar 19 '14 at 20:35
  • I did some research and came up with this site that sort of explains "ECB penguin". The link is for all those, like me, who are not familiar with the term. http://bobnalice.wordpress.com/2009/01/28/friends-don%E2%80%99t-let-friends-use-ecb-mode-encryption/ – Sarah Weinberger Mar 19 '14 at 20:49
  • 1
    I think that I just thought of the answer, namely use both, a hash and a secure encryption. – Sarah Weinberger Mar 19 '14 at 20:56
  • 1
    Yes, for encryption repeatability is a weakness. For hashes it is a fundamental requirement. `Hash(username+password) == StoredUnPwHash` solves your login problem. – H H Mar 19 '14 at 21:08

0 Answers0