serialized object in db

[pkirill99]

hi all

system php5.6 pgsql9+ yii2.0.6

I have simple model

class Table
    extends \yii\db\ActiveRecord
{
    .........
    /**
     * @inheritdoc
     */
    public function rules() {
        return [
                ........
            [['object'], 'string'],
            .......
        ];
    }
}

migrations:

$this->createTable('{{%table}}', [
            'id'             => $this->bigPrimaryKey(),
            ...
            'object'         => $this->text(),
]);

and component

class Manager
    extends Component
{
    public $a1 = null;
    public $a2 = 'default_manager';
    public $a3 = null;
    public $a4 = 0;
    public $a5 = null;

    public function changeStatus($status) {
        return true;
    }
}

I doing next action

$manager = Yii::createObject([ ..........  ]);
$model = new Table();
.....
$model->object = serialize($manager);
$model->save(); 

and ..... $object = unserialize($model->object); trying unserialize data and have

PHP Notice – yii\base\ErrorException unserialize(): Error at offset 185 of 189 bytes

because data in $model->object is corrupt

if I use base64 -

$model->object = base64_encode(serialize($manager));
$model->save(); 
$object =  unserialize(base64_decode($model->object));

no problem

and objects in yii\rbac\DbManager - serialized and unserialised wo problem.... but DbManager not use AR model for insert data in db

[SilverFire]

Try to use blob type for DB field

[SilverFire]

As far as I see, the problem does not relate Yii, but PHP and DBMS. I'm closing it for now. Feel free to post your thoughts here

[pkirill99]

yii\rbac\DbManager used "text" type in DB also, not a blob

[SilverFire]

Is it related to https://github.com/yiisoft/yii2/issues/10176?

[pkirill99]

hm ... in my example DbManager is work. I related to DbManager as example. But the bug is similar. in db I see trimmed data

O:40:"app\models\.....\....\Table":4:{s:4:"name";s:6:"author";s:53:"

[klimov-paul]

Most likely field max value size if not enough to contain all the data.

[pkirill99]

no, "text"-type of field in unlimited, and if i use base64 - lenght is even more

[cebe]

text is not unlimited, it is limited to 65536 bytes, see https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html

[klimov-paul]

So it is the same as #10176

[klimov-paul]

@cebe, we are talking about PostgreSQL here.

[cebe]

oops, sorry :)

[klimov-paul]

Can you catch the serialized string before it is saved and try to run INSERT query with it manually? Does it saves data into the text column correctly?

[klimov-paul]

If direct saving of the string runs correctly, try to enable Connection::emulatePrepare. May be PDO parameter binding causes this trouble.

[pkirill99]

i have test, i put string data in "text" field and put data in JSON in one model, in one transaction

see difference: in text O:21:"common\models\Manager":7:s:7:"account";s:1:"1";s:4:"name";s:15:"default_manager";s:6:"amount";d:369;s:8:"currency";i:0;s:6:"target";a:1:{s:2:"id";i:21;}s:27:"

and in JSON: {"manager":"O:21:\"common\\models\\Manager\":7:{s:7:\"account\";s:1:\"1\";s:4:\"name\";s:15:\"default_manager\";s:6:\"amount\";d:369;s:8:\"currency\";i:0;s:6:\"target\";a:1:{s:2:\"id\";i:21;}s:27:\"\u0000yii\\base\\Component\u0000_events\";a:0:{}s:30:\"\u0000yii\\base\\Component\u0000_behaviors\";N;}"}

and see .... \u0000 - symbol - NULL in unicode .

[klimov-paul]

So PostgreSQL itself rejects the string? If you try to save string with \u0000 symbol string saving ends on it?

[pkirill99]

no .... i try save sdfdsfsfsa\u0000dfsafdsafsfsa - and PG saved

NULL is not terminating in model

[pkirill99]

and from where NULL undertook?

[klimov-paul]

Can you retrieve orrginal text of serialized data? And try save it without Yii, PDO and even PHP being involved?

Post the actual trouble-making string here.

[pkirill99]

