jlandercy jlandercy - 8 months ago 47
PHP Question

How to recover a gzcompressed string stored in PostgreSQL ByteA using PHP PDO Object?

I have a problem in understanding how PHP handle ByteA with postgreSQL binding.

For logging and archiving purposes I store in a

column files that I serve with my PHP/Apache Server. For storing, I compress data using
and then I escape string before storing using

// Compress:
if($compress) {
$data = gzencode($data, 9);
// PostgreSQL ByteA Escaping:
$data = pg_escape_bytea($data);

I also have a page that allow user to retrieve previous served files. But I cannot succeed for compressed one, and I don't find why:

$id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT);

$dbCur = $webConn->prepare("SET bytea_output = 'escape';");

$dbCur = $webConn->prepare("SELECT * FROM logs.webservice WHERE Id=?;");
$dbCur->bindParam(1, $id);

$row = $dbCur->fetch(PDO::FETCH_ASSOC);

$data = stream_get_contents($row['binarydata']);
$data = pg_unescape_bytea($data);

if($row['gzip']) {
$data = gzdecode($data);

header("Content-type: ".$row['mimetype']."; charset=".$row['charset']);
echo $data;

I must use PDO object, all examples that I have found (even on PHP website) are based on dedicated DBMS API. Second,
column are returned as resources, then I had to use
to get a string. When I store uncompressed files, I can recover from it easily, what ever I use or not
SET bytea_output = 'escape';
query and/or
function. All combinations allow me to get the file.

When I use compressed data,
dramatically eats up almost all my bytes. Anyway, in all combinations,
fails to work. It seems, I have missing or wrong characters in my binary string that does not block in plain text mode. Anyway, the thing is not well documented on internet and I am stuck without any clue.

How should I recover a gzcompressed string stored in PostgreSQL ByteA using PHP PDO Object?


Solving the retrieval problem only, assuming the insertion works

Once the binary contents get into $data with the following code:

$data = stream_get_contents($row['binarydata']);

they are already in the raw binary format as needed, so you must not decode it again with this:

$data = pg_unescape_bytea($data);

Just remove that spurious unescaping. The reason why you don't notice the problem when $data has only ASCII characters is that pg_unescape_bytea converts these characters to themselves (given bytea_output is set to escape).

But when the binary stream really contains the full range of 256 possible bytes, such as in gzipped content, then it's guaranteed that pg_unescape_bytea will produce a corrupted result in that context.

pg_unescape_bytea should only be used on strings that come directly from the database as bytea-encoded-as-text.

How it's normally done with PDO

Actually with PDO we're not supposed to use the pg_[un]escape_bytea functions, or even any of the functions that start with pg_* because PDO is database-independant, and its purpose is to allow for code that works across different databases.

Inserting should be done as described in http://php.net/manual/en/pdo.lobs.php , qualifying the binary parameters with PDO::PARAM_LOB. When doing this, PDO will itself encodes the data for a binary transfer, using the appropriate method for the kind of database it's connected to.

When doing explicit escaping with pg_escape_bytea(), it produces a string that PDO can consider as a text content and transmit as such. This is a way to transmit binary "behind PDO's back", but there's not much point in doing that.

What definitely does not work is mixing both: escaping (which produces text) and telling PDO that it's binary.