Обсуждение: Store base64 in database. Use bytea or text?
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,
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:If they are encoded in base64, use text. Use bytea if you dont want to encode them.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,
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
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:I really don't think you want to do that. Base64 is used to make
> 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?
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.
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, 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/ >
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.
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/
>
--
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, 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/ >> > >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > > > -- > // Dmitriy. > > >
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);
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/
>> >
>>
>> --
>> 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,
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/
>
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 On Fri, 28 Jan 2011 13:32:31 +0000, Andre Lopes wrote: > 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, > > > > > 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/ >>
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 ?
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/
>
--
// 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