Store base64 in database. Use bytea or text?

In response to Andre Lopes <lopes80andre@gmail.com>:

I need to put some images on Base64 in a PostgreSQL database. Wich
type should I use and what is the difference between using bytea or
text to store Base64?

bytea is overkill if you know for sure that the data will always be
base64. Aside from that, I don't know of any particular advantage
either way. Since the data isn't really text, your locale isn't
really going to come into play (i.e., it's not like you're going to
sort the data or anything). Since the data isn't binary, you won't
have to worry about escaping anything.

Personally, I'd use text.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

On 1/25/2011 4:27 PM, Andre Lopes wrote:

Hi,

I need to put some images on Base64 in a PostgreSQL database. Wich
type should I use and what is the difference between using bytea or
text to store Base64?

Best Regards,

If they are encoded in base64, use text. Use bytea if you dont want to
encode them.

-Andy

2011/1/26 Andy Colson <andy@squeakycode.net>

On 1/25/2011 4:27 PM, Andre Lopes wrote:

Hi,

I need to put some images on Base64 in a PostgreSQL database. Wich
type should I use and what is the difference between using bytea or
text to store Base64?

Best Regards,

If they are encoded in base64, use text. Use bytea if you dont want to
encode them.

Or *store* in bytea by using decode() function and retrieve
in base64 by using encode() function.
http://www.postgresql.org/docs/9.0/static/functions-string.html

-Andy

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
// Dmitriy.

On 25 January 2011 22:27, Andre Lopes <lopes80andre@gmail.com> wrote:

Hi,

I need to put some images on Base64 in a PostgreSQL database. Wich
type should I use and what is the difference between using bytea or
text to store Base64?

I really don't think you want to do that. Base64 is used to make
binary data 7-bit safe for compatibility with legacy systems (i.e. to
embed arbitrary binary data within ASCII). Sometimes people escape
binary data as base64 to store it in their DB, but they typically
store it as bytea. Base64 probably isn't even a particularly good
choice for escaping binary, let alone storing it.

You should just use a generic escaping function. libpq has
PQescapeByteaConn(), for example.

--
Regards,
Peter Geoghegan

In response to Peter Geoghegan <peter.geoghegan86@gmail.com>:

On 25 January 2011 22:27, Andre Lopes <lopes80andre@gmail.com> wrote:

Hi,

I need to put some images on Base64 in a PostgreSQL database. Wich
type should I use and what is the difference between using bytea or
text to store Base64?

I really don't think you want to do that. Base64 is used to make
binary data 7-bit safe for compatibility with legacy systems (i.e. to
embed arbitrary binary data within ASCII). Sometimes people escape
binary data as base64 to store it in their DB, but they typically
store it as bytea. Base64 probably isn't even a particularly good
choice for escaping binary, let alone storing it.

You should just use a generic escaping function. libpq has
PQescapeByteaConn(), for example.

A warning: last I checked, PHP's pg_escape_bytea() was broken, so be
cautious if you're using PHP.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

2011/1/26 Peter Geoghegan <peter.geoghegan86@gmail.com>

On 25 January 2011 22:27, Andre Lopes <lopes80andre@gmail.com> wrote:

Hi,

I need to put some images on Base64 in a PostgreSQL database. Wich
type should I use and what is the difference between using bytea or
text to store Base64?

I really don't think you want to do that. Base64 is used to make
binary data 7-bit safe for compatibility with legacy systems (i.e. to
embed arbitrary binary data within ASCII). Sometimes people escape
binary data as base64 to store it in their DB, but they typically
store it as bytea. Base64 probably isn't even a particularly good
choice for escaping binary, let alone storing it.

You should just use a generic escaping function. libpq has
PQescapeByteaConn(), for example.

It is better to use PQexecParams and binary data transfer instead
of escaping via PQescapeByteaConn.

--
Regards,
Peter Geoghegan

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
// Dmitriy.

Thanks for all the reply's.

I will be using PHP for now to insert data.

So I shouldn't use base64 to store images or any other kind of files.
I'm new to storing files in the database. This will be my first
experience.

I will research about PQescapeByteaConn.

Thanks for the help.

Best Regards,

2011/1/26 Andre Lopes <lopes80andre@gmail.com>

Thanks for all the reply's.

I will be using PHP for now to insert data.

So I shouldn't use base64 to store images or any other kind of files.
I'm new to storing files in the database. This will be my first
experience.

You may want to use large objects to store files instead of using
bytea data type. With large objects you can achieve streaming
data transfer and as of PostgreSQL 9.0 it is possible to control
access permissions on large objects via GRANT.

