Best way of encrypting text to store in mysql database

Question

I want to know the best way of storing text in a database and encrypting it so as to prevent others (admin) from reading it. I'm allowing users to write (up-to) paragraphs of plain text and then storing in a database. This text is then displayed back to the user in their account. This means that I will have to be able to decrypt the data once i've encrypt it and stored it in the database. (I have created the project using PHP)

Thanks

Read here: http://stackoverflow.com/questions/1289061/best-way-to-use-php-to-encrypt-and-decrypt — Christof, Apr 01 '11 at 13:28

score 2 · Answer 1 · answered Apr 01 '11 at 13:28

2

AES_ENCRYPT and AES_DECRYPT are easy ways to encrypt/decrypt strings without writing the code yourself, available in MySql 5 upwards.

Be aware that the output of AES_ENCRYPT is a binary string, which needs to be stored in columns of a binary data type (most likely the appropriate one would be BLOB) instead of text types such as TEXT or VARCHAR that you would normally use for text data.

The problem is that you are going to have to store the encryption key somewhere, and you somehow have to keep the admin from accessing it. I don't know if that will be possible (admin of what exactly?)

answered Apr 01 '11 at 13:28

Jon

428,835
81
738
806

How do you propose to store, pull, rotate, and otherwise manage encryption keys, in such a way that others can't read encrypted data? – yfeldblum Apr 01 '11 at 13:30
@Justice: I don't. And it would be impossible to make such a proposal without having much more detailed knowledge of the application in question. – Jon Apr 01 '11 at 13:31
@Justice - It depends on the requirements – Who do you mean by "others"? If its people with full access to the server, the only option is to use client-side (javascript) encryption. And even then they could modify the javascript code to not encrypt data, for example. – Joel L Apr 01 '11 at 13:33

score 2 · Answer 2 · answered Apr 01 '11 at 13:29

What you are looking for is MCrypt. Also if you are wanting the data to be truly secure you will need to use HTTPS for transport as once the PHP script has decrypted the cipher text (when the user is accessing the text) the plain text is sent out through the NIC of the server. So a crafty admin or attacker could just sniff the trafic on the interface and log the traffic.

score 2 · Answer 3 · answered Apr 01 '11 at 13:31

2

In fact, you can't prevent admin from viewing these texts as he'll be able to read encryption password as well and decrypt them.

answered Apr 01 '11 at 13:31

Your Common Sense

156,878
40
214
345

Really? Only if you store the password itself. If you only store the password hash the admin will not be able to decrypt the text. – Oliver A. Apr 01 '11 at 14:15
but we're talking about encryption here, not hashing – Garrett W. Mar 25 '22 at 17:05

score -1 · Answer 4 · answered Apr 01 '11 at 14:30

Use a save connection (https) so your admin can not get the password from the logs.
Use MCript to encrypt decrypt the data with the users password.
Decrypt the data with the users password.

There is however one BIG drawback:

You will have to store the users password in cleartext in the session, so you MUST take care that session data is not stored in logs, the database, etc...

If your admin has access to the php code it is a matter of seconds to hack this.

The only case where this will work is if your Admin can access the database and the backend BUT NOT the code.

Best way of encrypting text to store in mysql database

4 Answers4