Storing images in bytea fields in a PostgreSQL dat

Question

I stored an image in a PostgreSQL database with column type bytea using PHP. The problem is every time I try to load the image in a browser it does not appear. The Firefox developer console says the image is either truncated or corrupt.

The PHP code:

//code for inserting into the database
if(array_key_exists('submit_pic', $_POST)){
$user=$_SESSION['name'];
if(isset($_FILES['thumbnail'])&&$_FILES['thumbnail']['size']>0){
$fi =  $_FILES['thumbnail']['tmp_name'];
$p=fopen($fi,'r');
$data=fread($p,filesize($fi));
$data=addslashes($data);
$dat= pg_escape_bytea($data); 
$q="update userinfo set image='{$dat}' where email='$user'";
$e=pg_query($q)or die(pg_last_error());

// code for retreving from database
require_once('conn.php');
session_start();
$user=$_SESSION['name'];
pg_query('SET bytea_output = "escape";');
$lquery ="select image from userinfo where email='$user'";
$lq = pg_query($lquery)or die(pg_last_error());
$lqq=pg_fetch_row($lq,'image');
header("conent-type:image");
echo pg_unescape_bytea($lqq[0]);

and i need to store the uploaded image in a database- i am actually using heroku thanks

Answer 1

TL;DR:

Delete addslashes($data). It's redundant here.

Double-escaping .. twice

$data=fread($p,filesize($fi));
$data=addslashes($data);
$dat= pg_escape_bytea($data); 

You read the data in, escape it as if it were a string literal, then convert it to bytea octal or hex escapes. It could never work that way around even if pg_escape_bytea was sane, which it isn't.

PHP's pg_escape_bytea appears to double-escape the output so it can be inserted into a string literal. This is incredibly ugly, but there doesn't appear to be an alternative that doesn't do this double-escaping, so you can't seem to use parameterised statements for bytea in PHP. You should still do so for everything else.

In this case, simply removing the addslashes line for the data read in from the file is sufficient.

Test case showing that pg_escape_bytea double-escapes (and always uses the old, inefficient octal escapes, too):

<?php
# oh-the-horror.php
print pg_escape_bytea("Blah binary\x00\x01\x02\x03\x04 blah");
?>

Run:

php oh-the-horror.php

Result:

Blah binary\\000\\001\\002\\003\\004 blah

See the doubled backslashes? That's because it's assuming you're going to interpolate it into SQL as a string, which is extremely memory inefficient, ugly, and a very bad habit. You don't seem to get any alternative, though.

Among other things this means that:

pg_unescape_bytea(pg_escape_bytea("\x01\x02\x03"));

... produces the wrong result, since pg_unescape_bytea is not actually the reverse of pg_escape_bytea. It also makes it impossible to feed the output of pg_escape_bytea into pg_query_params as a parameter, you have to interpolate it in.

Decoding

If you're using a modern PostgreSQL, it probably sets bytea_output to hex by default. That means that if I write my data to a bytea field then fetch it back, it'll look something like this:

craig=> CREATE TABLE byteademo(x bytea);
CREATE TABLE
craig=> INSERT INTO byteademo(x) VALUES ('Blah binary\\000\\001\\002\\003\\004 blah');
INSERT 0 1
craig=> SELECT * FROM byteademo ;
                                     x                                      
----------------------------------------------------------------------------
 \x426c61682062696e6172795c3030305c3030315c3030325c3030335c30303420626c6168
(1 row)

"Um, what", you might say? It's fine, it's just PostgreSQL's slightly more compact hex representation of bytea. pg_unescape_bytea will handle it fine and produce the same raw bytes as output ... if you have a modern PHP and libpq. On older versions you'll get garbage and will need to set bytea_output to escape for pg_unescape_bytea to handle it.

What you should do instead

Use PDO.

It has sane(ish) support for bytea.

$sth = $pdo->prepare('INSERT INTO mytable(somecol, byteacol) VALUES (:somecol, :byteacol)');
$sth->bindParam(':somecol', 'bork bork bork');
$sth->bindParam(':byteacol', $thebytes, PDO::PARAM_LOB);
$sth->execute();

See:

• PHP: Large Objects, which has an example of exactly what you want;
• PDOStatement::bindParam
• how to store serialized object with namespace in database using pdo php
• Bind BYTEA to PGSQL PDO Prepared Statement in PHP5

You may also want to look in to PostgreSQL's lob (large object) support, which provides a streaming, seekable interface that's still fully transactional.

Now, on to my soap box

If PHP had a real distinction between "byte string" and "text string" types, you wouldn't even need pg_escape_bytea as the database driver could do it for you. None of this ugliness would be required. Unfortunately, there are no separate string and bytes types in PHP.

Please, use PDO with parameterised statements as much as possible.

Where you can't, at least use pg_query_params and parameterised statements. PHP's addslashes is not an alternative, it's inefficient, ugly, and doesn't understand database specific escaping rules. You still have to manually escape bytea if you're not using PDO for icky historical reasons, but everything else should go through parameterised statements.

For guidance on pg_query_params:

• Bobby tables, PHP section.
• The PHP manual on pg_query_params

Answer 2

I found a strange way of getting this to work too without using PDO.

Use a text field in postgresql instead of bytea. On insert, prep your data like this:

$imgdta = pg_escape_string(bin2hex($filedata));

Then when you want to display the file after your query, use:

echo pack("H*", $img["filedata"]);

I'm not going to pretend I get why, but this worked for me!

Answer 3

As the source of your data is a file in the file system so it seems to me efficient to find an inspiration here:

In your db create an auxiliary function, run as superuser:

create or replace function bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
begin
  select lo_import(p_path) into l_oid;
  select lo_get(l_oid) INTO p_result;
  perform lo_unlink(l_oid);
end;$$
security definer;

In your php execute a query like:

#make sure that postgres will have access to the file
chmod($_FILES['thumbnail']['tmp_name'], 0644);
pg_query("update userinfo set image=(select bytea_import('".$_FILES['thumbnail']['tmp_name']."')) where email='$user'");

Answer 4

It is better to use postgres large objects if you really have to store images in your database. In the userinfo table instead of the image itself store just a link to it as loid (large object id).

Insert an image into the database:

    pg_query("begin");  // pg_lo functions need to be run in a transaction
    $loid = pg_lo_import('full_path_and_file_name');
    pg_query("update userinfo set loid=$loid where email='$user'");
    pg_query("commit");

Retrieve an image from the database:

    $rs = pg_query("select loid from userinfo where email='$user'");
    $loid = pg_fetch_row($rs, 0)[0];
    pg_query("begin");
    $blob = pg_lo_open($loid, "r");
    header("Content-type: image");
    pg_lo_read_all($blob);
    pg_lo_close($blob);
    pg_query("commit");

The loid field is of type oid (of course you can name it as you want).

Consider using the lo type from the lo extension instead of using the oid type. Using lo gives you automatic "orphan removal", where deleting a row from a table will automatically remove the associated large object, so it's good for cases where a table row "owns" a large object.

Storing links to images is especially convenient in case you use one image more than one time. However, you should pay attention to delete unused images from your database (PHP function pg_lo_unlink()).

Large objects in postgres documentation.

PHP manual: pg_lo_import.