1

We have a problem retrieving uploaded image from postgres database with yii2

we store image with that way to the db:

$data = pg_escape_bytea(file_get_contents($model->CheckIfAvatarExists(Yii::$app->user->identity->username)));

$profile->passphoto = new Expression("'{$data}'");
$profile->save();

stores image perfectly

but when we try to display image, it is not working:

header('Content-type: image/png');

echo pg_unescape_bytea(  $profile->passphoto);

I think the big problem is data after escaped it wont back to original be unescape

Any solutions?

oratoran
  • 23
  • 6

1 Answers1

1

Consider this user-contributed comment in pg_unescape_bytea online doc:

PostgreSQL 9.0 introduced "hex" as the new default format for encoding binary data. Because "pg_unescape_bytea" only works with the old "escape" format, you need to do pg_query('SET bytea_output = "escape";'); before executing your select queries.

In fact, it's only true when the client library is older than 9.0 (libq.so.5.2 I believe, now EOL'ed).

If that is your case, that explains the problem of wrong unescaping.

To force bytea_output to escape to decode properly, you may either :

  • dynamically with SET bytea_output=escape; as a SQL command in the session
  • or statically for the whole database: ALTER DATABASE SET bytea_output=escape;
  • or in postgresql.conf for the whole instance.

The ideal solution would be to upgrade the libpq client library to a newer version, in which case none of this is necessary, it just works.

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156