Postgres Pro
Downloads
Home   >   mailing lists

Thread: PQunescapebytea not reverse of PQescapebytea?

PQunescapebytea not reverse of PQescapebytea?

From
Karthik Segpi
Date:
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 'bytea'.

Has anyone faced this scenario before?

Thanks-
Karthik

Re: PQunescapebytea not reverse of PQescapebytea?

From
Tom Lane
Date:
Karthik Segpi <karthik.segpi@gmail.com> writes:
> 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'.

It's not supposed to be, as the fine manual points out:

    This conversion is not exactly the inverse of PQescapeBytea, because the
    string is not expected to be "escaped" when received from PQgetvalue. In
    particular this means there is no need for string quoting considerations,
    and so no need for a PGconn parameter.

If you're having problems, it's probably because you are misusing one
function or the other.  A likely bet is that you're passing the output
of PQescapeBytea through some additional processing rather than just
sticking it into an INSERT statement with single quotes around it.
But it's impossible to be sure without seeing a detailed example of
what you're doing, on both the insertion and extraction sides.

            regards, tom lane