But I don't know about support of large objects in PHP.

I will research about PQescapeByteaConn.

Thanks for the help.

Best Regards,

On Tue, Jan 25, 2011 at 10:46 PM, Bill Moran <wmoran@potentialtech.com>
wrote:

In response to Peter Geoghegan <peter.geoghegan86@gmail.com>:

On 25 January 2011 22:27, Andre Lopes <lopes80andre@gmail.com> wrote:

Hi,

I need to put some images on Base64 in a PostgreSQL database. Wich
type should I use and what is the difference between using bytea or
text to store Base64?

I really don't think you want to do that. Base64 is used to make
binary data 7-bit safe for compatibility with legacy systems (i.e. to
embed arbitrary binary data within ASCII). Sometimes people escape
binary data as base64 to store it in their DB, but they typically
store it as bytea. Base64 probably isn't even a particularly good
choice for escaping binary, let alone storing it.

You should just use a generic escaping function. libpq has
PQescapeByteaConn(), for example.

A warning: last I checked, PHP's pg_escape_bytea() was broken, so be
cautious if you're using PHP.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/&lt;http://people.collaborativefusion.com/%7Ewmoran/&gt;

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
// Dmitriy.

Thanks for the reply.

I will mainly store files with 100kb to 250kb not bigger than this.

PQescapeByteaConn is not available in a default installation of
PostgreSQL? My hosting account hava a standard installation of
PostgreSQL. There are other options for escaping binary files?

Best Regards,

2011/1/26 Andre Lopes <lopes80andre@gmail.com>

Thanks for the reply.

I will mainly store files with 100kb to 250kb not bigger than this.

PQescapeByteaConn is not available in a default installation of
PostgreSQL? My hosting account hava a standard installation of
PostgreSQL. There are other options for escaping binary files?

Best Regards,

PQescapeByteConn is a function of libpq - native C client library.
In you case (PHP) you should use its functions to encode binary
data before including it into a query (e.g., in base64) and use
built-in decode() function of Postgres:
-- Pseudo-code
INSERT INTO img (dat) VALUES (decode(BASE64_INPUT_FROM_PHP, 'base64'));

where dat column of table img of type bytea.

Or you can use PHP's function which escapes binary data for bytea
textual representation (now hex by default) and omit decode(). In this
case you query will like that:
-- Pseudo-code
INSERT INTO img (dat) VALUES (BYTEA_ESCAPED_FROM_PHP);

On Tue, Jan 25, 2011 at 10:58 PM, Dmitriy Igrishin <dmitigr@gmail.com>
wrote:

2011/1/26 Andre Lopes <lopes80andre@gmail.com>

Thanks for all the reply's.

I will be using PHP for now to insert data.

So I shouldn't use base64 to store images or any other kind of files.
I'm new to storing files in the database. This will be my first
experience.

You may want to use large objects to store files instead of using
bytea data type. With large objects you can achieve streaming
data transfer and as of PostgreSQL 9.0 it is possible to control
access permissions on large objects via GRANT.

But I don't know about support of large objects in PHP.

I will research about PQescapeByteaConn.

Thanks for the help.

Best Regards,

On Tue, Jan 25, 2011 at 10:46 PM, Bill Moran <wmoran@potentialtech.com>
wrote:

In response to Peter Geoghegan <peter.geoghegan86@gmail.com>:

On 25 January 2011 22:27, Andre Lopes <lopes80andre@gmail.com>

wrote:

Hi,

I need to put some images on Base64 in a PostgreSQL database. Wich
type should I use and what is the difference between using bytea or
text to store Base64?

I really don't think you want to do that. Base64 is used to make
binary data 7-bit safe for compatibility with legacy systems (i.e. to
embed arbitrary binary data within ASCII). Sometimes people escape
binary data as base64 to store it in their DB, but they typically
store it as bytea. Base64 probably isn't even a particularly good
choice for escaping binary, let alone storing it.

You should just use a generic escaping function. libpq has
PQescapeByteaConn(), for example.

A warning: last I checked, PHP's pg_escape_bytea() was broken, so be
cautious if you're using PHP.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/&lt;http://people.collaborativefusion.com/%7Ewmoran/&gt;

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
// Dmitriy.

--
// Dmitriy.

In response to Dmitriy Igrishin <dmitigr@gmail.com>:

2011/1/26 Andre Lopes <lopes80andre@gmail.com>

Thanks for the reply.

I will mainly store files with 100kb to 250kb not bigger than this.

PQescapeByteaConn is not available in a default installation of
PostgreSQL? My hosting account hava a standard installation of
PostgreSQL. There are other options for escaping binary files?

