title: PHP/PostgreSQL: writing binary data with prepared statements
body: <p>I've been using <code>pg_prepare()</code> and <code>pg_execute()</code> very reliably for a long time now, but now that I'm writing binary data to it, I've run into difficulties.</p>
  
  <p>I was surprised to learn that <code>session_encode()</code> 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?</p>
  
  <p>If I execute:</p>
  
  <pre><code>UPDATE "solution_ubasket_temp" SET ("session_id", "session", "id") = ($1, $2, $3) WHERE "id"=$4"
  </code></pre>
  
  <p>and set the 2nd member of the input array to the 5kb return value of <code>session_encode()</code>, it cuts off after the first NULL value it finds.</p>
  
  <pre><code>select octet_length(session), * from solution_ubasket_temp
  </code></pre>
  
  <p>confirms that there are only 105 bytes stored in "session" which are:</p>
  
  <blockquote>
    <p>return_url|s:30:"<a href="https://solution-locale/Moneys" rel="nofollow noreferrer">https://solution-locale/Moneys</a>";complete|s:0:"";basket|O:15:"SolutionUBasket":22:{s:13:</p>
  </blockquote>
  
  <p><code>bin2hex()</code> confirms that the next byte after that string is a NULL.</p>
  
  <p>Making "session" a <code>text</code> or <code>bytea</code> makes no difference.</p>
  
  <p>I tried Googling for things like "php postgresql prepared statements binary" but I just got a lot of hits about MySQL and <a href="http://www.postgresqltutorial.com/postgresql-php/postgresql-blob/" rel="nofollow noreferrer">http://www.postgresqltutorial.com/postgresql-php/postgresql-blob/</a> but that uses something called "PDO" and I don't know what that is.</p>
  
  <p><a href="http://php.net/manual/en/function.pg-execute.php" rel="nofollow noreferrer">http://php.net/manual/en/function.pg-execute.php</a> doesn't have this text, but from <a href="http://php.net/manual/en/function.pg-query-params.php" rel="nofollow noreferrer">http://php.net/manual/en/function.pg-query-params.php</a>:</p>
  
  <blockquote>
    <p>Values intended for bytea fields are not supported as parameters. Use pg_escape_bytea() instead, or use the large object functions.</p>
  </blockquote>
  
  <p>might give a hint. Maybe I'm supposed to be using <code>bytea</code> instead of <code>text</code> and call <code>pg_escape_bytea()</code>.</p>
  
  <p>The table I'm working w/:</p>
  
  <pre><code>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");
  </code></pre>
  
  <p>I'm running PHP 5.6 on Debian 8.5 and PostgreSQL 9.4.</p>