Hello.

Tell me, please, how is it recommended to store confidential data like VARCHAR in MySQL DBMS?

There is information about people (name, address, mobile phone number, etc.). It is necessary to protect this data while maintaining the ability to search by LIKE. What encryption methods exist for this (preferably with a PHP implementation)?

  • @undestroyer, you will not be able to search for like after encryption, you will have to decrypt every line. - etki
  • @Etki, the only acceptable methods found an offset by key and dictionary replacement, but in our time it is not serious at all - atom-22
  • one
    @ atom-22, you are not wasting your time. Even if you encrypt data in MySQL with a super-durable algorithm with the ability to search by LIKE, but leave a hole in the PHP application that allows you to make an exploit - all your work will go on smarting. - Dmitriy Simushev

1 answer 1

Alternatively, you can use the MySQL-symmetric encryption functions, for example, AES_ENCRYPT() and AES_DECRYPT() . It’s not a 128-bit key, it’s still a security with the possibility of subsequent data decryption

 INSERT INTO tbl VALUES ( NULL, AES_ENCRYPT('name', UNHEX('F3229A0B371ED2D9441B830D21A390C3')), MD5('password') ); SELECT AES_ENCRYPT(name, UNHEX('F3229A0B371ED2D9441B830D21A390C3')) AS name FROM tbl WHERE id = 1

The password can be immediately encrypted irreversibly and hashes be compared during authentication.

  • Does LIKE on fields encrypted using AES work?) - Dmitriy Simushev
  • Unfortunately, no, security measures almost always lead to a decrease in convenience. Alternatively, you can keep the decrypted data in temporary tables. Unencrypted data at least will not fall into the dump. Unfortunately, MySQL is not the best database for safe information storage. - cheops
  • So your answer is not true: Необходимо защитить эти данные с сохранением возможности поиска по LIKE - Dmitriy Simushev