pgp_sym_decrypt() - error 39000: wrong key or corrupt data

On 06/21/2018 08:36 AM, Moreno Andreo wrote:

Hi,
ￜￜￜ while playing with pgcrypto I ran into a strange issue (postgresql
9.5.3 x86 on Windows 7)

Having a table with a field
dateofbirth text

I made the following sequence of SQL commands
update tbl_p set dateofbirth = pgp_sym_encrypt('2018-06-21', 'AES_KEY')
where codguid = '00000001-0001-0001-0001-000000000001';
OK

select pgp_sym_decrypt(dateofbirth::bytea, 'AES_KEY') as datanasc from
tbl_p where codguid = '00000001-0001-0001-0001-000000000001'
'2018-06-21'

select * from tab_paz where pgp_sym_decrypt(natoil::bytea, 'AES_KEY') =
'2018-06-21'

You switched gears above.

What is the data type of the natoil field in table tab_paz?

Was the data encrypted in it using the 'AES_KEY'?

I can replicate the below by doing:

select pgp_sym_decrypt(pgp_sym_encrypt('2018-06-21', 'AES_KEY'), 'AES');
ERROR: Wrong key or corrupt data

ERROR:ￜ Wrong key or corrupt data
********** Error **********

ERROR: Wrong key or corrupt data
SQL state: 39000

Can't find reference anywhere...
Any help would be appreciated.
Thanks,
Moreno.-

--
Adrian Klaver
adrian.klaver@aklaver.com

Il 21/06/2018 23:31, Adrian Klaver ha scritto:

On 06/21/2018 08:36 AM, Moreno Andreo wrote:

Hi,
ￜￜￜￜ while playing with pgcrypto I ran into a strange issue
(postgresql 9.5.3 x86 on Windows 7)

Having a table with a field
dateofbirth text

I made the following sequence of SQL commands
update tbl_p set dateofbirth = pgp_sym_encrypt('2018-06-21',
'AES_KEY') where codguid = '00000001-0001-0001-0001-000000000001';
OK

select pgp_sym_decrypt(dateofbirth::bytea, 'AES_KEY') as datanasc
from tbl_p where codguid = '00000001-0001-0001-0001-000000000001'
'2018-06-21'

select * from tab_paz where pgp_sym_decrypt(natoil::bytea, 'AES_KEY')
= '2018-06-21'

You switched gears above.

What is the data type of the natoil field in table tab_paz?

Sorry, just a typo... natoil is, actually dateofbirth, so it's text.
You can read it as
select * from tbl_p where pgp_sym_decrypt(dateofbirth::bytea, 'AES_KEY')
= '2018-06-21'

Was the data encrypted in it using the 'AES_KEY'?

Yes, the command sequence is exactly reported above.
If I use pgp_sym_decrypt in a SELECT statement it's OK, but if it's in a
where clause it seems not to be working.

On 06/22/2018 01:46 AM, Moreno Andreo wrote:

Il 21/06/2018 23:31, Adrian Klaver ha scritto:

On 06/21/2018 08:36 AM, Moreno Andreo wrote:

Hi,
ￜￜￜￜ while playing with pgcrypto I ran into a strange issue
(postgresql 9.5.3 x86 on Windows 7)

Having a table with a field
dateofbirth text

I made the following sequence of SQL commands
update tbl_p set dateofbirth = pgp_sym_encrypt('2018-06-21',
'AES_KEY') where codguid = '00000001-0001-0001-0001-000000000001';
OK

select pgp_sym_decrypt(dateofbirth::bytea, 'AES_KEY') as datanasc
from tbl_p where codguid = '00000001-0001-0001-0001-000000000001'
'2018-06-21'

select * from tab_paz where pgp_sym_decrypt(natoil::bytea, 'AES_KEY')
= '2018-06-21'

You switched gears above.

What is the data type of the natoil field in table tab_paz?

Sorry, just a typo... natoil is, actually dateofbirth, so it's text.
You can read it as
select * from tbl_p where pgp_sym_decrypt(dateofbirth::bytea, 'AES_KEY')
= '2018-06-21'

Was the data encrypted in it using the 'AES_KEY'?

Yes, the command sequence is exactly reported above.
If I use pgp_sym_decrypt in a SELECT statement it's OK, but if it's in a
where clause it seems not to be working.

Are you sure that the entries where not encrypted with a different key
because I can't replicate.(More comments below):

create table pgp_test(id integer, fld_1 varchar);

insert into pgp_test values (1, pgp_sym_encrypt('2018-06-21', 'AES_KEY'))

