1

I have stored encrypted values (like people names) on two columns: id, names

These plain text name values were encrypted using MCRYPT_RIJNDAEL_128 (AES-compliant) and MCRYPT_MODE_CBC then they were INSERTED one by one into a mySQl table named myTable.

If I encrypt "Bob" two, or three times or more, the encrypted string will always become different.

But now that I have +5000 rows; how do I find (SELECT) "Bob" on this table ?

user3162468
  • 425
  • 6
  • 14

2 Answers2

1

If you encrypt values, but then were able to select them by their unencrypted original value, the point of encryption would be lost. Never encrypt non-sensitive values, it's a loss of performance with no functional improvement.

If you insist on encrypting names, what you'll have to do is compare encrypted values. The Key and IV will be individual (I dare hope), which is why the outcome becomes different each time. If Bob logs in, use his Key and IV to encrypt his username, and then compare it with your database values.

Fredrik
  • 764
  • 1
  • 6
  • 22
  • Fredrik sorry I must temporarily disagree with all due respect. Will wait for some more answers before agreeing with you eheh :) Thanks anyway for sharing your point of view. Anyway I don't think that method of using individual key/iv values is secure anymore. – user3162468 May 23 '14 at 07:16
  • We all know development can be very subjective. :) As noted by Mark comparing hashes is an alternative, which ultimately is the same process with a few more bytes generated. Also, if you don't find individual Key/IVs secure, I'm not sure RIJNDAEL is the right algorithm for you. – Fredrik May 23 '14 at 07:30
1

That's a problem with storing encrypted data when you need to search on it....

The best solution is that you have two columns for each encrypted data value:

  • store encrypted "Bob" in one column so that you can decrypt subsequently when you need to recover the value
  • and also storing a strong hash of the original "Bob" value in another column, that you can then use to search against the hash of your search term. This second column can't be decrypted to return the original value, and if you salt it as well, you need to use the same salt for every value in that column
Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • Thanks for your answer, in this situation, the script hunting for "Bob" doesn't know the hash, that's the problem. Its ok probably it doesn't make sense to encrypt "Bob", but his mobile no. instead ;) – user3162468 May 23 '14 at 07:24
  • That's why you need to store a separate hashed column (with or without a known salt)..... then when somebody asks to search for "Bob" you salt and hash "Bob" using the same salt and hash algorithm, and search against the hashed column rather than the encrypted column..... the script hunting for "Bob" needs to generate a hash using the same method, that it can then use for the search – Mark Baker May 23 '14 at 07:34