How to store "encrypted" data in MySQL?

Question

I need a way to store encrypted data in such a way that I can still fulfill requests. Is it possible?

At least I need an encryption algorithm that always returns the same row for the same input, so I can find all the users with the name "John", encrypt this row and look for the encrypted result in the database. In PHP, mcrypt always returns different strings (I know that this is on purpose, to increase security).

Any ideas?

Answer 1

Depending on how you store this name, "John". If this is ONLY a thing in a certain area, you can do something like

SELECT ... FROM sometable WHERE cryptedfirstname = AES_ENCRYPT('John', $key) 

If “John” is part of a larger line (“John Doe” or “King John, Ruler of the Universe”), then you will have to decrypt the full field and match it

 SELECT ... FROM sometime WHERE INSTR(AES_DECRYPT(cryptedFULLame, $key), 'John') > 0 

Please note that I am embedding the decryption key in the request. A bad idea for a production system, but this is just an example.

You cannot do something like:

 ... WHERE INSTR(cryptedFULLname, AES_ENCRYPT('John', $key)) 

due to how AES and most other useful / decent cryptosystems work.

Answer 2

It seems that you understand this, but it should be emphasized that the encryption algorithm, which always produces the same encrypted text for a given plaintext, is broken. This leads to all kinds of attacks.

For example, an attacker with access to your database and application may select the value "John" for the field and force the application to store it in the database. He can then look at the encryption text for his record and identify any other records containing this encrypted text. He does not need to get a key for this.

An exception may be if you encrypt large "unpredictable" unique numbers, such as session identifiers or UUIDs. In this case, since simple texts are not repeated, and valid unencrypted texts cannot be predicted by the attacker, indistinguishability of the encrypted text is not required.

Any symmetric cipher used in ECB mode will call consistent ciphertext from plaintext, as well as use modes that take an initialization vector if you always use the same IV. This is usually not a good idea.