postgresql and binary data

[Dmitry Teslenko]

Hello!
I have some postgresql database that stores binary data (windows/linux
executables). Field type I use for this is bytea.
Also I have php script that allows users download these binaries via
their web browser.
That stuff was running on ubuntu 7.10 with postgresql 8.2 and most
recent php version.

That was written like this:

$db_con = pg_connect(...) ;
/*determine file name (rg_target) and file
size (l) with postgres' octet_length*/
header('Content-type: application/octet-stream');
/*header("Conten t-length: $l");*/
header("Content-disposition: attachment; filename=$rg_ta rget");
pg_free_result( $db_res);
$db_res = pg_query(..)
or die('Error executing query: ' . pg_last_error() );
$rg_binary = pg_fetch_result ($db_res, 'rg_binary');
echo pg_unescape_byt ea($rg_binary);
pg_free_result( $db_res);
pg_close();
exit();

and that worked (with commented content-length) in ff and ie.

Then I've upgraded to ubuntu 8.04, postgres 8.3 and this code became
broken: when downloading file, only first ~175 kbytes get downloaded.
File in database, say, 4MB and user can download only first 175 kbyte
chunk.

[Sjoerd]

Re: postgresql and binary data

On May 21, 9:58 am, Dmitry Teslenko <dtesle...@gmai l.comwrote:

Then I've upgraded to ubuntu 8.04, postgres 8.3 and this code became
broken.

Maybe you can run the query without using PHP, i.e. using psql. This
way, you can check whether this is a PHP issue or a Postgres issue.

[Gordon]

Re: postgresql and binary data

On May 21, 8:58 am, Dmitry Teslenko <dtesle...@gmai l.comwrote:

Hello!
I have some postgresql database that stores binary data (windows/linux
executables). Field type I use for this is bytea.
Also I have php script that allows users download these binaries via
their web browser.
That stuff was running on ubuntu 7.10 with postgresql 8.2 and most
recent php version.
>
That was written like this:
>
$db_con = pg_connect(...) ;
/*determine file name (rg_target) and file
size (l) with postgres' octet_length*/
header('Content-type: application/octet-stream');
/*header("Conten t-length: $l");*/
header("Content-disposition: attachment; filename=$rg_ta rget");
pg_free_result( $db_res);
$db_res = pg_query(..)
or die('Error executing query: ' . pg_last_error() );
$rg_binary = pg_fetch_result ($db_res, 'rg_binary');
echo pg_unescape_byt ea($rg_binary);
pg_free_result( $db_res);
pg_close();
exit();
>
and that worked (with commented content-length) in ff and ie.
>
Then I've upgraded to ubuntu 8.04, postgres 8.3 and this code became
broken: when downloading file, only first ~175 kbytes get downloaded.
File in database, say, 4MB and user can download only first 175 kbyte
chunk.

My prior experience has tended to find that storing BLOBs in the
database itself is often problematic, and that the filesystem itself
is the best place to store data such as binaries. Whenever I need a
database to manipulate binary data I store the binary itself to a file
on disk and just store the filename in the database itself.

If the reason you're storing binaries in the database is to prevent
unauthorized downloads then may I suggest that instead you store the
files to a directory that's outside your web tree (PHP can write
anywhere in the filesystem that the user your web server is running
under has write permission for) and use readfile() or
file_get_conten ts() to download it to the user?

[Dmitry Teslenko]

Re: postgresql and binary data

Sjoerd wrote:

On May 21, 9:58 am, Dmitry Teslenko <dtesle...@gmai l.comwrote:

>Then I've upgraded to ubuntu 8.04, postgres 8.3 and this code became
>broken.

>
Maybe you can run the query without using PHP, i.e. using psql. This
way, you can check whether this is a PHP issue or a Postgres issue.

Sure I can. That was clearly php issue but I didn't know which one.
Trying to figure out data length I found out that script execution
aborts due to exceeding allowed memory limit. In ubuntu 8.10 they set it
to 16 mbytes (looks like in 7.10 it was bigger).

So I set it to 100 mbytes and that works!

Thanks!

[Dmitry Teslenko]

Re: postgresql and binary data

Gordon wrote:

On May 21, 8:58 am, Dmitry Teslenko <dtesle...@gmai l.comwrote:
My prior experience has tended to find that storing BLOBs in the
database itself is often problematic, and that the filesystem itself
is the best place to store data such as binaries. Whenever I need a
database to manipulate binary data I store the binary itself to a file
on disk and just store the filename in the database itself.
>
If the reason you're storing binaries in the database is to prevent
unauthorized downloads then may I suggest that instead you store the
files to a directory that's outside your web tree (PHP can write
anywhere in the filesystem that the user your web server is running
under has write permission for) and use readfile() or
file_get_conten ts() to download it to the user?

Reason is I upload files via python script and it could be run on win32
or unix os. I.e. I searched for portable solution to execute some
scripting language that could execute some commands and transfer files
to another computer and one I found was python and RDBMS on distant
computer.

I agree that storing binary data in database is brittle and error-prone
solution.