Orm: DDC-3241: object type fails to save serialized class to postgresql

[doctrinebot]

Jira issue originally created by user renoreckling:

Doctrine 2 fails to properly store data from serialize() into postgresql.
This happens because the postgresql pdo driver truncates text on NULL bytes when escaping values. This leads to truncated serialized objects being inserted into the database.

A ugly but working workaround for us is to call json_encode(serialize()) when saving to the database and unserialize(json_decode()) when reading the value back because json_encode properly serializes the NULL bytes of the serialize() output to readable text.

This is pretty ugly though and it would help if doctrine would provide a minimal encoding/decoding function for postgresql that converts all NULL bytes to something else to not break the object type on postgresql.

[doctrinebot]

• relates to DBAL-964: [GH-653] Update docs to include warning when using object type with PostgreSQL

[doctrinebot]

Comment created by @ocramius:

I'm fairly sure that we don't want to invent our own serialization format. Instead, base64_encode could work.

Due to BC compat, we can't introduce it for 2.x, so it would have to be a completely new type.

[doctrinebot]

Comment created by renoreckling:

Agreed, we'll just go with base64_encode for now.
Maybe someone should update the documentation that using the object type on postgresql is _not_ working as well as that using binary strings containing a NULL byte in any varchar/text column on postgresql is not working either.

[doctrinebot]

Comment created by @ocramius:

[~renoreckling] just open a pull request against the doctrine/doctrine2 repository

[doctrinebot]

Comment created by renoreckling:

[~ocramius] Here you go: https://github.com/doctrine/dbal/pull/653
http://www.doctrine-project.org/jira/browse/DBAL-964

Thanks for the reply.

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-653] was closed:
https://github.com/doctrine/dbal/pull/653

[doctrinebot]

Comment created by @ocramius:

Merged DBAL-964, but the issue persists here.

[root-talis]

I've just ran into this bug. Any hope this will be fixed? Right now a workaround is to do base64_encode(serialize($field)), as suggested above, but it doesn't look like a good way to go.

[zeromodule]

I am also interested in solving this problem.
This bug is 3 years old.
Does it make sense to wait for a fix in the near future?

[Ocramius]

Requires:

1. a test case
2. a fix

Don't expect core maintainers to fix this for you, sorry.

[NeoFusion]

My solution: create new mapping type based on ObjectType

<?php

namespace AppBundle\DBAL\Types;

use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Types\ObjectType;

class ByteObjectType extends ObjectType
{
    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        return $platform->getBlobTypeDeclarationSQL($fieldDeclaration);
    }

    public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        return pg_escape_bytea(serialize($value));
    }

    public function getName()
    {
        return 'byte_object';
    }
}

```yaml
doctrine:
dbal:
types:
byte_object: AppBundleDBALTypesByteObjectType

```php
/**
 * @ORM\Column(type="byte_object")
 */

[zlikavac32]

@NeoFusion I stumbled upon the same issue and solved it similarly. But instead of convertToDatabaseValue(), I used

public function getBindingType(): int
{
    return ParameterType::LARGE_OBJECT;
}