PHP/PostgreSQL: writing binary data with prepared statements

Question

I've been using pg_prepare() and pg_execute() very reliably for a long time now, but now that I'm writing binary data to it, I've run into difficulties.

I was surprised to learn that session_encode() returns NULL values in its strings, but have always been told that you don't need to escape data in prepared statements. So why when I write a 5kb field to the db, does it cut off after about 100 bytes?

If I execute:

UPDATE "solution_ubasket_temp" SET ("session_id", "session", "id") = ($1, $2, $3) WHERE "id"=$4"

and set the 2nd member of the input array to the 5kb return value of session_encode(), it cuts off after the first NULL value it finds.

select octet_length(session), * from solution_ubasket_temp

confirms that there are only 105 bytes stored in "session" which are:

return_url|s:30:"https://solution-locale/Moneys";complete|s:0:"";basket|O:15:"SolutionUBasket":22:{s:13:

bin2hex() confirms that the next byte after that string is a NULL.

Making "session" a text or bytea makes no difference.

I tried Googling for things like "php postgresql prepared statements binary" but I just got a lot of hits about MySQL and http://www.postgresqltutorial.com/postgresql-php/postgresql-blob/ but that uses something called "PDO" and I don't know what that is.

http://php.net/manual/en/function.pg-execute.php doesn't have this text, but from http://php.net/manual/en/function.pg-query-params.php:

Values intended for bytea fields are not supported as parameters. Use pg_escape_bytea() instead, or use the large object functions.

might give a hint. Maybe I'm supposed to be using bytea instead of text and call pg_escape_bytea().

The table I'm working w/:

CREATE TABLE solution_ubasket_temp
(
  session_id character varying(40) NOT NULL,
  session text,
  last_update timestamp with time zone NOT NULL DEFAULT now(),
  order_id integer,
  status character varying(2),
  email character varying(100),
  id integer NOT NULL DEFAULT nextval('solution_ubasket_temp_id_seq1'::regclass),
  CONSTRAINT solution_ubasket_temp_id_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

CREATE INDEX solution_ubasket_temp_email_index
  ON solution_ubasket_temp
  USING btree
  (email COLLATE pg_catalog."default");

CREATE INDEX solution_ubasket_temp_session_id_index
  ON solution_ubasket_temp
  USING btree
  (session_id COLLATE pg_catalog."default");

I'm running PHP 5.6 on Debian 8.5 and PostgreSQL 9.4.

Answer 1

Using PDO

To effectively insert/update text or binary to your table, you need to use PDO, this is what the code might look like.

<?php
$dbh = new PDO('pgsql:host=localhost;dbname=test_rideshare', "postgres");

$fdata = file_get_contents('/tmp/sample.txt');
$n = 11;


$stmt = $dbh->prepare("INSERT INTO solution_ubasket_temp(session_id,session,order_id) Values(?,?,?) ");
$stmt->bindParam(1,$n);
$stmt->bindParam(2,$fdata, PDO::PARAM_LOB);
$stmt->bindParam(3,$n);


$stmt->execute();
print $stmt->debugDumpParams();
print_r($stmt->errorInfo());
print_r($dbh->errorInfo());


$stmt = $dbh->prepare("SELECT session FROM solution_ubasket_temp LIMIT 1");
$stmt->execute();
$a = $stmt->fetch();
print_r($a);

file_put_contents("/tmp/sample2.txt",$a['session']);

?>

The above code sample shows how to do an insert and then read the data again. It also writes the same data to another file for comparison. Visual or diff comparison will show that they are the same.

For update, just replace the insert statement with an update statement

Binary Data

If you are storing binary data, you should use a binary field. That is

session bytea,

In fact, I never use text fields, when ever I have the need to store large objects I choose binary and whenever possible I try to avoid storing large objects in the db. In fact though my code above demonstrates how to store a file in the DB. this is something that I would never do in production.

PHP Serialize vs JSON

You data looks like it's serialized PHP. May I suggest that you switch to JSON instead and store the data in an JSONB field instead? That would give you many more options than PHP serialize and deserialize, not to mention that mention the fact that JSON is supported in almost any programming language while using PHP serialize would lock you down to PHP.