From: | Bill Moran <wmoran(at)potentialtech(dot)com> |
---|---|
To: | Dmitriy Igrishin <dmitigr(at)gmail(dot)com> |
Cc: | Andre Lopes <lopes80andre(at)gmail(dot)com>, Peter Geoghegan <peter(dot)geoghegan86(at)gmail(dot)com>, postgresql Forums <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Store base64 in database. Use bytea or text? |
Date: | 2011-01-26 14:09:29 |
Message-ID: | 20110126090929.495544aa.wmoran@potentialtech.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
In response to Dmitriy Igrishin <dmitigr(at)gmail(dot)com>:
> 2011/1/26 Andre Lopes <lopes80andre(at)gmail(dot)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/
From | Date | Subject | |
---|---|---|---|
Next Message | Arturo Perez | 2011-01-26 14:27:28 | Re: temporal period type and select distinct gives equality error |
Previous Message | Mark Morgan Lloyd | 2011-01-26 09:52:18 | Getting the name of the timezone, adjusted for daylight saving |