How is data Encrypted and Decrypted from a database with PHP Sodium

Question

I'm a PHP web developer who has been trying to learn and understand how to encrypt and secure the data stored on a database with PHP Sodium.

In terms of security knowledge I am very new to the area besides applying basic password_hash / password_verify with bycrypt for passwords and use PDO actively when handling data, otherwise I have little to no understanding of how to encrypt / decrypt e.g. messages, files or other sensitive information.

What I'm trying to understand

• How to encrypt and store data with PHP Sodium to a database. "do unique keys need to be stored with each piece of data or each row of data?"
• What should and shouldn't be encrypted. "only sensitive data? or all data?"
• How to search for encrypted database data "by user search or by website/application request".
• How to Decrypt data stored on a database with PHP sodium.

Example

If an article named "StackExchange" is encrypted and then stored in the database as e.g. "TO+/ve+/vUTvv73v...".

How is it possible to search/load that encrypted information and return it in a plainText format either by viewing a public page or accessing it with an authorized user account?

---

Objectives

• To learn the basics / find out where to look for resources "in relation to the above"
• Understanding how PHP Sodium works with encryption & decryption for databases "mainly using mysql / postgresql"
• Decrypting encrypted data were appropriate.

Answer 1

If you have information that is encrypted, you can not search inside of it without decrypting it first.

If the information that you're using to search for something is not sensitive, such as a user ID, message ID, or otherwise only tangentially related to your sensitive information, then use that information as an index for your encrypted data. A popular choice is an auto-incrementing primary key.

If the information that you're using for the basis of your search is sensitive, then your index should be implemented through a cryptographic hash function. Keep in mind that, while hash collisions are rare, the fact that unrelated plaintexts will have hashes that collide is an important feature of cryptographic hash functions. After you've found candidate row, you need to verify it.

You're definitely not going to be able to do any full-text searches of the encrypted data without decrypting every row. Construct your indexes wisely.

Answer 2

Generally speaking you want to avoid trying to implement any of this yourself. Database encryption is a tricky topic already, and searchable database encryption is very difficult to design securely.

One of the reasons this is hard, for example, is the small plaintext space that you often get in your application. To borrow an example from Scott Arciszewski, imagine you have a database of patients in a hospital that contains their HIV status. This is a highly sensitive piece of information, so naturally you want to encrypt it. But you also need to be able to search by that column, in order to perform queries that show only HIV-positive patients. You might go about this by encrypting the value in a deterministic manner, then encrypting the value you want to search for and returning the rows that match that value. The problem is that for fields like this, where the plaintext space is very small (just true or false) then an attacker can discover the HIV status of every patient just by guessing which encrypted value is true, and which is false, which is trivial because fewer people will be positive than negative.

This is just one of the issues with searchable encryption, even before we get into things like indexing and performance with randomised encryption.

I strongly recommend looking at CipherSweet, which is the definitive library for doing such things in PHP. The blog post linked goes through most of the details of what you need to do to get it up and running, and what security considerations you need to make in your database design. There's also another blog post which goes into the design considerations that went into the library, and how the encryption works under the hood.