store in bytea

Hi

Im using php to upload Binary data into bytea field.
The problem is that when I query what I uploaded I discover that it hold
only 13 characters and not the whole binary file

I tried manual insert more the 13 characters successfully.
I tried to do addslashes($data);

but still :(
$slashed_data = addslashes($data);
$sql = "insert into files (image_id, bin_data, filename, filesize,

filetype)

values ('$image_id', '$slashed_data', '$new_name', '$filesize',
'$filetype');";

I tried strlen($slashed_data); before the insert to be sure that that the
binary is bigger then 13 characters and yes, its 4KB ( the tested file );

I recently posted a PHP function which escapes data for insertion
into a bytea column (for anyone who followed this from the last post,
I found that I needed to add one more escaped character):

function sqlesc($ct)
{
$buf = "";
for ($i = 0; $i < strlen($ct); $i++)
{
if (ord($ct[$i]) == 0)
$buf .= "\\\\000";
else if (ord($ct[$i]) == 10)
$buf .= "\\\\012";
else if (ord($ct[$i]) == 39)
$buf .= "\\\\047";
else if (ord($ct[$i]) == 92)
$buf .= "\\\\134";
else
$buf .= $ct[$i];
}
return $buf;
}

Here's an example of how to use it:

$fp = fopen("/dev/urandom","r");
$iv = fread($fp, 8);
fclose($fp);

$payload = "hello world";

$ct = mcrypt_encrypt (MCRYPT_TRIPLEDES, "mykey", $payload,
MCRYPT_MODE_CBC, $iv);
$esc_ct = sqlesc($ct);

$sql = "insert into foobar(f1,f2) values('$esc_ct',1)";

$rs = pg_exec($conn, $sql);

As far as storage is concerned, all escaped characters get converted back
into their single byte equivilent for storage, so using bytea is the most
efficient way to store binary in the database.

However on retrieval, PostgreSQL will escape all "nonprintable" characters
(based on the C "isprint()" function), which is quite a few. Your PHP app
will have to unescape all of the nonprintable characters. I haven't written
a PHP function for that yet, but it shouldn't be too hard.

I have started hacking the php pgsql extension to add pg_bytea_encode
and pg_bytea_decode functions (so far the pg_bytea_encode is working).
When I'm done I'll submit a patch to the PHP folks, and hopefully they will
accept it.

Hope this helps,

-- Joe

It sure is :)

On the theoretical issue, can I use TEXT field to store binary ?
If so, what will be the case with addslashes ? will it work ?

----- Original Message -----
From: "Joe Conway" <joseph.conway@home.com>
To: "Ben-Nes Michael" <miki@canaan.co.il>; <pgsql-general@postgresql.org>
Sent: Thursday, August 23, 2001 10:18 PM
Subject: Re: [GENERAL] store in bytea

Hi

Im using php to upload Binary data into bytea field.
The problem is that when I query what I uploaded I discover that it hold
only 13 characters and not the whole binary file

I tried manual insert more the 13 characters successfully.
I tried to do addslashes($data);

but still :(
$slashed_data = addslashes($data);
$sql = "insert into files (image_id, bin_data, filename, filesize,

filetype)

values ('$image_id', '$slashed_data', '$new_name', '$filesize',
'$filetype');";

I tried strlen($slashed_data); before the insert to be sure that that

the

binary is bigger then 13 characters and yes, its 4KB ( the tested

file );

I recently posted a PHP function which escapes data for insertion
into a bytea column (for anyone who followed this from the last post,
I found that I needed to add one more escaped character):

function sqlesc($ct)
{
$buf = "";
for ($i = 0; $i < strlen($ct); $i++)
{
if (ord($ct[$i]) == 0)
$buf .= "\\\\000";
else if (ord($ct[$i]) == 10)
$buf .= "\\\\012";
else if (ord($ct[$i]) == 39)
$buf .= "\\\\047";
else if (ord($ct[$i]) == 92)
$buf .= "\\\\134";
else
$buf .= $ct[$i];
}
return $buf;
}

Here's an example of how to use it:

$fp = fopen("/dev/urandom","r");
$iv = fread($fp, 8);
fclose($fp);

$payload = "hello world";

$ct = mcrypt_encrypt (MCRYPT_TRIPLEDES, "mykey", $payload,
MCRYPT_MODE_CBC, $iv);
$esc_ct = sqlesc($ct);

$sql = "insert into foobar(f1,f2) values('$esc_ct',1)";

$rs = pg_exec($conn, $sql);