select * from pgp_test ;

id |
fld_1

----+------------------------------------------------------------------------------------------------------------------------------------------------------------
1 |
\xc30d04070302444e9b2792436e3d7ed23b01cf097f0a6a36298bab63ae4f22f39de54a9b0d8f905d48198ce76089de5f21669c46d96439718b6b0408c541427b6e7c11008bd3d0ebdae0dceb

select * from pgp_test where pgp_sym_decrypt(fld_1::bytea, 'AES_KEY') =
'2018-06-21';

id |
fld_1

----+------------------------------------------------------------------------------------------------------------------------------------------------------------
1 |
\xc30d04070302444e9b2792436e3d7ed23b01cf097f0a6a36298bab63ae4f22f39de54a9b0d8f905d48198ce76089de5f21669c46d96439718b6b0408c541427b6e7c11008bd3d0ebdae0dceb

Have you looked at the entry in its encrypted state to see if it looks
the same as pgp_sym_encrypt('2018-06-21', 'AES_KEY')?

Can you return decrypted values for other items in the table?

--
Adrian Klaver
adrian.klaver@aklaver.com

Il 22/06/2018 15:18, Adrian Klaver ha scritto:

On 06/22/2018 01:46 AM, Moreno Andreo wrote:

Il 21/06/2018 23:31, Adrian Klaver ha scritto:

On 06/21/2018 08:36 AM, Moreno Andreo wrote:

Hi,
ￜￜￜￜ while playing with pgcrypto I ran into a strange issue
(postgresql 9.5.3 x86 on Windows 7)

Having a table with a field
dateofbirth text

I made the following sequence of SQL commands
update tbl_p set dateofbirth = pgp_sym_encrypt('2018-06-21',
'AES_KEY') where codguid = '00000001-0001-0001-0001-000000000001';
OK

select pgp_sym_decrypt(dateofbirth::bytea, 'AES_KEY') as datanasc
from tbl_p where codguid = '00000001-0001-0001-0001-000000000001'
'2018-06-21'

select * from tab_paz where pgp_sym_decrypt(natoil::bytea,
'AES_KEY') = '2018-06-21'

You switched gears above.

What is the data type of the natoil field in table tab_paz?

Sorry, just a typo... natoil is, actually dateofbirth, so it's text.
You can read it as
select * from tbl_p where pgp_sym_decrypt(dateofbirth::bytea,
'AES_KEY') = '2018-06-21'

Was the data encrypted in it using the 'AES_KEY'?

Yes, the command sequence is exactly reported above.
If I use pgp_sym_decrypt in a SELECT statement it's OK, but if it's
in a where clause it seems not to be working.

Are you sure that the entries where not encrypted with a different key
because I can't replicate.(More comments below):

(other replies below, inline)
I'm almost sure (you're never absolutely sure :-) ), since I kept all
commands I entered in PgAdminIII SQL Window, and they're reported above.
On the other side, I tried the same procedure on another field and it
succeeded.

The only difference between the 2 fields, and I don't know if it can
make any sense, is that the field I tried now and succeeded was created
as text, while the other field (dateofbirth) was a timestamp I ALTERed
with the statement
alter table tbl_p alter column dateofbirth type text using
to_char(dateofbirth, 'YYYY-MM-DD');

I'm just afraid it can happen in production....

create table pgp_test(id integer, fld_1 varchar);

insertￜ into pgp_test values (1, pgp_sym_encrypt('2018-06-21',
'AES_KEY'))

select * from pgp_test ;

ￜid | ￜￜￜￜￜￜￜ fld_1
----+------------------------------------------------------------------------------------------------------------------------------------------------------------

ￜ 1 |
\xc30d04070302444e9b2792436e3d7ed23b01cf097f0a6a36298bab63ae4f22f39de54a9b0d8f905d48198ce76089de5f21669c46d96439718b6b0408c541427b6e7c11008bd3d0ebdae0dceb

select * from pgp_test where pgp_sym_decrypt(fld_1::bytea, 'AES_KEY')
= '2018-06-21';

id | ￜￜￜￜￜ fld_1
----+------------------------------------------------------------------------------------------------------------------------------------------------------------

ￜ 1 |
\xc30d04070302444e9b2792436e3d7ed23b01cf097f0a6a36298bab63ae4f22f39de54a9b0d8f905d48198ce76089de5f21669c46d96439718b6b0408c541427b6e7c11008bd3d0ebdae0dceb

Have you looked at the entry in its encrypted state to see if it looks
the same as pgp_sym_encrypt('2018-06-21', 'AES_KEY')?

