将图像存储在PostgreSQL数据库的bytea字段中 [英] Storing images in bytea fields in a PostgreSQL database
问题描述
我使用PHP在一个PostgreSQL数据库中存储了一个列类型为bytea的图像。问题是我每次尝试在浏览器中加载图像时都没有出现。 Firefox开发者控制台表示图像被截断或损坏。
PHP代码：
//代码插入数据库
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）或die（pg_last_error（））;
//从数据库中删除代码
require_once（'conn.php'）;
session_start（）;
$ user = $ _ SESSION ['name'];
pg_query（'SET bytea_output =escape;'）;
$ lquery =从userinfo选择图像，其中email ='$ user';
$ lq = pg_query（$ lquery）或die（pg_last_error（））;
$ lqq = pg_fetch_row（$ lq，'image'）;
头（conent-type：image）;
echo pg_unescape_bytea（$ lqq [0]）;
我需要将上传的图像存储在数据库中 - 我实际上使用了heroku感谢
TL; DR：
删除 addslashes（ $数据）。这是多余的。
双重转义..两次
$数据=的fread（$ p，文件大小（$ FI））;
$ data = addslashes（$ data）;
$ dat = pg_escape_bytea（$ data）;
您读取数据，将它转义为字符串，然后将其转换为bytea八进制或十六进制转义。即使 pg_escape_bytea 是理智的，它不会这样。
PHP的 pg_escape_bytea 显示为 double-escape 输出，因此可以将其插入到字符串文字中。这是令人难以置信的丑陋，但似乎没有一个替代方案不会执行此双转义操作，所以您似乎无法在PHP中使用bytea的参数化语句。在这种情况下，只需删除 addslashes 行来读取数据即可。 $ b 从文件中获得足够的数据。
测试用例显示 pg_escape_bytea 双重转义（并且始终使用旧的，无效的八进制转义）：
<？php
＃哦-the-horror.php
打印pg_escape_bytea（Blah binary\x00\x01\x02\x03\x04 blah）;
？>
运行：
php oh-the-horror.php
结果：
Blah binary \\000\\\\\\\\\\\\\\\\\\\\\\\\\\\\ 查看加倍的反斜杠？这是因为它假设你将把它作为一个字符串插入到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 默认为十六进制。这意味着，如果我将数据写入 bytea 字段，然后取回它，它会看起来像这样：
克雷格=> CREATE TABLE byteademo（x bytea）;
CREATE TABLE
craig =>插入到byteademo（x）VALUES（'Blah binary \\\\\\\\\\\\\\\\\\\\\'）;
INSERT 0 1
craig => SELECT * FROM byteademo;
x
------------------------------------------ ----------------------------------
\x426c61682062696e6172795c3030305c3030315c3030325c3030335c30303420626c6168
（1 row）
嗯，什么，你可能会说？这很好，它只是PostgreSQL稍微更紧凑的 bytea 的十六进制表示。 pg_unescape_bytea 可以很好地处理它，并生成与输出相同的原始字节......如果您有现代化的PHP和 libpq 。在旧版本中，你会得到垃圾，并且需要为 pg_unescape_bytea设置 bytea_output 到 escape 来处理它。
你应该做什么而不是
使用PDO。
对 bytea 具有理智（ish）支持。
$ 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（）;
请参阅：
您可能还想看看PostgreSQL的lob（大对象）支持，它提供了一个仍然完全事务化的流式可搜索接口。 >
现在，在我的soap框中
如果PHP真的区分了byte字符串和文本字符串类型，你甚至不需要 pg_escape_bytea 作为数据库驱动程序可以为你做。这个丑陋都不是必需的。不幸的是，PHP中没有单独的字符串和字节类型。
请尽可能使用具有参数化语句的PDO。
如果不能，至少应该使用 pg_query_params 和参数化语句。 PHP的 addslashes 不是一种替代方法，它效率低下，丑陋，并且不理解特定于数据库的转义规则。如果您不是因为恶劣的历史原因而使用PDO，还是必须手动转义 bytea ，但其他一切都应该通过参数化语句。
有关 pg_query_params 的指导：
- Bobby表格，PHP部分。 .net / manual / en / function.pg-query-params.phprel =nofollow noreferrer>
pg_query_params 上的PHP手册 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
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
这篇关于将图像存储在PostgreSQL数据库的bytea字段中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
