css (1) jQuery (7) linux (38) mac (70) php (29) python (1) svn/git (9) trac (4) ubuntu (1) virtualbox (6) vista (2) windows (14)

Saturday, 15 January 2011

PHP PGSQL PDO and null bytes

Here's an interesting quirk of pgsql/php/pdo that took me hours to figure out.
I was trying to store a serialized php object in a pgsql text field via pdo. However whenever the object contained a private variable the string was truncated.
It turns out php serialized private/protected variables are surrounded by null bytes and pdo doesn't escape null bytes for pgsql so the null byte terminates the string.
However pdo mysql does escape the null byte which causes total confusion.
I've reported it as a bug with php however it maybe a problem with the underlaying pgsql driver, would be pgsqls problem to fix, I'll let the php people figure that out.
Lastly as a work around setting the field to hold the serialized data to a bytea type solves the issue setting the pdo data type to PDO::PARAM_LOB. Or you can use pgsql text fields and base64 encode the data.
(btw a subject containing nearly all acronyms, awesome!)