Thread: 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 );
Any ideas ?
--------------------------
Canaan Surfing Ltd.
Internet Service Providers
Ben-Nes Michael - Manager
Tel: 972-4-6991122
http://sites.canaan.co.il
--------------------------
> 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
> accept it.
>
> Hope this helps,
>
> -- Joe
>
>
>
>
>
>
>
"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 ? > "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 >
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 > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >
> 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.