Re: store in bytea - Mailing list pgsql-general

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
>
>
>
>
>
>
>