how to store serialized object with namespace in database using pdo php

Question

When i try to store serialized object with namespace i cant do that beacuse i got error unterminated quoted string at or near "'O:22:"protect\classes\Router". Code:

$router = new protect\classes\Router();
$tmp = serialize($router);

$dsn = 'pgsql:dbname=system;host=127.0.0.1';
$user = 'postgres';
$password = 'mypassword';
$pdo = new PDO($dsn, $user, $password, $options);
$pdo->exec('SET search_path = temporary');
$pdo->query("SELECT replace_value('protect\classes\Router','$tmp','serialized_classes')");  // this is my function it`s work fine

If i use php function addslashes before query executed for exampe

  $tmp = addslashes(serialize($router));

the query is successful executed. Unfortunately serialized object with additional slashes is disorder.

I will grateful for help with this topic.

You seem to have forgotten to post the source code of your `replace_value` function. — Craig Ringer, Jan 07 '14 at 00:16

Craig Ringer · Accepted Answer · 2014-01-07T01:11:23.733

There are several things wrong here, but the biggest is that you aren't using query parameters.

Don't use addslashes. If you find yourself using that, you should think "oops, I need to go fix the query so I use parameters instead".

In this case, you should be writing something like:

$sth = $pdo->prepare('SELECT replace_value(?, ?, ?)');
$sth->execute(array('protect\classes\Router', $tmp, 'serialized_classes'));

You haven't mentioned what the data type of the argument you pass the serialized data to is. The above will only work if it is text or varchar or similar.

If it's bytea like it should be for serialized object data, you must tell PHP that the parameter is a binary field:

$sth = $pdo->prepare('SELECT replace_value(:router, :serialbytes, :mode)');
$sth->bindParam(':router', 'protect\classes\Router');
$sth->bindParam(':mode', 'serialized_classes');
$sth->bindParam(':serialbytes', $tmp, PDO::PARAM_LOB);
$sth->execute();

Note the use of PDO::PARAM_LOB to tlel PDO that $tmp contains binary data to be passed to PostgreSQL as bytea.

(It's fine to put constants like 'protect\classes\Router' directly into your queries, btw, so long as you split them out into params if they ever become variables. I mostly separated them because I find it more readable in a query like this.)

first thing when i use code with bindParam i got error: The arguments passed to bindParam have to be reference, so i put instead string a varaible. I got effect but i got next error. My serialized object is not right with UTF-8 encoding. my serialbytes columns is type text. So i change to bytea now working fine, but in row i have just something like "". I`m not sure if i get this () from database i can use my serialized object? So i try too use you first example with preapare and execute(array(...)) all working fine but my serialized object in db i cut at half. — ZiupeX, Jan 07 '14 at 21:10
@ZiupeX It's probably cut in half because it contains nulls (zero bytes, `'\0'`. If so, you *cannot* store it in a `text` column, you *must* use `bytea`. As for how to read `bytea` from the DB - you `SELECT` it like any other column. If you are having problems with that, *post a new question* tagged `php postgresql` with appropriate details, including storage and retrieval code, SQL, etc. — Craig Ringer, Jan 07 '14 at 22:48

score 2 · Answer 2 · answered May 13 '16 at 12:38

I was facing same problem - and found out here that this is the zer-byte problems. After some development I created an adapter for Zend Serializer (ZF2) that overloads serialize/unserialize methods in a way that it will be possible to store these serialized values in PostgreSQL.

The code you can find below - hopefully it will somehow useful :)

use Zend\Serializer\Adapter\PhpSerialize;

/**
 *
 * Class overloads serialization methods so serialized objects will be PostgreSQL safe
 * For further information on safe/unsafe objects:
 * http://php.net/manual/en/function.serialize.php#96504
 *
 */
class PostgresSerialize extends PhpSerialize {

    const DEFAULT_SAFE_NULLBYTE_REPLACEMENT = "~~NULL_BYTE~~";

    protected static $serializedFalse = null;

    public function serialize($value) {
        $serializedString = parent::serialize($value);
        if (strpos($this->options['safe_nullbyte_replacement'], $serializedString))
            throw new \RuntimeException('Cannot perform safe nullbyte replace operation since safe_nullbyte_replacement="' .  $this->options['safe_nullbyte_replacement'] . '"value already exists in the serialized string', \Zend\Log\Logger::ERR);
        if ($this->options['safe_nullbyte_replacement'] == null)
            $this->options['safe_nullbyte_replacement'] = self::DEFAULT_SAFE_NULLBYTE_REPLACEMENT;
        return str_replace("\0", $this->options['safe_nullbyte_replacement'], $serializedString);
    }

    public function unserialize($serialized) {
        if ($this->options['safe_nullbyte_replacement'] == null)
            $this->options['safe_nullbyte_replacement'] = self::DEFAULT_SAFE_NULLBYTE_REPLACEMENT;
        $serializedString = str_replace($this->options['safe_nullbyte_replacement'], "\0", $serialized);
        return parent::unserialize($serializedString);
    }

}

how to store serialized object with namespace in database using pdo php

2 Answers2

Linked