Обсуждение: PQunescapebytea not reverse of PQescapebytea?

* Karthik Segpi:

> I have a 'bytea' column in the database, onto which my custom C application
> is inserting encrypted data. Before inserting, I am calling
> 'PQescapebytea()' to escape the ciphertext. However, after SELECT, the data
> needs to be 'un-escaped' before attempting to decrypt. I am trying to
> 'un-escape' using 'PQunescapebytea'. However, I am finding that
> 'PQunescapebytea' is not  exact inverse of 'PQescapebytea'. I saw
> documentation and posts in the mailing lists alluding to this as well. As a
> result, the decryption always fails.

Can you show us some example data that shows the inconsistency?
PQunescapebytea should give you back the blob you passed to
PQescapebytea, but the same blob can have different BYTEA
encodings—not everyone uses the \x hexadecimal encoding.

On Wed, 2014-03-19 at 21:28 +0100, Florian Weimer wrote:
> * Karthik Segpi:
> 
> > I have a 'bytea' column in the database, onto which my custom C application
> > is inserting encrypted data. Before inserting, I am calling
> > 'PQescapebytea()' to escape the ciphertext. However, after SELECT, the data
> > needs to be 'un-escaped' before attempting to decrypt. I am trying to
> > 'un-escape' using 'PQunescapebytea'. However, I am finding that
> > 'PQunescapebytea' is not  exact inverse of 'PQescapebytea'. I saw
> > documentation and posts in the mailing lists alluding to this as well. As a
> > result, the decryption always fails.
> 
> Can you show us some example data that shows the inconsistency?
> PQunescapebytea should give you back the blob you passed to
> PQescapebytea, but the same blob can have different BYTEA
> encodings—not everyone uses the \x hexadecimal encoding.

Example:
 size_t len1, len2; char *str = "\\\\123";
 printf("%s\n", str); printf("%s\n", PQescapeBytea(str, strlen(str), &len1)); printf("%s\n", PQunescapeBytea(
                    PQescapeBytea(str, strlen(str), &len1),                                &len2));
 

The reason for this is that PQescapeBytea is designed to escape it to be
passed into the server via a SQL string (adding two levels of escaping,
one for the sql string and one for bytea); whereas PQunescapeBytea is
designed to unescape a result coming back from the server (which only
has one level of escaping to undo: the bytea escaping).

To be more consistent, PQ[un]escapeBytea might only [un]escape the bytea
portion, making them true inverses. That would mean that to put a bytea
in a sql string, you'd need to do something like: PQescapeString(PQescapeBytea(str, ...))
rather than just: PQescapeBytea(str, ...)

Both methods are a bit error-prone. The first is error-prone because you
have to remember to do both; the second because it's inconsistent.

The best thing to do is use parameterized input to reduce the room for
confusion. Specify bytea parameters as binary format, and you don't need
to escape them at all on input. When reading them back out of the
server, use binary format if you can; and if not, then you'll need to
use PQunescapeBytea.

Regards,Jeff Davis

* Jeff Davis:

> On Wed, 2014-03-19 at 21:28 +0100, Florian Weimer wrote:
>> * Karthik Segpi:
>>
>> > I have a 'bytea' column in the database, onto which my custom C application
>> > is inserting encrypted data. Before inserting, I am calling
>> > 'PQescapebytea()' to escape the ciphertext. However, after SELECT, the data
>> > needs to be 'un-escaped' before attempting to decrypt. I am trying to
>> > 'un-escape' using 'PQunescapebytea'. However, I am finding that
>> > 'PQunescapebytea' is not  exact inverse of 'PQescapebytea'. I saw
>> > documentation and posts in the mailing lists alluding to this as well. As a
>> > result, the decryption always fails.
>>
>> Can you show us some example data that shows the inconsistency?
>> PQunescapebytea should give you back the blob you passed to
>> PQescapebytea, but the same blob can have different BYTEA
>> encodings—not everyone uses the \x hexadecimal encoding.
>
> Example:
>
>   size_t len1, len2;
>   char *str = "\\\\123";
>
>   printf("%s\n", str);
>   printf("%s\n", PQescapeBytea(str, strlen(str), &len1));
>   printf("%s\n", PQunescapeBytea(
>                                  PQescapeBytea(str, strlen(str), &len1),
>                                  &len2));
>
> The reason for this is that PQescapeBytea is designed to escape it to be
> passed into the server via a SQL string (adding two levels of escaping,
> one for the sql string and one for bytea); whereas PQunescapeBytea is
> designed to unescape a result coming back from the server (which only
> has one level of escaping to undo: the bytea escaping).

Ah, right, this is annoying.  I think a thin (one-level) escaping
function would make sense to add to libpq.