As far as storage is concerned, all escaped characters get converted back
into their single byte equivilent for storage, so using bytea is the most
efficient way to store binary in the database.

However on retrieval, PostgreSQL will escape all "nonprintable" characters
(based on the C "isprint()" function), which is quite a few. Your PHP app
will have to unescape all of the nonprintable characters. I haven't

written

a PHP function for that yet, but it shouldn't be too hard.

I have started hacking the php pgsql extension to add pg_bytea_encode
and pg_bytea_decode functions (so far the pg_bytea_encode is working).
When I'm done I'll submit a patch to the PHP folks, and hopefully they

will

"Ben-Nes Michael" <miki@canaan.co.il> writes:

On the theoretical issue, can I use TEXT field to store binary ?

TEXT will not handle null (zero) bytes. If you are using a multibyte
character set, it will likely also do the wrong thing with byte
sequences that are illegal or incomplete multibyte characters.

Use BYTEA if you want to store arbitrary byte sequences --- that's what
it's for.

regards, tom lane

But Ill have to add double slashes and even more importent I will have to
unescape every binary data going out of the server, which mean in one of my
aplication all the Images.

Wont this action be much more heavey then the way I used untill now with
MySQL (blob) ?
addslash on insert and ordinary select for output.

Does any one here save his website images on DB and retrive them out on his
web page ?

What are you using to develop? If perl, DBD::Pg will escape/unescape bytea
data after 0.98 version. (Unescaping is automatic, to escape, you need to
do bind(.., SQL_BINARY)).

-alex

On Fri, 24 Aug 2001, Ben-Nes Michael wrote:

But Ill have to add double slashes and even more importent I will have to
unescape every binary data going out of the server, which mean in one of

my

aplication all the Images.

Wont this action be much more heavey then the way I used untill now with
MySQL (blob) ?
addslash on insert and ordinary select for output.

Not sure about performance (would be nice if you tried this and reported
back results of MySQL vs PostgreSQL), but last night I found a built-in PHP
function that looks like it would work to convert the escaped binary back to
binary -- see http://www.php.net/manual/en/function.stripcslashes.php

-- Joe

Im using PHP4.6

What are you using to develop? If perl, DBD::Pg will escape/unescape bytea
data after 0.98 version. (Unescaping is automatic, to escape, you need to
do bind(.., SQL_BINARY)).

-alex

On Fri, 24 Aug 2001, Ben-Nes Michael wrote:

But Ill have to add double slashes and even more importent I will have

to

unescape every binary data going out of the server, which mean in one of

my

aplication all the Images.

Wont this action be much more heavey then the way I used untill now with
MySQL (blob) ?
addslash on insert and ordinary select for output.

Does any one here save his website images on DB and retrive them out on

his

web page ?

"Ben-Nes Michael" <miki@canaan.co.il> writes:

On the theoretical issue, can I use TEXT field to store binary ?

TEXT will not handle null (zero) bytes. If you are using a multibyte
character set, it will likely also do the wrong thing with byte
sequences that are illegal or incomplete multibyte characters.

Use BYTEA if you want to store arbitrary byte sequences --- that's

what

it's for.

regards, tom lane

--------------------------
Canaan Surfing Ltd.
Internet Service Providers
Ben-Nes Michael - Manager
Tel: 972-4-6991122
http://sites.canaan.co.il
--------------------------

At 09:28 AM 8/24/01 -0400, Tom Lane wrote:

"Ben-Nes Michael" <miki@canaan.co.il> writes:

On the theoretical issue, can I use TEXT field to store binary ?

TEXT will not handle null (zero) bytes. If you are using a multibyte
character set, it will likely also do the wrong thing with byte
sequences that are illegal or incomplete multibyte characters.

Use BYTEA if you want to store arbitrary byte sequences --- that's what
it's for.

I wanted to use bytea wasn't sure how though. So I used base64 and text.
Yeah that is ugly but it works.

With bytea I didn't understand how to quote and unquote stuff. And how
stuff is stored. At that time the docs were sparse on it. I didn't know
what characters I'd need to unquote and quote and how, yeah null was one
but the others?

Sure people can use the source to figure out how to use bytea. But what if
the source is different from the intended behaviour and the source is fixed
later.

Don't want to be hit by a gotcha after writing lots of code y'know.

So base64 and text it was. It was either that or store in the filesystem
(that's what I did with 6.5 - sorry but I have no confidence in postgresql
BLOBs).

Cheerio,
Link.