Thread: PQunescapebytea not reverse of PQescapebytea?
Hi -
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. Is there another C method out there that does exact inverse of 'PQescapebytea'? If the answer is no, then what other options do I have? I could think of the following:
1. Write my own C method that does exactly the inverse of 'PQescapebytea'
2. Instead of using 'PQescapebytea', base64 encode ciphertext before inserting. Not sure how well this would work.
3. Use 'text' data type for the column instead of 'byte a'.
Has anyone faced this scenario before?
Thanks-
Karthik
Karthik
* 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.