在Postgres中存储Zip文件 [英] Storing Zip file in Postgres
问题描述
我一直在将zip文件的内容存储在MySQL数据库的LONGBLOB中,但是我正在迁移到Postgres.根据我的阅读,Postgres中的LONGBLOB相当于bytea.不幸的是,我无法将zip内容写入数据库.首先,我收到此错误:
警告:pg_query():查询失败:错误:编码"UTF8"的字节序列无效0x5c
然后我用SQL_ASCII编码创建了一个新数据库(以前的数据库为UTF8),并出现以下错误:
PHP警告:pg_query():查询失败:错误:v╘▓ú5"或附近的语法错误第2行:...√╢V[úB√▬┌»å⌐²└ù╙±b±≡ß▼┐π}°ï»┌G╜₧╧εo»〜°0o╞W/_╝ƒL¢\'v╘▓ú5;Ω░#第...
有没有办法在Postgres中创建数据库/表/列而不进行编码?如果没有,我应该如何存储此信息?我尝试使用pg_escape_bytea,但是在尝试解压缩内容时会产生错误.不确定这是否真的很重要,但是这是我用来写入数据库的PHP:
$ content = file_get_contents($ zipLocation);$ content =加号($ content);$ sql ="INSERT INTO ZIP_TBL(BUILD,CONTENT)VALUES('$ build','$ content');如果(!pg_query($ con,$ sql)){die('Error:'.pg_last_error($ con));}
bytea
输入格式之一是十六进制.因此,请使用 bin2hex
$ content = file_get_contents($ zipLocation);$ content ='\\ x'.bin2hex($ content);$ sql =插入zip_tbl(构建,内容)值('$ build',E'$ content');
http://www.postgresql.org/docs/current/static/datatype-binary.html#AEN5318
http://php.net/manual/en/function.bin2hex.php
顺便说一句,如果您不知道,则很容易受到SQL注入的攻击.</p>
I've been storing the contents of zip files in a LONGBLOB in a MySQL database, but I'm moving to Postgres. From what I've read, the equivalent to LONGBLOB in Postgres is bytea. Unfortunately, I can't get the zip contents to write to the database. First, I got this error:
Warning: pg_query(): Query failed: ERROR: invalid byte sequence for encoding "UTF8"
0x5c
Then I created a new database with SQL_ASCII encoding (the previous had UTF8), and got this error:
PHP Warning: pg_query(): Query failed: ERROR: syntax error at or near "v╘▓ú5"
LINE 2: ...√╢V[úB√▬┌»å⌐²└ù╙±b±≡ß▼┐π}°ï»┌G╜₧╧εo»~°0o╞W/_╝ƒL¢\'v╘▓ú5;Ω░#╩...
Is there any way to create a database/table/column in Postgres without encoding? If not, how should I store this information? I tried using pg_escape_bytea but that produces errors when I try to unzip the contents. Not sure that this really matters, but here's the PHP I'm using to write to the database:
$content = file_get_contents($zipLocation);
$content = addslashes($content);
$sql="INSERT INTO ZIP_TBL ( BUILD, CONTENT)
VALUES ('$build', '$content')";
if (!pg_query($con,$sql)) {
die('Error: ' . pg_last_error($con));
}
One of the bytea
input formats is hexadecimal. So use bin2hex
$content = file_get_contents($zipLocation);
$content = '\\x' . bin2hex($content);
$sql="
insert into zip_tbl ( build, content)
values ('$build', E'$content')
";
http://www.postgresql.org/docs/current/static/datatype-binary.html#AEN5318
http://php.net/manual/en/function.bin2hex.php
BTW, if you are not aware, you are susceptible to SQL injection.
这篇关于在Postgres中存储Zip文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!