将图像存储在 PostgreSQL 数据库中的 bytea 字段中

Storing images in bytea fields in a PostgreSQL database

提问by Olayemi Odunayo

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.

我使用 PHP 在列类型为 bytea 的 PostgreSQL 数据库中存储了一个图像。问题是每次我尝试在浏览器中加载图像时它都不会出现。Firefox 开发者控制台显示图像被截断或损坏。

The PHP code:

PHP代码：

//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

我需要将上传的图像存储在数据库中 - 我实际上正在使用 heroku，谢谢

回答by Craig Ringer

TL;DR:

特尔；博士：

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

删除addslashes($data)。这里是多余的。

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_byteawas sane, which it isn't.

您读入数据，将其转义为字符串文字，然后将其转换为字节八进制或十六进制转义。即使pg_escape_bytea是理智的，它也永远不可能那样工作，但事实并非如此。

PHP's pg_escape_byteaappears to double-escapethe 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.

PHPpg_escape_bytea似乎对输出进行了双重转义，因此可以将其插入到字符串文字中。这非常难看，但似乎没有替代方法可以不进行这种双重转义，因此您似乎无法在 PHP 中对 bytea 使用参数化语句。对于其他所有事情，您仍然应该这样做。

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

在这种情况下，只需删除addslashes从文件中读取的数据行就足够了。

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

测试用例显示pg_escape_bytea双重转义（并且总是使用旧的、低效的八进制转义）：

<?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.

看到双反斜杠了吗？那是因为它假设您要将其作为字符串插入到 SQL 中，这是内存效率极低、丑陋且非常坏的习惯。不过，您似乎没有其他选择。

Among other things this means that:

除其他外，这意味着：

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

... produces the wrong result, since pg_unescape_byteais not actually the reverse of pg_escape_bytea. It also makes it impossible to feed the output of pg_escape_byteainto pg_query_paramsas a parameter, you have to interpolate it in.

... 产生错误的结果，因为pg_unescape_bytea实际上并不是pg_escape_bytea. 这也使得无法将pg_escape_byteainto的输出pg_query_params作为参数提供，您必须将其插入。

Decoding

解码

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

如果您使用的是现代 PostgreSQL，它可能会默认设置bytea_output为hex。这意味着如果我将我的数据写入一个bytea字段然后取回它，它看起来像这样：

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_byteawill 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_outputto escapefor pg_unescape_byteato handle it.

“嗯，什么”，你可能会说？没关系，它只是 PostgreSQL 稍微更紧凑的bytea. pg_unescape_bytea会很好地处理它并产生与输出相同的原始字节......如果你有一个现代的 PHP 和libpq. 在旧版本上你会得到垃圾并且需要设置bytea_output为escapeforpg_unescape_bytea来处理它。

What you should do instead

你应该怎么做

Use PDO.

使用 PDO。

It has sane(ish) support for bytea.

它对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

• PHP: Large Objects，它有一个你想要的例子；
• PDOStatement::bindParam
• 如何使用 pdo php 在数据库中存储带有命名空间的序列化对象
• PHP5中将BYTEA绑定到PGSQL PDO Prepared Statement

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.

您可能还想查看 PostgreSQL 的 lob（大对象）支持，它提供了一个流、可查找的接口，它仍然是完全事务性的。

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_byteaas 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.

如果 PHP 在“字节字符串”和“文本字符串”类型之间有真正的区别，那么您甚至不需要，pg_escape_bytea因为数据库驱动程序可以为您做这件事。不需要这些丑陋的东西。不幸的是，PHP 中没有单独的字符串和字节类型。

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

请尽可能使用带有参数化语句的 PDO。

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

在你不能的地方，至少使用pg_query_params和参数化的语句。PHPaddslashes不是替代方案，它效率低下、丑陋，并且不了解数据库特定的转义规则。bytea如果由于棘手的历史原因不使用 PDO，您仍然必须手动转义，但其他一切都应该通过参数化语句。

For guidance on pg_query_params:

有关以下方面的指导pg_query_params：

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

• Bobby 表，PHP 部分。
• PHP 手册 pg_query_params

回答by klin

It is better to use postgres large objectsif you really have to store images in your database. In the userinfotable instead of the imageitself store just a link to it as loid(large object id).

如果您确实需要在数据库中存储图像，最好使用 postgres大对象。在userinfo表中，而不是图像本身，只将指向它的链接存储为loid（大对象 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 loidfield is of type oid (of course you can name it as you want).

该雾状字段类型OID（当然，只要你想，你可以将其命名）的。

Consider using the lotype from the loextensioninstead of using the oidtype. Using logives 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.

考虑使用lo类型从lo延伸，而不是使用oid类型。使用lo为您提供自动“孤立删除”，其中从表中删除一行将自动删除关联的大对象，因此它适用于表行“拥有”大对象的情况。

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()).

如果您多次使用一张图像，则存储图像链接特别方便。但是，您应该注意从数据库中删除未使用的图像（PHP 函数 pg_lo_unlink()）。

Large objects in postgres documentation.

postgres 文档中的大对象。

PHP manual: pg_lo_import.

PHP 手册：pg_lo_import。

回答by Kevin Brown

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

我发现了一种不使用 PDO 也能正常工作的奇怪方法。

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

在 postgresql 中使用文本字段而不是 bytea。在插入时，像这样准备你的数据：

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

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

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

然后，当您想在查询后显示文件时，请使用：

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

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

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

我不会假装我明白为什么，但这对我有用！

回答by David L.

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:

在您的 php 中执行如下查询：

#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'");