Is there a way to automatic apply the decryption and encryption on some columns on a table?-CodePudding

this is the scenario I'm trying to figure out.

I have a table named users having the following columns,

id
user_name
email      --encrypted
phone      --encrypted
password   --encrypted
created_at
updated_at

What I want to achieve is this, suppose if I ran DQL command that is SELECT so it should automatically decrypt the data in those columns like

SELECT email FROM users; // this should give the decrypted email value
SELECT * FROM users; // this should give the decrypted email, phone and password value

What I want to achieve is this, suppose if I ran DML command that is insert, update so it should automatically encrypt the data and then insert or update in those columns like

INSERT INTO users (user_name,email,phone,password) VALUES ('test','[email protected]','1234123412','password'); // this should encrypt email, password, phone before inserting automatically.

I could do this from Server side scripting only, but the thing is the application is already created and changing the whole code for the column names will be a very time consuming task.

Any help is appreciated.

CodePudding user response：

Your best bet is eloquent mutators/accessors Laravel and the default Laravel encrypt/decrypt helpers. Will automatically encrypt when saving and return your column unencrypted. the code below for email field for instance will automatically encrypt and decrypt the email column. biggest disadvantage is you can not search by any encrypted column. hope this helps

public function setEmailAttribute($value)
{

    $this->attributes['email'] = encrypt($value);
}

public function getEmailAttribute($value)
{
    if ($value !== null) {
        return decrypt($value);
    }
    return null;
}

CodePudding user response：

MySQL 5.7 and later support some options for encrypting tablespaces. Here's a good article introducing the options: https://dev.mysql.com/blog-archive/controlling-table-encryption-in-mysql-8-0/

But there is no automatic way to encrypt & decrypt individual columns. The closest thing would require you to write triggers to encrypt on INSERT & UPDATE, and a VIEW to decrypt as you read the data.

Other than that, you need to get to work refactoring the client code. (That's what I would do.)