Yes, it seems to have the same value

On 06/22/2018 09:50 AM, Moreno Andreo wrote:

Il 22/06/2018 15:18, Adrian Klaver ha scritto:

Are you sure that the entries where not encrypted with a different key
because I can't replicate.(More comments below):

(other replies below, inline)
I'm almost sure (you're never absolutely sure :-) ), since I kept all
commands I entered in PgAdminIII SQL Window, and they're reported above.
On the other side, I tried the same procedure on another field and it
succeeded.

The only difference between the 2 fields, and I don't know if it can
make any sense, is that the field I tried now and succeeded was created
as text, while the other field (dateofbirth) was a timestamp I ALTERed
with the statement
alter table tbl_p alter column dateofbirth type text using
to_char(dateofbirth, 'YYYY-MM-DD');

Assuming the ALTER TABLE was done and then the values where encrypted,
that does not seem to affect anything here(More below):

test=# create table pgp_alter_test(id integer, birthdate date);
CREATE TABLE
test=# \d pgp_alter_test
Table "public.pgp_alter_test"
Column | Type | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
id | integer | | |
birthdate | date | | |

test=# insert into pgp_alter_test values (1, '2018-06-21');
INSERT 0 1
test=# select * from pgp_alter_test ;
id | birthdate
----+------------
1 | 2018-06-21
(1 row)

test=# alter table pgp_alter_test alter column birthdate type text using
to_char(birthdate, 'YYYY-MM-DD');
ALTER TABLE

test=# \d pgp_alter_test
Table "public.pgp_alter_test"
Column | Type | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
id | integer | | |
birthdate | text |

test=# select * from pgp_alter_test ;
id | birthdate

----+------------

1 | 2018-06-21

(1 row)

test=# update pgp_alter_test set birthdate = pgp_sym_encrypt(birthdate,
'AES_KEY') where id = 1;

UPDATE 1

test=# select * from pgp_alter_test ;

id |
birthdate

----+------------------------------------------------------------------------------------------------------------------------------------------------------------

1 |
\xc30d04070302b3f55c80f9ab657c68d23b010cd9d4d82631f89c786394a8bceb35529db07c708e5a0c4f04cf91aed24b5ff397dd99c678ec4f5bb769e148cfae3cdfc5453daaeb34ddd2737c

(1 row)

^

test=# select * from pgp_alter_test where
pgp_sym_decrypt(birthdate::bytea, 'AES_KEY') = '2018-06-21';

id |
birthdate

----+------------------------------------------------------------------------------------------------------------------------------------------------------------

1 |
\xc30d04070302b3f55c80f9ab657c68d23b010cd9d4d82631f89c786394a8bceb35529db07c708e5a0c4f04cf91aed24b5ff397dd99c678ec4f5bb769e148cfae3cdfc5453daaeb34ddd2737c

(1 row)

I am at a loss now. The only thing I can think of is that data itself is
actually corrupted. Maybe some sort of language encoding/collation
issue. Just not sure how to test that at the moment.

I'm just afraid it can happen in production....

create table pgp_test(id integer, fld_1 varchar);

insertￜ into pgp_test values (1, pgp_sym_encrypt('2018-06-21',
'AES_KEY'))

Have you looked at the entry in its encrypted state to see if it looks
the same as pgp_sym_encrypt('2018-06-21', 'AES_KEY')?

Yes, it seems to have the same value

So

--
Adrian Klaver
adrian.klaver@aklaver.com

Il 22/06/2018 19:56, Adrian Klaver ha scritto:

On 06/22/2018 09:50 AM, Moreno Andreo wrote:

Il 22/06/2018 15:18, Adrian Klaver ha scritto:

Are you sure that the entries where not encrypted with a different
key because I can't replicate.(More comments below):

(other replies below, inline)
I'm almost sure (you're never absolutely sure :-) ), since I kept all
commands I entered in PgAdminIII SQL Window, and they're reported above.
On the other side, I tried the same procedure on another field and it
succeeded.

The only difference between the 2 fields, and I don't know if it can
make any sense, is that the field I tried now and succeeded was
created as text, while the other field (dateofbirth) was a timestamp
I ALTERed with the statement
alter table tbl_p alter column dateofbirth type text using
to_char(dateofbirth, 'YYYY-MM-DD');

Assuming the ALTER TABLE was done and then the values where encrypted,
that does not seem to affect anything here(More below):

