Storing Zip file in Postgres

Question

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

Can't you use a bound parameter and let the PostgreSQL driver deal with properly encoding the binary data? — mu is too short
– mu is too short, Commented Aug 4, 2014 at 20:59
Never, ever, ever use addslashes. If you're using it, that's a giant flashing warning saying "probable security bug here". In this case you should clearly be using a parameterzed query. — Craig Ringer
– Craig Ringer, Commented Aug 5, 2014 at 1:09

Clodoaldo Neto · Accepted Answer · 2014-08-04 20:44:50Z

3

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.

answered Aug 4, 2014 at 20:44

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael A Over a year ago

This worked for me, I just had to use $content = substr($content, 2); and $content = hex2bin($content); when reading it from the database

Community · Accepted Answer · 2017-05-23 11:49:20Z

2

Now is a good opportunity to switch to PDO, which does this sort of thing cleanly and sanely.

Here's an earlier answer I wrote on this topic, which discusses the numerous ways in which the old-style PostgreSQL driver is truly awful for binary data.

The short version is that your code should be more like:

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

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Aug 5, 2014 at 1:10

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

1 Comment

Michael A Over a year ago

Thanks for the answer! Definitely helpful, although I ended up using Clodoaldo's method.

fiction · Accepted Answer · 2014-08-04 20:23:15Z

0

Try to use base64encode() function:

$content = file_get_contents($zipLocation);
$content = base64encode($content);

$sql="INSERT INTO ZIP_TBL ( BUILD, CONTENT)
    VALUES ('$build', '$content')";

if (!pg_query($con,$sql)) {
    die('Error: ' . pg_last_error($con));
}

And when you will need this data back, use base64decode() function. Also, type of your column needs to be text to store this data.

string base64_encode ( string $data )

string base64_decode ( string $data [, bool $strict = false ] )

http://php.net//manual/en/function.base64-decode.php

http://php.net//manual/en/function.base64-encode.php

answered Aug 4, 2014 at 20:23

fiction

5781 gold badge6 silver badges22 bronze badges

3 Comments

Michael A Over a year ago

Using this method I don't see any errors writing to the database, but when I try to read from the database the zip file is still corrupted ( I didn't forget to use base64_decode). I could post the php code I'm using to read from the database if that would help.

fiction Over a year ago

i wrote simple script to test it at my local machine and it's all right. Paste your code, will check it

user1587985 Over a year ago

You might need to change database collation

Collectives™ on Stack Overflow

Storing Zip file in Postgres

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related