将图像存储在 PostgreSQL 数据库中的 bytea 字段中 [英] Storing images in bytea fields in a PostgreSQL database

问题描述

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

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 代码:

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

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

推荐答案

TL;DR:

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

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

您读入数据，将其转义为字符串文字，然后将其转换为字节八进制或十六进制转义.即使 pg_escape_bytea 是理智的，但事实并非如此.

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

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.

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

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

测试用例显示 pg_escape_bytea 双转义(并且总是使用旧的、低效的八进制转义):

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 binaryx00x01x02x03x04 blah");
?>

运行:

php oh-the-horror.php

结果:

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

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

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.

这意味着:

pg_unescape_bytea(pg_escape_bytea("x01x02x03"));

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

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

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

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)

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

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

使用 PDO.

它对 bytea 有健全的(ish)支持.

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

见:

• PHP:大对象，其中有一个具体说明的示例你想要；
• PDOStatement::bindParam
• 如何使用 pdo php 在数据库中存储带有命名空间的序列化对象
• PHP5 中将 BYTEA 绑定到 PGSQL PDO 准备好的语句

• 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

您可能还想查看 PostgreSQL 的 lob(大对象)支持，它提供了一个流式、可查找的接口，并且仍然是完全事务性的.

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.

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

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.

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

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

如果不能，至少使用 pg_query_params 和参数化语句.PHP 的 addslashes 不是替代方案，它效率低下、丑陋且不理解数据库特定的转义规则.如果您出于令人讨厌的历史原因不使用 PDO，您仍然必须手动转义 bytea，但其他所有内容都应通过参数化语句.

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.

有关pg_query_params 的指导:

• Bobby 表，PHP 部分.
• pg_query_params 的 PHP 手册

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

这篇关于将图像存储在 PostgreSQL 数据库中的 bytea 字段中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！