Best Regards,

PQescapeByteConn is a function of libpq - native C client library.
In you case (PHP) you should use its functions to encode binary
data before including it into a query (e.g., in base64) and use
built-in decode() function of Postgres:
-- Pseudo-code
INSERT INTO img (dat) VALUES (decode(BASE64_INPUT_FROM_PHP, 'base64'));

where dat column of table img of type bytea.

More specifically:

$query = "INSERT INTO image_data (bytea_field) VALUES ('" .
pg_escape_bytea($binary_data) . "')";
pg_query($query);

And to get it back out:
$query = "SELECT bytea_field FROM image_data";
$rs = pg_query($query);
$row = pg_fetch_assoc($rs);
$binary_data = pg_unescape_bytea($row['bytea_field']);

(note that I may have omitted some steps for clarity)

DO NOT use parametrized queries with PHP and bytea (I hate to say that,
because parametrized fields are usually a very good idea). PHP has a
bug that mangles bytea data when pushed through parametrized fields.

PHP bug #35800

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Hi,

Another question about this subject.

It is possible to cache this images from the database? In the future I
will need to cache the pictures.

If you have any knowledge about this, please give me a clue.

Best Regards,

Hi,

In means of database, it is impossible. If you want to cache, add
version or last modified column, then ask for changes and cache data
locally.

Kind regards,
Radosław Smogura
http://softperience.eu

2011/1/28 Andre Lopes <lopes80andre@gmail.com>

Hi,

Another question about this subject.

It is possible to cache this images from the database? In the future I
will need to cache the pictures.

If you have any knowledge about this, please give me a clue.

Best Regards,

How would you like to cache them ? On a file system of client (e.g.,
WEB-server) ?
Why would you like to cache them ?

On Wed, Jan 26, 2011 at 2:09 PM, Bill Moran <wmoran@potentialtech.com>
wrote:

In response to Dmitriy Igrishin <dmitigr@gmail.com>:

2011/1/26 Andre Lopes <lopes80andre@gmail.com>

Thanks for the reply.

I will mainly store files with 100kb to 250kb not bigger than this.

PQescapeByteaConn is not available in a default installation of
PostgreSQL? My hosting account hava a standard installation of
PostgreSQL. There are other options for escaping binary files?

Best Regards,

PQescapeByteConn is a function of libpq - native C client library.
In you case (PHP) you should use its functions to encode binary
data before including it into a query (e.g., in base64) and use
built-in decode() function of Postgres:
-- Pseudo-code
INSERT INTO img (dat) VALUES (decode(BASE64_INPUT_FROM_PHP, 'base64'));

where dat column of table img of type bytea.

More specifically:

$query = "INSERT INTO image_data (bytea_field) VALUES ('" .
pg_escape_bytea($binary_data) . "')";
pg_query($query);

And to get it back out:
$query = "SELECT bytea_field FROM image_data";
$rs = pg_query($query);
$row = pg_fetch_assoc($rs);
$binary_data = pg_unescape_bytea($row['bytea_field']);

(note that I may have omitted some steps for clarity)

DO NOT use parametrized queries with PHP and bytea (I hate to say that,
because parametrized fields are usually a very good idea). PHP has a
bug that mangles bytea data when pushed through parametrized fields.

PHP bug #35800

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/&lt;http://people.collaborativefusion.com/%7Ewmoran/&gt;

--
// Dmitriy.

On 2011-01-26, Bill Moran <wmoran@potentialtech.com> wrote:

DO NOT use parametrized queries with PHP and bytea (I hate to say that,
because parametrized fields are usually a very good idea). PHP has a
bug that mangles bytea data when pushed through parametrized fields.

PHP bug #35800

OOTOH pg_insert() and pg_update() work well with bytea data

--
⚂⚃ 100% natural

On 2011-01-28, Dmitriy Igrishin <dmitigr@gmail.com> wrote:

--001636c598d9470a92049ae97be4
Content-Type: text/plain; charset=UTF-8

2011/1/28 Andre Lopes <lopes80andre@gmail.com>

Hi,

Another question about this subject.

It is possible to cache this images from the database? In the future I
will need to cache the pictures.

for HTTP caching to work you need to serve the resources with
apropriate HTTP headers, and probably need to honour HEAD requests in
a useful way.

you mention PHP, how does your web server communicate a HEAD request
to your PHP script?

it's beginning to look like disk might be a better place to store the
images. it's more work to administer, but it lets the server and
cache interact naturally.

--
⚂⚃ 100% natural