test=# create table pgp_alter_test(id integer, birthdate date);
CREATE TABLE
test=# \d pgp_alter_test
ￜￜￜￜￜￜￜￜￜￜￜ Table "public.pgp_alter_test"
ￜ Columnￜￜ |ￜ Typeￜￜ | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
ￜidￜￜￜￜￜￜￜ | integer |ￜￜￜￜￜￜￜￜￜￜ |ￜￜￜￜￜￜￜￜￜ |
ￜbirthdate | dateￜￜￜ |ￜￜￜￜￜￜￜￜￜￜ |ￜￜￜￜￜￜￜￜￜ |

test=# insert into pgp_alter_test values (1, '2018-06-21');
INSERT 0 1
test=# select * from pgp_alter_test ;
ï¿œid | birthdate
----+------------
ￜ 1 | 2018-06-21
(1 row)

test=# alter table pgp_alter_test alter column birthdate type text
using to_char(birthdate, 'YYYY-MM-DD');
ALTER TABLE

test=# \d pgp_alter_test
ￜￜￜￜￜￜￜￜￜￜￜ Table "public.pgp_alter_test"
ￜ Columnￜￜ |ￜ Typeￜￜ | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
ￜidￜￜￜￜￜￜￜ | integer |ￜￜￜￜￜￜￜￜￜￜ |ￜￜￜￜￜￜￜￜￜ |
ￜbirthdate | textￜￜￜ |

test=# select * from pgp_alter_test ;
ï¿œid | birthdate

----+------------

ￜ 1 | 2018-06-21

(1 row)

test=# update pgp_alter_test set birthdate =
pgp_sym_encrypt(birthdate, 'AES_KEY') where id = 1;
UPDATE 1

test=# select * from pgp_alter_test ;
ￜid | ￜￜￜￜￜ birthdate
----+------------------------------------------------------------------------------------------------------------------------------------------------------------

ￜ 1 |
\xc30d04070302b3f55c80f9ab657c68d23b010cd9d4d82631f89c786394a8bceb35529db07c708e5a0c4f04cf91aed24b5ff397dd99c678ec4f5bb769e148cfae3cdfc5453daaeb34ddd2737c

(1 row)

ￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜￜ ^

test=# select * from pgp_alter_test where
pgp_sym_decrypt(birthdate::bytea, 'AES_KEY') = '2018-06-21';
ￜid | ￜￜￜￜￜ birthdate
----+------------------------------------------------------------------------------------------------------------------------------------------------------------

ￜ 1 |
\xc30d04070302b3f55c80f9ab657c68d23b010cd9d4d82631f89c786394a8bceb35529db07c708e5a0c4f04cf91aed24b5ff397dd99c678ec4f5bb769e148cfae3cdfc5453daaeb34ddd2737c

(1 row)

I am at a loss now. The only thing I can think of is that data itself
is actually corrupted. Maybe some sort of language encoding/collation
issue. Just not sure how to test that at the moment.

Actually, I tried it in a bunch of other fields with varying data types
and everything went fine.
I don't know if it's as you say and I mismatched keys (and I need
another pair of glasses) or something else. Just hoping (but being
confident) it won't happen again.

Now trying to speed up a little some queries involving SELECTing among
these encrypted fields, if I'm stuck I'll open a new thread.

Thanks,
Moreno.-

On 06/27/2018 09:55 AM, Moreno Andreo wrote:

Il 22/06/2018 19:56, Adrian Klaver ha scritto:

On 06/22/2018 09:50 AM, Moreno Andreo wrote:

Il 22/06/2018 15:18, Adrian Klaver ha scritto:

I am at a loss now. The only thing I can think of is that data itself
is actually corrupted. Maybe some sort of language encoding/collation
issue. Just not sure how to test that at the moment.

Actually, I tried it in a bunch of other fields with varying data types
and everything went fine.
I don't know if it's as you say and I mismatched keys (and I need
another pair of glasses) or something else. Just hoping (but being
confident) it won't happen again.

You might try emailing the pgcrypto author
https://www.postgresql.org/docs/10/static/pgcrypto.html#id-1.11.7.35.11

In the source code I noticed that there are many:

px_debug('Some text')

that map to:

{PXE_PGP_CORRUPT_DATA, "Wrong key or corrupt data"}.

I tried running with messages set to DEBUG to see if I could get at the
more specific messages. That did not work, so you might ask the author
if there is a way to get at them.

Now trying to speed up a little some queries involving SELECTing among
these encrypted fields, if I'm stuck I'll open a new thread.

Thanks,
Moreno.-

--
Adrian Klaver
adrian.klaver@aklaver.com