Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Auto AES_ENCRYPT() and AES_DECRYPT solution?
  • I will create many columns of encrypted by "AES_ENCRYPT()" in MySQL.
    I want to encrypt/decrypt automatically when find(), query() and save() methods called.

    likely this.
    class Model_Foo extends Model
    {
    protected static $_properties = array(
    'id',
    'not_encrypted_field',
    'encrypted_field',
    'created_at',
    'updated_at',
    );
    protected static $_encrypt_properties = array(
    'encrypted_field'
    );
    }

    I want encrypt/decrypt field(s) which set on $_encrypt_properties automatically.
    Is there solution about this?

    Use Observer? Extend Model class?

    I will use this if there is no solution.
    Model_Foo::query()->select(DB::expr('AES_DECRYPT(`encrypted_field`, key)'));


    But, I have no solution when save()...

    Please help me.
  • I don't see an immediate solution for this, other then to write a specific model/query combo for it that would deal with it.
  • @Harro Verton
    thank you for response.

    What is popular solution encrypt/decrypt fields.
    Use Crypt class and Observer class?
  • That is what I currently do.

    I have extended Observer_Typing for this purpose. I'll have a chat with my team to see if we can open source that extension.
  • I would completely be interested by this too.

    Thx Harro for your work.
  • @HarroVerton
    Thanks and I look forward to your work.

    I tried automaticaly save (by extended observer) and manualy get.

    //create model
    class Model_Sandbox extends \Orm\Model
    {
    protected static $_properties = array(
    'id',
    'val',
    'enc', //<- want encrypt field
    );

    protected static $_observers = array(
    'Observer_Crypt' => array(
    'events' => array('before_save'),
    ),
    );
    }
    //create fuel/app/classes/observer/crypt.php
    class Observer_Crypt extends \Orm\Observer
    {
    public function before_save(\Orm\Model $model)
    {
    if(isset($model->enc)){
    $model->enc = DB::expr(sprintf('AES_ENCRYPT(%s, "%s")', DB::escape($model->enc), 'key'));
    }
    }
    }
    //Save
    $model = Model_Sandbox::forge();
    $model->val = 'abcdefg';
    $model->enc = 'hijklmn';
    $model->save();
    check on mysql console, it was encrypted.
    //Get.  it returned array(Model_Sandbox, Model_Sandbox, ...).
    $result = DB::query('SELECT `id`, `val`, AES_DECRYPT(`enc`, "key") AS enc FROM `sandboxes` ORDER BY `id` DESC LIMIT 5')->as_object('Model_Sandbox')->execute()->as_array();
    This approach is not beautiful, but I can save and get.
  • I got bad solution.

    Put this code in Orm\Query::get() method in fuel/packages/orm/query.php directly.
    public function get()
    {
    // Get the columns
    $columns = $this->select();

    foreach($columns as $key => $column){
    if(!is_array($column) || count($column) != 2){
    continue;
    }
    $fields = explode('.', $column[0]);
    //'enc' is example. if use in product, create method on model for get encrypted field keys.
    if(count($fields) != 2 || $fields[1] != 'enc'){
    continue;
    }
    //'key' is example. If use in product, use Config::get(**);
    $column[0] = \DB::expr(sprintf('AES_DECRYPT(`' . implode('`.`', $fields) . '`, "%s")', 'key'));
    $columns[$key] = $column;
    }
    //Abbreviation below


    But this is so bad... not extends... directly put...
  • I got new solution. this execute one query after got model.

    class Observer_Crypt extends \Orm\Observer
    {
    public function after_load(Model_Soft $model)
    {
    //generate select
    $select = array();
    $crypt_properties = $model::get_crypt_properties();
    foreach($crypt_properties as $crypt_property){
    $expr = sprintf('AES_DECRYPT(`%s`, "%s")', $crypt_property, Config::get('db.crypt_key'));
    $select[] = array(DB::expr($expr), $crypt_property);
    }

    //when have crypted property(ies)
    if(count($select) > 0){
    //execute query and get row
    $query = call_user_func_array('DB::select', $select);
    $row = $query->from($model->table())->where(array('id' => $model->id))->execute()->current();
    //set result to model
    foreach($row as $field_name => $decrypted_value){
    $model->{$field_name} = $decrypted_value;
    }
    }
    }
    }

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion