Display image from PostgreSQL database in PHP

问题:

I want to display an image from my PostgreSQL database in a PHP file. After trying a lot of different possibilities, I don't get very far. Here is what I do:

Upload:

echo "<div>".
    "<table border=\"1\" cellpadding=\"3\">".
       "<form action=\"test2.php\" method=\"post\" enctype=\"multipart/form-data\">".
         "<tr>".
           "<td>".
             "<input type=\"file\" name=\"file\" id=\"file\">".
             "<input class=\"button\" type=\"submit\" name=\"submit\" value=\"Submit\">".
           "<tr>".
         "</tr>".
      "</form>".
     "</table>".
   "</div>"; 

Display:

if(isset($_FILES['file'])) //file uploaded
{
  $file_raw = file_get_contents($_FILES['file']['tmp_name']);
  $file     = pg_escape_bytea($file_raw);
  //Here is Code to insert into Database but just to test, I try it directly:

  header('Content-Type: image/png');
  echo pg_unescape_bytea($file);
}

I already have the display part in an extra file and so on, but these are the essential information you need to know.

I wont get any image displayed, just this "Broken Image" icon from the browser. What is wrong here? How can I solve this? Thank you!

回答1:

Simple handling:

<?php 
// Connect to the database
$dbconn = pg_connect( 'dbname=foo' );

// Read in a binary file
$data = file_get_contents( 'image1.jpg' );

// Escape the binary data to avoid problems with encoding
$escaped = bin2hex( $data );

// Insert it into the database
pg_query( "INSERT INTO gallery (name, data) VALUES ('Pine trees', decode('{$escaped}' , 'hex'))" );

// Get the bytea data
$res = pg_query("SELECT encode(data, 'base64') AS data FROM gallery WHERE name='Pine trees'");  
$raw = pg_fetch_result($res, 'data');

// Convert to binary and send to the browser
header('Content-type: image/jpeg');
echo base64_decode($raw);
?>

回答2:

The short answer is that pg_unescape_bytea is not necessarily the inverse of pg_escape_bytea. This is mentioned in libpq documentation about PQunescapeBytea :

This conversion is not exactly the inverse of PQescapeBytea, because the string is not expected to be "escaped" when received from PQgetvalue

Back to php, you'd need to do a round-trip through the database for the test to be meaningful, for example:

$pgconn = pg_connect("....");
$escaped = pg_escape_bytea($pgconn, $bytes);
$pgr = pg_query($pgconn, "SELECT '$escaped'::bytea");
list($textual) = pg_fetch_array($pgr);
$raw_bytes = pg_unescape_bytea($textual);

In this case $raw_bytes will be identical to the initial $bytes, in all cases.

---

On a side note, if you want to dig deeper, there's much more to it.

pg_escape_bytea takes a connection resource to a database as an optional argument:

string pg_escape_bytea ([ resource $connection ], string $data )

Its behavior differs whether a connection is used or not (when $connection is left out but pg_connect() has been called before, the "current connection" will be used anyway).

When a connection is used, its output is driven by the connection properties, specifically:

• is the PG server recent enough (>=9.0) to support hex sequences \x... in bytea strings?
• is standard_conforming_strings to on or off (on by default when >=9.1)

Examples of different results with the same input in different configurations:

Server 9.1, client 9.1, php 5.3.10

Code #1:

// No connection
// pg_connect("dbname=test");
echo pg_escape_bytea(chr(0).chr(1));

Result #1:

 \\000\\001

Code #2:

// With connection
$pgconn= pg_connect("dbname=test");
echo pg_escape_bytea(chr(0).chr(1));

Result #2:

 \x0001

Code #3:

// With connection
$pgconn= pg_connect("dbname=test");
pg_query("SET standard_conforming_strings TO off");
echo pg_escape_bytea(chr(0).chr(1));

Result #3:

 \\x0001

Now with an older configuration: 8.4 client, 8.4 server, php 5.2.6:

Code #4:

$pgconn= pg_connect("dbname=test");
pg_query("SET standard_conforming_strings TO off");
echo pg_escape_bytea(chr(0).chr(1));

Result #4:

 \\000\\001

Code #5:

$pgconn= pg_connect("dbname=test");
pg_query("SET standard_conforming_strings TO on");
echo pg_escape_bytea(chr(0).chr(1));

Result #5:

 \000\001

Now we don't need to care about all this in normal usage. Just using it as intended with its connection will work since the server will interpret the results correctly. The point is to not assume that pg_escape_bytea is a server-independant fixed reversible transformation like bin2hex or some such, because it's not.