store in bytea

[Ben-Nes Michael]

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

[Joe Conway]

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

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 ?

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[Tom Lane]

"Ben-Nes Michael" <mi...@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 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly

[Ben-Nes Michael]

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" <mi...@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 majo...@postgresql.org

[Alex Pilosov]

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

[Ben-Nes Michael]

Im using PHP4.6

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster