Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to encrypt a specific column in a MySQL table?

Tags:

I am experimenting with creating a simple message system (PHP) page that uses a MySQL table to store the entries. The rough outline of the columns I'll use in the table are:

msg_id (primary key, auto_increment)

user_id (foreign key pointing to the user who created the message)

time (a DATETIME entry to provide msg timestamps)

msg (a VARCHAR containing the msg)

accessable (just an int(1), 0 means no one except the user himself can read the msg, and 1 means others can read it)

What I'm wondering is, what's the best way to encrypt the msg field so prying eyes can't read it (let's say, by opening the mysql CLI or phpMyAdmin and just read the value stored in a row)?

If "accessable" is set to 0, then only the user him/herself should be able to read it (by accessing some PHP page), but if set to 1, everyone else should be able to read it as well. I don't know how to tackle this, so any help is very appreciated!

like image 554
BeeDog Avatar asked Nov 25 '10 10:11

BeeDog


People also ask

How do I encrypt a string in MySQL?

The MySQL AES_ENCRYPT function is used for encrypting a string using Advanced Encryption Standard (AES) algorithm. The MySQL AES_ENCRYPT function encodes the data with 128 bits key length but it can be extended up to 256 bits key length. It encrypts a string and returns a binary string.

Can we encrypt data in MySQL?

To enable encryption for the mysql system tablespace, specify the tablespace name and the ENCRYPTION option in an ALTER TABLESPACE statement. mysql> ALTER TABLESPACE mysql ENCRYPTION = 'Y'; To disable encryption for the mysql system tablespace, set ENCRYPTION = 'N' using an ALTER TABLESPACE statement.

How do I encrypt a table in MySQL?

To encrypt data in an InnoDB file-per-table tablespace, run ALTER TABLE tbl_name ENCRYPTION = 'Y' . To encrypt a general tablespace or the mysql tablespace, run ALTER TABLESPACE tablespace_name ENCRYPTION = 'Y' . Encryption support for general tablespaces was introduced in MySQL 8.0.


2 Answers

Look here for list of possible encryption functions:

http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html

You can create trigger for update and check there field accessable. Something like that:

CREATE TRIGGER crypt_trg BEFORE UPDATE ON table FOR EACH ROW BEGIN   IF new.accessable = 0 THEN     SET new.msg := ENCRYPT(new.msg, 'key');   ELSE     SET new.msg := DECRYPT(new.msg, 'key');   END IF; END; 

You also can update all existing records in you table with this query:

UPDATE table SET msg = IF(accessable = 0, ENCRYPT(msg, 'key'), DECRYPT(msg, 'key')); 

So you can select records for you PHP code:

SELECT msg_id, user_id, time, IF(accessable = 0, DECRYPT(msg, 'key'), msg) msg FROM table 

UPD. Also here was similar question:

MySQL encrypted columns

like image 65
rMX Avatar answered Oct 24 '22 09:10

rMX


You can also encrypt the data prior to the query to insert it, so that MySQL doesn't even know it's encrypted, and decrypt it on retrieval in the application. For that, you should store it in a varbinary or blob column though.

like image 45
Core Xii Avatar answered Oct 24 '22 10:10

Core Xii