2

I just converted to PostgreSQL from MySQL. In PostgreSQL my field (jobInfo) is a blob in (bytea) format. 

SELECT folderName, jobInfo
FROM jobs

In mySQL, that field came in as a string that could be unserialized. However, when I read the data in from postgres, it comes in looking like this:

\x613a31353a7b733a31373a2266696c65466f726d617456657273696f6e223b733a333a22342e30223b733a373a226a6f62 [.....]

Is there a way, in PHP or postgres, to decode that back to the serialized string of text characters?

UPDATE Per request, here is the code used to initially create the mySQL Blob field:

$theJobInfo = serialize($theJobInfo);

And SQL query:

UPDATE `jobs` SET `jobInfo` = theJobInfo
WHERE `folderName` = 'myFolderName'
Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
VikR
  • 4,818
  • 8
  • 51
  • 96
  • If that bytea field represents a PHP Object, how do you know it will make sense as a string of characters? – Evan Carroll Dec 28 '16 at 23:41
  • Show the code you use to set the field in mysql/php originally. – Evan Carroll Dec 28 '16 at 23:41
  • Because it's serialized. I have saved and retrieved this data thousands of times from mySQL, so for that reason I do believe it makes sense as a string of characters. I have updated the original post with the PHP/SQL code per your request. – VikR Dec 29 '16 at 00:01
  • Paste the schema for jobs... `\d jobs` in psql. – Evan Carroll Dec 29 '16 at 00:04

2 Answers2

2

You can easily convert to and from bytea and text, like this

SELECT convert_from('foo'::bytea, 'UTF8');

However, I don't think that's relevant.

You're using the serailize() function of php

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.

That function returns binary data. And likewise, unserialize() expects binary data. If you don't want to use binary data in the php, you can make jobs.jobinfo a jsonb type and send the result of json_encode() to it, and then json_decode() from it..

Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
  • I *do* want to use binary data. That's why I'm saving it to a Blob field in mySQL. – VikR Dec 29 '16 at 00:16
  • The question is, once I get it into a postgres ByteA field, how do I retrieve it as a serialized string that I can then unserialize? – VikR Dec 29 '16 at 00:16
  • You don't retrieve it as as a text string. You retrieve it as a "binary string", or "scalar" if php wasn't dumb af. Then you feed the scalar (with its binary contents) to `unserialize()`. – Evan Carroll Dec 29 '16 at 00:18
  • json has difficulty with PHP objects. It's possible to json encode PHP objects via JsonSerializable, but decoding them back to the original objects (not stdClass objects) is slow. That's why I'm seeking to find out if it's possible to use the serialized PHP objects. – VikR Dec 29 '16 at 00:19
  • Our comments crossed. – VikR Dec 29 '16 at 00:19
  • Okay, very good. What is the correct way to retrieve it from ByteA as a binary string or scalar? Is it `SELECT convert_from('foo'::bytea, 'UTF8');` as you posted a few comments ago? – VikR Dec 29 '16 at 00:20
  • No, just `var row = SELECT jobInfo FROM jobs`. And, then just `unserialize(row[jobInfo])` or whatever. – Evan Carroll Dec 29 '16 at 00:22
  • As I noted in the original post, the contents of the ByteA field are not in string format. They are `"\x613a31353a7b733a31373a2266696c65466f726d617456657273696f6e223b733a333a22342e30223b733a373a226a6f624e616d65223b733a34313a224e4743462d4356412d50726f6d6f5669642d"`[.....]; Running unserialize on this returns false or 0. This question is the basis of my original post. – VikR Dec 29 '16 at 00:51
  • I have no idea what's going on. All I know is you wanting to turn binary data that unserialize expects into a string of text (which is impossible) is not going to solve the problem. Perhaps you need to paste the schema, or provide a test case to produce the issue. – Evan Carroll Dec 29 '16 at 00:55
  • In mySQL that binary blob data comes back to PHP as a text string, including null characters where necessary for PHP objects, that can be unserialized. So, it is very doable. Not impossible at all. :) – VikR Dec 29 '16 at 01:04
  • It **has** to be bytea. That's the end of the story. Learn to use it. Don't know how to help you with what's given. http://stackoverflow.com/a/20961819/124486 – Evan Carroll Dec 30 '16 at 21:36
  • Solved it yesterday. Will post solution here as soon as work permits. – VikR Dec 30 '16 at 22:57
0

This code retrieved the serialized string from the contents of the postgres ByteA field:

$serializedString = pg_unescape_bytea($contentsOfPostGresByteAField);

Thanks to Sergey Pashkov for the tip!

VikR
  • 4,818
  • 8
  • 51
  • 96