Re: store in bytea

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(dot)conway(at)home(dot)com>
To: "Ben-Nes Michael" <miki(at)canaan(dot)co(dot)il>; <pgsql-general(at)postgresql(dot)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
>
>
>
>
>
>
>