string(241) "O:21:"common\models\Manager":7:{s:7:"account";s:1:"1";s:4:"name";s:15:"default_manager";s:6:"amount";d:492;s:8:"currency";i:0;s:6:"target";a:1:{s:2:"id";i:22;}s:27:"yii\base\Component_events";a:0:{}s:30:"yii\base\Component_behaviors";N;}"

[pkirill99]

but NULL is not show, and string size 237 wo NULL

[klimov-paul]

So, does PostgreSQL trims this string while saving without PHP envolved? Can you try it at console client or "phppgadmin"?

[pkirill99]

in C/C++ NULL is the end of string

in Component yii\base\Component _events yii\base\Component _behaviors private fields

in https://github.com/yiisoft/yii2/issues/10176 - same problem (private fields)

in phppgadmin string data is saved to db, but no unserialised, maybe NULL is marker of private field, and from JSON with terminating NULL - unserialized wo problem

[kidol]

http://php.net/manual/en/function.serialize.php

Note: Object's private members have the class name prepended to the member name; protected members have a '*' prepended to the member name. These prepended values have null bytes on either side.

Maybe DbManager should use a binary column type to store this data, or store as json instead.

[pkirill99]

DbManaget use text also

Rule extends Object and have not a private fields

and yes ....

These prepended values have null bytes on either side.

utf8_encode doesn't help

[klimov-paul]

Code to reproduce:

$model = new Item(); // refer to table with 'description' column of type 'text'
$model->description = 'begin ' . "\x00" . ' end';
echo $model->canonicalDescription;
$model->save(false);

[klimov-paul]

Same problem with varying(255) column type

[klimov-paul]

Relates: http://stackoverflow.com/questions/11322624/why-psycopg2-can-not-insert-string-like-x00

[kidol]

@pkirill99

Rule extends Object and have not a private fields

But in related issue someone used custom rule with private property.

Re your issue, what's the problem now? Just change column type to some binary format. Nothing Yii can do when you manually try to store NULL bytes in text column.

[pkirill99]

it is not a true solution, it is a crutch

if NULL is a normal part of string in PHP - it means has to work also it is a problem of a framework

[kidol]

Then what should Yii do if NULL byte detected in string?

[pkirill99]

Model must should terminating for save in DB (PG and any with same architecture) and unterminating for external code

otherwise it is necessary to do handlers of NULL everywhere

[kidol]

You mean escaping the NULL bytes in some way and then unescaping when retrieving? I don't think that's possible. Even if possible, it would change string size and add magic (data now different in db).

PHP manual even says:

Note that this is a binary string which may include null bytes, and needs to be stored and handled as such. For example, serialize() output should generally be stored in a BLOB field in a database, rather than a CHAR or TEXT field.

>>>binary<<< string

[SamMousa]

Could it be this? http://blog.rwky.net/2011/01/php-pgsql-pdo-and-null-bytes.html

[SDKiller]

@SamMousa

Yes, you are right. This issue was closed as 'not a bug':
https://bugs.php.net/bug.php?id=53756

Serialized content of private/protected properties contain \0 inside the generated string. This causes truncation, to avoid it you need to change the column type to binary and write data as a binary string.

[SilverFire]

Thank you for digging in. I'm closing the issue since it's not Yii2 - related

[SDKiller]

@SilverFire

IMO, it is Yii2-related - since framework proposes text type in migrations and thus creates prerequsites for possible issues.

For example in:

https://github.com/yiisoft/yii2/blob/master/framework/rbac/migrations/schema-pgsql.sql#L20

as it is supposed to save serialized object in \yii\rbac\DbManager::addRule - the issue may occur (or may not - depending on specific object).

From the other side, Yii2 does not provide migrations for DbSession, but in phpdoc for \yii\web\DbSession::$sessionTable it is exactly stated that data column should be of BLOB type.

Upd: I mean - probably separate issue should be created about column types in bundled migrations for serialized data structures.

[SamMousa]

@SDKiller how does the framework suggest a type? SchemaBuilderTrait also has ->binary()..

[pkirill99]

no .... this problem is not in migrations and not in field type

"text" === "text" "text" != "binary"