#276 Problem editing bytea columns, with fix.
G'day. One of my clients is using phpPgAdmin to
manage a database with tables that include bytea
columns.
This works ok for display, but doesn't correctly
format the bytea data on the way back into the
database.
I have been able to resolve this, against the version
in our current distribution (3.5.5), and I did verify
that the problem still exists in the most current
release on SF.net.
The changes required were quite minimal:
in classes/database/Postgres.php:
in function 'printField', include the '\' character in
the list of character to escape with 'addCSlashes'.
in the function 'formatValue', add handling for bytea
that does:
case 'bytea':
$value = stripcslashes($value);
$value = pg_escape_bytea($value);
return "'$value'::bytea";
Including the '\' character in the initial escaping is
necessary for reversing the process later.
I hope that helps. Feel free to let me know if you
need more advice or information on this.
Regards,
Daniel <daniel@rimspace.net>
Logged In: YES
user_id=361841
Originator: NO
Does the effect change if you have magic_quotes_gpc turned off?
This would appear to only be a problem with magic_quotes on. Are you able to verify that?
Logged In: YES
user_id=15283
Originator: NO
I had a similar problem using PhpPgAdmin 4.1.1 with Postgres 8.1 and PHP 5.1.6.
I fixed it by adding this to formatValue's select:
case 'bytea':
return "'$value'::bytea";
The strip/escape dance that Daniel did above did not work for me. (I do have magic_quotes=off). However the error that was occurring is because the "default:" action calls clean, which calls pg_escape_string(). That function was doubling the single-quotes which had already been doubled by the call to pg_escape_bytea() before displaying the string for editing in the first place. So all my single-quotes were being doubled and corrupting my binary data.
In effect, the edit/save cycle was doing this to the bytea data:
pg_escape_string( pg_escape_bytea( $original_data ) )
The case I added reduces it to this (which is as it should be, I think):
pg_escape_bytea( $original_data )
One caveat: all the data must be entered by the user in Postgres "input" format. That means double-slashes for octal bytes and doubled single-quotes for single-quotes. "This ain't gonna work" if entered in a bytea field won't work. It has to be "This ain''t gonna work.".
I think Daniel's fix is magic_quotes related, but perhaps he only got there after enabling magic-quotes after seeing the doubled-single-quote problem as I did in the first place. So I think my report qualifies as the same bug, and Daniel's fix led me to the right places to make the right fix. So I'm just adding my notes here.
Adding steps to reproduce, which were submitted by Farkaspók in a later ticket.
===
I created a table with a bytea column, and insert only one ' (single quote)
character to this column. If i edit this row with phpPgAdmin, it displays
two ' characters, and if i save this without changes, it stores two single
quotes.
===
And a more general summary of the problem; the issue here is we need to escape the data on the way out, so that it displays as data rather than a binary when select/browsing. We also need to escape the data on the way in, to avoid any hijinx from data input. So, we end up with double escaping.
Possible solution #1: Is there a better way to escape the data on the way out? Could we convert it via htmlentities or something similar, or would that lead to corrupted data?
Possible solution #2: It might be possible to only escape the data while browsing, and then repull the data and dump it to a form field when going to edit it. Then we could escape it on the way back in. Of course that could completely blow up...