FuelPHP Forums

takyam

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.

Harro

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.

takyam

@Harro Verton
thank you for response.

What is popular solution encrypt/decrypt fields.
Use Crypt class and Observer class?

Harro

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.

Gonab

I would completely be interested by this too.

Thx Harro for your work.

takyam

@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.

takyam

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...

takyam

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;
			}
		}
	}
}

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion