Back

Inserting MySQL data with AES_ENCRYPT


I have a table that needs to store confidential data in two BLOB columns. The overall SQL statement needs to look like this:

INSERT INTO mytable (user_id, colA, colB) 
VALUES ('123', AES_ENCRYPT('value1', 'secretkey'), AES_ENCRYPT('value1', 'secretkey'));

I'd obviously like to use Eloquent's save() functionality, but it looks easier to do with the DB driver. From the documentation, it needs to be:

DB::table('mytable')->insert(
    array('user_id'=>'123', 
'colA' => DB::raw("AES_ENCRYPT('value1', 'secretkey')"), 
'colB' => DB::raw("AES_ENCRYPT('value1', 'secretkey')"))
);

Is there a better way to do this, or a native way to do with Eloquent (or the saving() event?) It would be lovely to have AES in models automatically, e.g. a function called $model->secure_save().

divdax replied 3 years ago

Use Laravels Crypt::encrypt/decrypt with Eloquents setColAttribute/getColAttribute? :)

azcoppen replied 3 years ago

The above code works successfully, but it's a pain to implement. That was going to be my next question - what's the best way to transparently use MySQL encryption with Eloquent? Can't find any documentation on those 2 methods?

datoneperson replied 2 years ago

I'm looking for this as well.

rapocalypse replied 2 years ago

Using Acessors and Mutators don't help you? I found it as the easy way to handle data modification when saving and reading from DB.

Ra1da35ma replied 3 months ago

Its 2017 !

Is there any better way to do this yet ??

I'm in real need of this.

jdubwelch replied 2 months ago

I just used an accessor:

# Controller
$model->fill([ 'password' => '12345' ]);

# Model
public function setPasswordAttribute($value)
{
    $this->attributes['password'] = DB::raw("AES_ENCRYPT('$value', 'some-key')");
}

Sign in to participate in this thread!



We'd like to thank these amazing companies for supporting us