如何使用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.

Answer 1

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