在PostgreSQL数据库中使用bytea字段存储图像

Question

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

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]);

我需要将上传的图片存储到数据库中——实际上我正在使用Heroku，谢谢。

- Olayemi Odunayo

---

为什么你需要将图像存储在数据库中？这种方式在各个方面都是低效的。 - user80168

@user80168，至少有一些情况下它并不低效。在一次请求中获取小图像（如缩略图）可以更快。如果您需要适当的ACID语义和数据一致性来处理图片，则相比于使用文件重命名、两阶段提交和fsync()等效率要高得多。在小规模备份时更容易（但在大规模时更难）。就像任何其他事物一样，这是一个权衡，有时可能很好，有时可能很糟糕，大多数情况下它只是还好。 - Craig Ringer

有时效率并不是最重要的方面。 我所参与的所有项目都有某种形式的妥协。 作为一名专业人士，我尝试识别最重要的方面并首先解决它们。 - barrypicker

Answer 1

简而言之：

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

双重转义..两次

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

您需要读取数据，并将其转义为字符串文字，然后将其转换为bytea八进制或十六进制转义。即使 pg_escape_bytea 是正常的，它也永远无法以这种方式运行。

PHP 的 pg_escape_bytea 似乎会对输出进行双重转义，以便将其插入字符串文字中。这非常丑陋，但貌似没有其他方法可以避免这种双重转义，因此在 PHP 中似乎不能使用参数化语句来处理 bytea。但是对于其他一切，您仍然应该这样做。

在这种情况下，只需删除从文件中读取的数据的 addslashes 行即可。

测试用例显示 pg_escape_bytea 双重转义（并始终使用旧的低效八进制转义）：

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

运行：

php oh-the-horror.php

结果：

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

看到了双反斜杠吗？这是因为它假定你将其作为字符串插入到 SQL 中进行插值，这非常浪费内存、丑陋，并且是一种非常不好的习惯。但似乎没有其他选择。

除此之外，这意味着：

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

由于pg_unescape_bytea实际上并不是pg_escape_bytea的反向操作，因此会产生错误的结果。这也使得将pg_escape_bytea的输出作为参数传递给pg_query_params变得不可能，你必须对其进行插值。

解码

如果您正在使用现代的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)

"嗯，什么？" 你可能会说。没关系，这只是PostgreSQL更紧凑的十六进制表示法bytea。如果你有现代的PHP和libpq，pg_unescape_bytea将正确处理它，并产生相同的原始字节作为输出......在旧版本上，你将得到垃圾，并需要将bytea_output设置为escape才能让pg_unescape_bytea处理它。

相反应该做什么

使用PDO。

它对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();

请参考以下链接：

• PHP: Large Objects（示例展示了您所需的内容）
• PDOStatement::bindParam
• how to store serialized object with namespace in database using pdo php
• Bind BYTEA to PGSQL PDO Prepared Statement in PHP5

您还可以研究一下 PostgreSQL 的 lob (large object) 支持，它提供了一个流式、可寻址的接口，仍然完全符合事务性。

现在，我要说几句话

如果 PHP 对 "字节字符串" 和 "文本字符串" 类型有真正的区分，那么您甚至不需要 pg_escape_bytea，因为数据库驱动程序可以为您完成这个工作。没有任何丑陋的东西是必需的。不幸的是，PHP 中没有单独的字符串和字节类型。

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

在不能使用 PDO 的情况下，请至少使用 pg_query_params 和带参数的语句。PHP 的 addslashes 不是一种替代方案，它效率低下、丑陋，并且不理解数据库特定的转义规则。如果由于历史原因而不使用 PDO，则仍然必须手动转义 bytea，但其他所有内容都应通过参数化语句进行处理。

有关 pg_query_params 的指南：

• Bobby tables，PHP 部分。
• PHP 手册的 pg_query_params 部分

- Craig Ringer

---

请帮忙，但我无法检索，请帮忙。这是我的图片： $user_image = User::where('id',Auth::User()->id)->pluck('avatar')->first(); - Sandip Jha

@SandipJha 请发布一个新问题。 - Craig Ringer

Answer 2

如果您确实需要在数据库中存储图像，则最好使用postgres的大对象。在userinfo表中，只需将loid（大对象ID）作为指向图像的链接而不是存储image本身。

将图像插入到数据库中：

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

从数据库中检索图像：

    $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");

loid字段的类型为oid（当然你可以随意命名）。

考虑使用来自lo扩展的lo类型，而不是使用oid类型。使用lo会自动执行“孤儿删除”，即从表中删除一行将自动删除相关的大对象，因此适用于表行“拥有”大对象的情况。

在存储图像链接时，特别方便，尤其是当您多次使用同一张图片时。但是，您应该注意从数据库中删除未使用的图像（PHP函数pg_lo_unlink()）。 Postgres文档中的大型对象。 PHP手册：pg_lo_import。

- klin

---

谢谢大家，现在它像魔法一样工作了。很抱歉我不能投票支持任何答案，因为我需要15个声望才能这样做——我昨天刚加入StackOverflow。在我移除了addslashes函数之后，图片开始加载了。@craig ringer，你真的是一个专业人士！！！！！ - Olayemi Odunayo

大型对象的管理有点麻烦，但至少您需要使用lo扩展程序，以便自动删除孤立对象。就个人而言，我更喜欢将缩略图等内容存储为bytea类型；它将被存储在TOAST表中，因此不会影响不需要它的查询的性能，但在需要时访问方便快捷。 - Craig Ringer

我已经使用大对象多年了。避免孤立对象对我来说是如此显而易见，以至于我甚至没有提到它。我已经编辑了答案，以便未来的读者可以参考。感谢您的留言。 - klin

Answer 3

由于您的数据源是文件系统中的文件，因此我认为在这里找到灵感是有效的：

在您的数据库中创建一个辅助函数，以超级用户身份运行：

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;

在您的 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'");

- David L.

Answer 4

我发现了一种奇怪的方法，可以在不使用PDO的情况下使其工作。

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

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

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

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

我不会假装我知道为什么，但这对我有用！

- Kevin Brown