Re: store in bytea - Mailing list pgsql-general

> 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